CommonTasks

0.3.171

DscResources/SqlServer/SqlServer.schema.psm1

configuration SqlServer
{
    param
    (
        [Parameter(Mandatory)]
        [hashtable]
        $Setup
    )

    Import-DscResource -ModuleName PSDesiredStateConfiguration
    Import-DscResource -ModuleName SqlServerDsc

    if( $null -ne $Setup )
    {
        # Remove Case Sensitivity of ordered Dictionary or Hashtables
        $Setup = @{}+$Setup

        # FileStreamAccessLevel is not supported by SqlSetup and must be enabled later
        $fileStreamAccessLevel = $Setup.FileStreamAccessLevel
        $Setup.Remove( 'FileStreamAccessLevel' )

        (Get-DscSplattedResource -ResourceName SqlSetup -ExecutionName "sqlSetup" -Properties $Setup -NoInvoke).Invoke($Setup)

        # enable/disable FileStream
        if( $null -ne $fileStreamAccessLevel )
        {        
            [string]$instanceName = $Setup.InstanceName

            Script sqlFileStreamAccess
            {
                TestScript = 
                {
                    # get installed SQL Server version
                    [string]$cmgmt = (Get-CimInstance -NameSpace 'ROOT\Microsoft\SQLServer' -Class “__NAMESPACE” | Where-Object { $_.Name.StartsWith( 'ComputerManagement' ) }).Name

                    $cim = Get-CimInstance -Namespace "ROOT\Microsoft\SqlServer\$cmgmt" -Class FilestreamSettings | Where-Object {$_.InstanceName -eq $using:instanceName}

                    Write-Verbose "Expected FilestreamAccessLevel is: $using:fileStreamAccessLevel"
 
                    Write-Verbose "Current FilestreamAccessLevel is: $($cim.AccessLevel) with file share name '$($cim.ShareName)'."
                   
                    if( $cim.AccessLevel -eq $using:fileStreamAccessLevel )
                    {
                        $sqlServer = "localhost$( if($using:instanceName -ne 'MSSQLSERVER') { "\$using:instanceName" })"

                        $sqlFileStreamAccessLevel = Invoke-Sqlcmd "SELECT SERVERPROPERTY( 'FilestreamEffectiveLevel' ) AS FileStreamAccessLevel" -ServerInstance $sqlServer | `
                                                    Select-Object -ExpandProperty FileStreamAccessLevel

                        Write-Verbose "The current SERVERPROPERTY 'FilestreamEffectiveLevel' is: $sqlFileStreamAccessLevel."

                        if( $sqlFileStreamAccessLevel -eq $((2,$using:fileStreamAccessLevel | Measure-Object -Min).Minimum) )
                        {
                            return $true
                        }
                    }

                    return $false
                }
                SetScript = 
                {
                    # get installed SQL Server version
                    [string]$cmgmt = (Get-CimInstance -NameSpace 'ROOT\Microsoft\SQLServer' -Class “__NAMESPACE” | Where-Object { $_.Name.StartsWith( 'ComputerManagement' ) }).Name

                    $cim = Get-CimInstance -Namespace "ROOT\Microsoft\SqlServer\$cmgmt" -Class FilestreamSettings | Where-Object {$_.InstanceName -eq $using:instanceName}

                    Write-Verbose "Set FilestreamAccessLevel to: $using:fileStreamAccessLevel."

                    Invoke-CimMethod -InputObject $cim -MethodName 'EnableFilestream' `
                                     -Arguments @{ AccessLevel = $using:fileStreamAccessLevel; ShareName = $using:instanceName }

                    Get-Service -Name $using:instanceName | Restart-Service -Force
                     
                    $sqlServer = "localhost$( if($using:instanceName -ne 'MSSQLSERVER') { "\$using:instanceName" })"

                    Invoke-Sqlcmd -Query "EXEC sp_configure filestream_access_level, $((2,$using:fileStreamAccessLevel | Measure-Object -Min).Minimum)" -ServerInstance $sqlServer
                    Invoke-Sqlcmd -Query "RECONFIGURE" -ServerInstance $sqlServer
                }
                GetScript = { return 'NA' }   
                DependsOn = '[SqlSetup]sqlSetup'
            }
        }
    }
}