DSCResources/DSC_SqlMemory/en-US/about_SqlMemory.help.txt

.NAME
    SqlMemory
 
.DESCRIPTION
    The `SqlMemory` DSC resource sets the minimum server memory and
    maximum server memory configuration option. That means it sets the minimum
    and the maximum amount of memory, in MB, in the buffer pool used by the
    instance of SQL Server The default setting for minimum server memory is 0,
    and the default setting for maximum server memory is 2147483647 MB. Read
    more about minimum server memory and maximum server memory in this article
    [Server Memory Server Configuration Options](https://msdn.microsoft.com/en-us/library/ms178067.aspx).
 
    >**NOTE** These configuration options can also be configured using the DSC
    >resource _SqlConfiguration_ but will not allow the dynamic configuration
    >as this resource provides. Make sure this value is not configured by both
    >the resources _SqLMemory_ and _SqlConfiguration_!
 
    ## Formula for dynamically allocating maximum memory
 
    The formula is based on the [SQL Max Memory Calculator](http://sqlmax.chuvash.eu/)
    website. This was inspired from the repository [sql-max](https://github.com/mirontoli/sql-max)
    maintained by [@mirontoli](https://github.com/mirontoli).
 
    ### Formula
 
    The dynamic maximum memory (in MB) is calculate with this formula:
 
    ```powershell
    TotalPhysicalMemory - (NumOfSQLThreads * ThreadStackSize) - (1024 * CEILING(NumOfCores / 4)) - OSReservedMemory
    ```
 
    #### NumOfSQLThreads
 
    * If the number of cores is less than or equal to 4, the number of SQL threads
      is set to: 256 + (NumberOfCores - 4) \* 8.
    * If the number of cores is greater than 4, the number of SQL threads is set
      to: 0 (zero).
 
    #### ThreadStackSize
 
    * If the architecture of windows server is x86, the size of thread stack is 1MB.
    * If the architecture of windows server is x64, the size of thread stack is 2MB.
    * If the architecture of windows server is IA64, the size of thread stack is 4MB.
 
    #### OSReservedMemory
 
    * If the total physical memory is less than or equal to 20GB, the percentage of
      reserved memory for OS is 20% of total physical memory.
    * If the total physical memory is greater than 20GB, the percentage of reserved
      memory for OS is 12.5% of total physical memory.
 
    ## Requirements
 
    * Target machine must be running Windows Server 2012 or later.
    * Target machine must be running SQL Server Database Engine 2012 or later.
 
    ## Known issues
 
    All issues are not listed here, see [here for all open issues](https://github.com/dsccommunity/SqlServerDsc/issues?q=is%3Aissue+is%3Aopen+in%3Atitle+SqlMemory).
 
.PARAMETER InstanceName
    Key - String
    The name of the SQL Server instance to be configured.
 
.PARAMETER ServerName
    Write - String
    The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME.
 
.PARAMETER Ensure
    Write - String
    Allowed values: Present, Absent
    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 its default values. Default value is 'Present'.
 
.PARAMETER DynamicAlloc
    Write - Boolean
    If set to $true then max memory will be dynamically configured. When this parameter is set to $true, the parameter MaxMemory must be set to $null or not be configured. Default value is $false.
 
.PARAMETER MinMemory
    Write - SInt32
    Minimum amount of memory, in MB, in the buffer pool used by the instance of SQL Server.
 
.PARAMETER MaxMemory
    Write - SInt32
    Maximum amount of memory, in MB, in the buffer pool used by the instance of SQL Server.
 
.PARAMETER ProcessOnlyOnActiveNode
    Write - Boolean
    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.
 
.PARAMETER IsActiveNode
    Read - Boolean
    Returns if the current node is actively hosting the SQL Server instance.
 
.EXAMPLE 1
 
This example shows how to set the minimum and maximum memory
configuration option with the value equal to 1024 and 12288.
 
Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )
 
    Import-DscResource -ModuleName 'SqlServerDsc'
 
    node localhost
    {
        SqlMemory 'Set_SQLServerMaxMemory_To12GB'
        {
            Ensure = 'Present'
            DynamicAlloc = $false
            MinMemory = 1024
            MaxMemory = 12288
            ServerName = 'sqltest.company.local'
            InstanceName = 'DSC'
 
            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}
 
.EXAMPLE 2
 
This example shows how to set the maximum memory
configuration option with the automatic configuration.
 
In the event this is applied to a Failover Cluster Instance (FCI), the
ProcessOnlyOnActiveNode property will tell the Test-TargetResource function
to evaluate if any changes are needed if the node is actively hosting the
SQL Server instance.
 
Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )
 
    Import-DscResource -ModuleName 'SqlServerDsc'
 
    node localhost
    {
        SqlMemory 'Set_SQLServerMaxMemory_ToAuto'
        {
            Ensure = 'Present'
            DynamicAlloc = $true
            ServerName = 'sqltest.company.local'
            InstanceName = 'DSC'
            ProcessOnlyOnActiveNode = $true
 
            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}
 
.EXAMPLE 3
 
This example shows how to set the minimum memory to 2GB and the maximum memory
configuration option with the automatic configuration.
 
Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )
 
    Import-DscResource -ModuleName 'SqlServerDsc'
 
    node localhost
    {
        SqlMemory 'Set_SQLServerMinAndMaxMemory_ToAuto'
        {
            Ensure = 'Present'
            DynamicAlloc = $true
            ServerName = 'sqltest.company.local'
            InstanceName = 'DSC'
            MinMemory = 2048
 
            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}
 
.EXAMPLE 4
 
This example shows how to set the minimum and maximum memory
configuration option with the default configuration.
 
Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )
 
    Import-DscResource -ModuleName 'SqlServerDsc'
 
    node localhost
    {
        SqlMemory 'Set_SQLServerMaxMemory_ToDefault'
        {
            Ensure = 'Absent'
            ServerName = 'sqltest.company.local'
            InstanceName = 'DSC'
 
            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}