DSCResources/MSFT_xSQLServerMemory/MSFT_xSQLServerMemory.psm1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
Import-Module -Name (Join-Path -Path (Split-Path (Split-Path $PSScriptRoot -Parent) -Parent) -ChildPath 'xSQLServerHelper.psm1') -Force

<#
    .SYNOPSIS
        This function gets the value of the min and max memory server configuration option.
 
    .PARAMETER SQLServer
        The host name of the SQL Server to be configured.
 
    .PARAMETER SQLInstanceName
        The name of the SQL instance to be configured.
#>


function Get-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Collections.Hashtable])]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $SQLInstanceName,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $SQLServer = $env:COMPUTERNAME
    )

    $sqlServerObject = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName

    if ($sqlServerObject)
    {
        Write-Verbose -Message 'Getting the value for minimum and maximum SQL server memory.'
        $minMemory = $sqlServerObject.Configuration.MinServerMemory.ConfigValue
        $maxMemory = $sqlServerObject.Configuration.MaxServerMemory.ConfigValue

        # Is this node actively hosting the SQL instance?
        $isActiveNode = Test-ActiveNode -ServerObject $sqlServerObject
    }

    $returnValue = @{
        SQLInstanceName = $SQLInstanceName
        SQLServer       = $SQLServer
        MinMemory       = $minMemory
        MaxMemory       = $maxMemory
        IsActiveNode    = $isActiveNode
    }

    $returnValue
}

<#
    .SYNOPSIS
        This function sets the value for the min and max memory server configuration option.
 
    .PARAMETER SQLServer
        The host name of the SQL Server to be configured.
 
    .PARAMETER SQLInstanceName
        The name of the SQL instance to be configured.
 
    .PARAMETER Ensure
        When set to 'Present' then min and max memory will be set to either the value in parameter MinMemory and MaxMemory or dynamically configured when parameter DynamicAlloc is set to $true.
        When set to 'Absent' min and max memory will be set to default values.
 
    .PARAMETER DynamicAlloc
        If set to $true then max memory will be dynamically configured.
        When this is set parameter is set to $true, the parameter MaxMemory must be set to $null or not be configured.
 
    .PARAMETER MinMemory
        This is the minimum amount of memory, in MB, in the buffer pool used by the instance of SQL Server.
 
    .PARAMETER MaxMemory
        This is the maximum amount of memory, in MB, in the buffer pool used by the instance of SQL Server.
 
    .PARAMETER ProcessOnlyOnActiveNode
        Specifies that the resource will only determine if a change is needed if the target node is the active host of the SQL Server instance.
        Not used in Set-TargetResource.
#>

function Set-TargetResource
{
    [CmdletBinding()]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $SQLInstanceName,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $SQLServer = $env:COMPUTERNAME,

        [Parameter()]
        [ValidateSet('Present', 'Absent')]
        [System.String]
        $Ensure = 'Present',

        [Parameter()]
        [System.Boolean]
        $DynamicAlloc = $false,

        [Parameter()]
        [System.Int32]
        $MinMemory,

        [Parameter()]
        [System.Int32]
        $MaxMemory,

        [Parameter()]
        [Boolean]
        $ProcessOnlyOnActiveNode
    )

    $sqlServerObject = Connect-SQL -SQLServer $SQLServer -SQLInstanceName $SQLInstanceName

    if ($sqlServerObject)
    {
        Write-Verbose -Message 'Setting the minimum and maximum memory used by the instance.'
        switch ($Ensure)
        {
            'Present'
            {
                if ($DynamicAlloc)
                {
                    if ($MaxMemory)
                    {
                        throw New-TerminatingError -ErrorType MaxMemoryParamMustBeNull `
                            -FormatArgs @( $SQLServer, $SQLInstanceName ) `
                            -ErrorCategory InvalidArgument
                    }

                    $MaxMemory = Get-SqlDscDynamicMaxMemory
                    New-VerboseMessage -Message "Dynamic maximum memory has been calculated to $($MaxMemory)MB."
                }
                else
                {
                    if (-not $MaxMemory)
                    {
                        throw New-TerminatingError -ErrorType MaxMemoryParamMustNotBeNull `
                            -FormatArgs @( $SQLServer, $SQLInstanceName ) `
                            -ErrorCategory InvalidArgument
                    }
                }

                $sqlServerObject.Configuration.MaxServerMemory.ConfigValue = $MaxMemory
                New-VerboseMessage -Message "Maximum memory used by the instance has been limited to $($MaxMemory)MB."
            }

            'Absent'
            {
                $sqlServerObject.Configuration.MaxServerMemory.ConfigValue = 2147483647
                $sqlServerObject.Configuration.MinServerMemory.ConfigValue = 0
                New-VerboseMessage -Message ('Ensure is set to absent. Minimum and maximum server memory' + `
                        'values used by the instance are reset to the default values.')
            }
        }

        try
        {
            if ($MinMemory)
            {
                $sqlServerObject.Configuration.MinServerMemory.ConfigValue = $MinMemory
                New-VerboseMessage -Message "Minimum memory used by the instance is set to $($MinMemory)MB."
            }

            $sqlServerObject.Alter()
        }
        catch
        {
            throw New-TerminatingError -ErrorType AlterServerMemoryFailed `
                -FormatArgs @($SQLServer, $SQLInstanceName) `
                -ErrorCategory InvalidOperation `
                -InnerException $_.Exception
        }
    }
}

<#
    .SYNOPSIS
        This function tests the value of the min and max memory server configuration option.
 
    .PARAMETER SQLServer
        The host name of the SQL Server to be configured.
 
    .PARAMETER SQLInstanceName
        The name of the SQL instance to be configured.
 
    .PARAMETER Ensure
        When set to 'Present' then min and max memory will be set to either the value in parameter MinMemory and MaxMemory or dynamically configured when parameter DynamicAlloc is set to $true.
        When set to 'Absent' min and max memory will be set to default values.
 
    .PARAMETER DynamicAlloc
        If set to $true then max memory will be dynamically configured.
        When this is set parameter is set to $true, the parameter MaxMemory must be set to $null or not be configured.
 
    .PARAMETER MinMemory
        This is the minimum amount of memory, in MB, in the buffer pool used by the instance of SQL Server.
 
    .PARAMETER MaxMemory
        This is the maximum amount of memory, in MB, in the buffer pool used by the instance of SQL Server.
 
    .PARAMETER ProcessOnlyOnActiveNode
        Specifies that the resource will only determine if a change is needed if the target node is the active host of the SQL Server instance.
#>

function Test-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Boolean])]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $SQLInstanceName,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $SQLServer = $env:COMPUTERNAME,

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

        [Parameter()]
        [System.Boolean]
        $DynamicAlloc = $false,

        [Parameter()]
        [System.Int32]
        $MinMemory,

        [Parameter()]
        [System.Int32]
        $MaxMemory,

        [Parameter()]
        [Boolean]
        $ProcessOnlyOnActiveNode
    )

    Write-Verbose -Message 'Testing the values of the minimum and maximum memory server configuration option set to be used by the instance.'

    $getTargetResourceParameters = @{
        SQLInstanceName = $SQLInstanceName
        SQLServer       = $SQLServer
    }

    $getTargetResourceResult = Get-TargetResource @getTargetResourceParameters

    $currentMinMemory = $getTargetResourceResult.MinMemory
    $currentMaxMemory = $getTargetResourceResult.MaxMemory
    $isServerMemoryInDesiredState = $true

    <#
        If this is supposed to process only the active node, and this is not the
        active node, don't bother evaluating the test.
    #>

    if ( $ProcessOnlyOnActiveNode -and -not $getTargetResourceResult.IsActiveNode )
    {
        # Use localization if the resource has been converted
        New-VerboseMessage -Message ( 'The node "{0}" is not actively hosting the instance "{1}". Exiting the test.' -f $env:COMPUTERNAME,$SQLInstanceName )
        return $isServerMemoryInDesiredState
    }

    switch ($Ensure)
    {
        'Absent'
        {
            if ($currentMaxMemory -ne 2147483647)
            {
                New-VerboseMessage -Message "Current maximum server memory used by the instance is $($currentMaxMemory)MB. Expected 2147483647MB."
                $isServerMemoryInDesiredState = $false
            }

            if ($currentMinMemory -ne 0)
            {
                New-VerboseMessage -Message "Current minimum server memory used by the instance is $($currentMinMemory)MB. Expected 0MB."
                $isServerMemoryInDesiredState = $false
            }
        }

        'Present'
        {
            if ($DynamicAlloc)
            {
                if ($MaxMemory)
                {
                    throw New-TerminatingError -ErrorType MaxMemoryParamMustBeNull `
                        -FormatArgs @( $SQLServer, $SQLInstanceName ) `
                        -ErrorCategory InvalidArgument
                }

                $MaxMemory = Get-SqlDscDynamicMaxMemory
                New-VerboseMessage -Message "Dynamic maximum memory has been calculated to $($MaxMemory)MB."
            }
            else
            {
                if (-not $MaxMemory)
                {
                    throw New-TerminatingError -ErrorType MaxMemoryParamMustNotBeNull `
                        -FormatArgs @( $SQLServer, $SQLInstanceName ) `
                        -ErrorCategory InvalidArgument
                }
            }

            if ($MaxMemory -ne $currentMaxMemory)
            {
                New-VerboseMessage -Message ("Current maximum server memory used by the instance " + `
                        "is $($currentMaxMemory)MB. Expected $($MaxMemory)MB.")
                $isServerMemoryInDesiredState = $false
            }

            if ($MinMemory)
            {
                if ($MinMemory -ne $currentMinMemory)
                {
                    New-VerboseMessage -Message ("Current minimum server memory used by the instance " + `
                            "is $($currentMinMemory)MB. Expected $($MinMemory)MB.")
                    $isServerMemoryInDesiredState = $false
                }
            }
        }
    }

    return $isServerMemoryInDesiredState
}

<#
    .SYNOPSIS
    This cmdlet is used to return the Dynamic MaxMemory of a SQL Instance
#>

function Get-SqlDscDynamicMaxMemory
{
    try
    {
        $physicalMemory = ((Get-CimInstance -ClassName Win32_PhysicalMemory).Capacity | Measure-Object -Sum).Sum
        $physicalMemoryInMegaBytes = [Math]::Round($physicalMemory / 1MB)

        # Find how much to save for OS: 20% of total ram for under 15GB / 12.5% for over 20GB
        if ($physicalMemoryInMegaBytes -ge 20480)
        {
            $reservedOperatingSystemMemory = [Math]::Round((0.125 * $physicalMemoryInMegaBytes))
        }
        else
        {
            $reservedOperatingSystemMemory = [Math]::Round((0.2 * $physicalMemoryInMegaBytes))
        }

        $numberOfCores = (Get-CimInstance -ClassName Win32_Processor | Measure-Object -Property NumberOfCores -Sum).Sum

        # Get the number of SQL threads.
        if ($numberOfCores -ge 4)
        {
            $numberOfSqlThreads = 256 + ($numberOfCores - 4) * 8
        }
        else
        {
            $numberOfSqlThreads = 0
        }

        $operatingSystemArchitecture = (Get-CimInstance -ClassName Win32_operatingsystem).OSArchitecture

        # Find threadStackSize 1MB x86/ 2MB x64/ 4MB IA64
        if ($operatingSystemArchitecture -eq '32-bit')
        {
            $threadStackSize = 1
        }
        elseif ($operatingSystemArchitecture -eq '64-bit')
        {
            $threadStackSize = 2
        }
        else
        {
            $threadStackSize = 4
        }

        $maxMemory = $physicalMemoryInMegaBytes - $reservedOperatingSystemMemory - ($numberOfSqlThreads * $threadStackSize) - (1024 * [System.Math]::Ceiling($numberOfCores / 4))
    }
    catch
    {
        throw New-TerminatingError -ErrorType ErrorGetDynamicMaxMemory `
            -ErrorCategory InvalidOperation `
            -InnerException $_.Exception
    }

    $maxMemory
}

Export-ModuleMember -Function *-TargetResource