cSQLConfig

1.2

DSCResources/TAMZ_cSQLMaxDop/TAMZ_cSQLMaxDop.psm1

function Get-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Collections.Hashtable])]
    param
    (
        [parameter(Mandatory = $true)]
        [System.Boolean]
        $DynamicAlloc
    )

    $server ="."
    $Query = "Select name,value_in_use from sys.configurations where name = 'max degree of parallelism'"

    $cn=New-Object -TypeName System.Data.SqlClient.SQLConnection
    $cn = new-object -TypeName system.data.SqlClient.SqlConnection -ArgumentList "Data Source=$server;Integrated Security=SSPI;Initial Catalog=master;Connect Timeout = 5"
    $cn.Open()
    $cmd=New-Object -TypeName system.Data.SqlClient.SqlCommand -ArgumentList $Query,$cn
    $sqlReader = $cmd.ExecuteReader()    

    while ($sqlReader.Read()) 
    { 
       $GetMaxDop = $sqlReader["value_in_use"] 
    }
     If ($DynamicAlloc -eq $true)
    {
        If ($GetMaxDop -eq 0)
        {
            $Ensure = "Absent"
        }
        else 
        {
            $Ensure = "Present"
        }
    }
    Else
    {
        If ($GetMaxDop -eq $MaxDop)
        {
            $Ensure = "Absent"
        }
        else 
        {
            $Ensure = "Present"
        }
    }

    $cn.Close()
    $returnValue = @{
            DynamicAlloc =$DynamicAlloc
            MaxDop = $GetMaxDop
            Ensure = $Ensure 
            }
    $returnValue
}


function Set-TargetResource
{
    [CmdletBinding()]
    param
    (
        [parameter(Mandatory = $true)]
        [System.Boolean]
        $DynamicAlloc,

        [System.Int32]
        $MaxDop,

        [ValidateSet("Present","Absent")]
        [System.String]
        $Ensure
    )

    Write-Verbose -Message "Ensure : $Ensure"
    If ($Ensure -eq "Absent")
    {
        $MaxDop = 0
    }
    
    ElseIf ($DynamicAlloc -eq $True)
    {
        $winProc = Get-WmiObject -class win32_Processor
        $winComp = Get-WMIObject -class Win32_ComputerSystem
        $NumCores = $winProc.NumberOfCores
        $NumProcs = $winComp.NumberOfProcessors
        $NumLogProc = $winComp.NumberOfLogicalProcessors
               
        if ($NumProcs -eq 1) 
        {
            $MaxDop =  ($NumCores /2)
            $MaxDop=[math]::round( $MaxDop,[system.midpointrounding]::AwayFromZero)
        }
        elseif ($NumCores -ge 8) 
        {
            $MaxDop = 8
        }
        else
        {
            $MaxDop = $NumCores
        }
    }
    else
    {
        if (!$MaxDop)
        {
            Throw "Dynamic Allocation is not set and MaxDop was not passed."
            Exit
        } 
    }
    #Check
    $server ="."
    $cn = new-object -TypeName system.data.SqlClient.SqlConnection -ArgumentList "Data Source=$server;Integrated Security=SSPI;Initial Catalog=master"
    $cn.Open()
    $q = "exec sp_configure 'show advanced options', 1;"
    $q = $q + "RECONFIGURE;"
    $q = $q + "exec sp_configure 'max degree of parallelism', $MaxDop;"
    $q = $q + "RECONFIGURE;"
    $cmd = new-object -TypeName System.Data.SqlClient.SqlCommand -ArgumentList $q, $cn
    Write-Verbose -Message "Setting MaxDop to $MaxDop"
    $dr = $cmd.ExecuteNonQuery()
    $cn.Close()
}



function Test-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Boolean])]
    param
    (
        [parameter(Mandatory = $true)]
        [System.Boolean]
        $DynamicAlloc,

        [System.Int32]
        $MaxDop,

        [ValidateSet("Present","Absent")]
        [System.String]
        $Ensure
    )

    $server ="."
    $Query = "Select name,value_in_use from sys.configurations where name = 'max degree of parallelism'"

    $cn=New-Object -TypeName System.Data.SqlClient.SQLConnection
    $cn = new-object -TypeName system.data.SqlClient.SqlConnection -ArgumentList "Data Source=$server;Integrated Security=SSPI;Initial Catalog=master;Connect Timeout = 5"
    $cn.Open()
    $cmd=New-Object -TypeName system.Data.SqlClient.SqlCommand -ArgumentList $Query,$cn
    $sqlReader = $cmd.ExecuteReader()    
    
    while ($sqlReader.Read()) 
    { 
        $GetMaxDop = $sqlReader["value_in_use"] 
    }

    If ($DynamicAlloc -eq $true )
    {
        IF ($Ensure -eq "Present")
        {
            If ($GetMaxDop -eq 0)
            {
                Write-verbose -message "Current MaxDop is $GetMaxDop should be updated"
                return $false
            }
            else 
            {
                Write-verbose -message "Current MaxDop is configured at $MaxDop."
                return $True
            }
        }
        #$Ensure must equal Absent therefore it should be 0
        Else 
        {
            If ($GetMaxDop -eq 0)
            {
                Write-verbose -message  "Current MaxDop is $GetMaxDop should be updated"
                return $true
            }
            else 
            {
                Write-verbose -message  "Current MaxDop is configured at $MaxDop."
                return $false
            }
        }
    }
    Else
    {
        If ($GetMaxDop -eq $MaxDop)
        {
            Write-verbose -message  "Current MaxDop is at Requested value. Do nothing." 
            return $true
        }
        else 
        {
            Write-verbose -message  "Current MaxDop is $GetMaxDop should be updated"
            return $False
        }
    }
    $cn.Close()
}



Export-ModuleMember -Function *-TargetResource