cSQLConfig

1.4

DSCResources/TAMZ_cSQLMemory/TAMZ_cSQLMemory.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 like '%server memory%'"

    $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()) 
    { 
        if ($sqlReader["name"] -eq "min server memory (MB)")
        {
            $MinMemory = $sqlReader["value_in_use"]
        }
        elseif ($sqlReader["name"] -eq "max server memory (MB)")
        {
            $MaxMemory = $sqlReader["value_in_use"]
        }
        
    }
    if ($MaxMemory -eq 2147483647)
    {
        $Ensure = "Absent"
    }
    else
    {
        $Ensure = "Present"
    }

    $cn.Close()
    $returnValue = @{
            DynamicAlloc = $DynamicAlloc
            MinMemory = $MinMemory
            MaxMemory = $MaxMemory
            Ensure = $Ensure
            }
        $returnValue
}


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

        [System.Int32]
        $MinMemory,

        [System.Int32]
        $MaxMemory,

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

    $winOS = Get-WMIObject -Class Win32_OperatingSystem
    $serverMem = $winOS.TotalVisibleMemorySize
    If($Ensure -eq "Absent")
    {
       $MaxMemory =2147483647
       $MinMemory = 128
    }        
    Elseif ($DynamicAlloc -eq $True)
    {
        if ($serverMem -ge 128000000) 
        {
            #Server mem - 10GB
            $maxMem = $serverMem - 10000000 
        }
        elseif ($serverMem -ge 32000000 -and $serverMem -lt 128000000) 
        {
            #Server mem - 4GB
            $maxMem = $serverMem - 4000000
        }
        elseif ($serverMem -ge 16000000)
        {
            #Server mem - 2GB
            $maxMem = $serverMem - 2000000
        }
        else
        {
            #Server mem - 1GB
            $maxMem = $serverMem - 1000000
        }
        [int] $maxMem = $maxMem / 1024
        [int] $serverMem = $serverMem / 1024

    }
    else
    {
        if (!$MaxMemory -xor !$MinMemory)
        {
            Throw "Dynamic Allocation is not set and Min and Max memory were not passed."
            Exit
        } 
    }

    Write-Verbose -message "Server Memory is $serverMem and should be set capped."
    $server ="."
    $cn = new-object -typename system.data.SqlClient.SqlConnection -ArgumentList "Data Source=$server;Integrated Security=SSPI;Initial Catalog=master;Connect Timeout = 5"
    $cn.Open()
    $q = "exec sp_configure 'show advanced options', 1;"
    $q = $q + "RECONFIGURE;"
    $q = $q + "exec sp_configure 'max server memory', $MaxMemory;"
    $q = $q + "RECONFIGURE;"
    $q = $q + "exec sp_configure 'min server memory', $MinMemory;"
    $q = $q + "RECONFIGURE;"
    $cmd = new-object -typename "System.Data.SqlClient.SqlCommand" -ArgumentList $q, $cn
    Write-Verbose -Message "Setting Max Server Memory to $MaxMemory"
    $dr = $cmd.ExecuteNonQuery()
    $cn.Close()
}



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

        [System.Int32]
        $MinMemory,

        [System.Int32]
        $MaxMemory,

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

    $server ="."
    $Query = "Select name,value_in_use from sys.configurations where name like '%server memory%'"

    $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()) 
    { 
        if ($sqlReader["name"] -eq "min server memory (MB)")
        {
            $GetMinMemory = $sqlReader["value_in_use"]
        }
        elseif ($sqlReader["name"] -eq "max server memory (MB)")
        {
            $GetMaxMemory = $sqlReader["value_in_use"]
        }
        
    }
    $cn.Close()

    If($Ensure -eq "Absent")
    {
       if ($GetMaxMemory  -eq 2147483647)
            {
                return $true
            }
            else 
            {
                return $false
            }
    }
    else
    {
        If ($DynamicAlloc -eq $true)
        {
            if ($GetMaxMemory  -eq 2147483647)
            {
                Write-Verbose -Message "Current Max Memory is $GetMaxMemory. Min Memory is $GetMinMemory"
                return $false
            }
            else 
            {
                Write-Verbose -Message "Current Max Memory is $GetMaxMemory. Min Memory is $GetMinMemory"
                return $true
            }
        }
        else
        {
            If($MinMemory -ne $GetMinMemory)
            {
                Write-Verbose -Message "Current Max Memory is $GetMaxMemory. Min Memory is $GetMinMemory"
                return $false
            }
            elseif ($MaxMemory -ne $GetMaxMemory)
            {
                Write-Verbose -Message "Current Max Memory is $GetMaxMemory. Min Memory is $GetMinMemory"
                return $false  
            }
            else
            {
                return $true
            }
        }
    }
}


Export-ModuleMember -Function *-TargetResource