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
    https://msdn.microsoft.com/en-us/library/ms178067.aspx.
 
    > [!IMPORTANT]
    > 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 http://sqlmax.chuvash.eu/
    website. This was inspired from the repository https://github.com/mirontoli/sql-max
    maintained by 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 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 the current computer name.
 
.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 MinMemoryPercent
    Write - SInt32
    Minimum amount of memory, as a percentage of total server memory, in the buffer pool used by the instance of SQL Server.
 
.PARAMETER MaxMemoryPercent
    Write - SInt32
    Maximum amount of memory, as a percentage of total server memory, 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
        }
    }
}
 
.EXAMPLE 5
 
This example shows how to set the minimum and maximum memory
configuration option, to a percentage of total server memory.
In this case a value equal to 25 and 75 percent of total memory.
 
Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )
 
    Import-DscResource -ModuleName 'SqlServerDsc'
 
    node localhost
    {
        SqlMemory 'Set_SQLServerMemoryByPercent'
        {
            Ensure = 'Present'
            DynamicAlloc = $false
            MinMemoryPercent = 25
            MaxMemoryPercent = 75
            ServerName = 'sqltest.company.local'
            InstanceName = 'DSC'
 
            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}
 
.EXAMPLE 6
 
This example shows how to set the minimum memory, to 25 percent of total server memory 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_SQLServerMemoryByPercent'
        {
            Ensure = 'Present'
            DynamicAlloc = $true
            MinMemoryPercent = 25
            ServerName = 'sqltest.company.local'
            InstanceName = 'DSC'
 
            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}
 
.EXAMPLE 7
 
This example shows how to set the maximum memory to 75 percent of total server memory and the minimum memory
to a fixed value of 1024.
 
Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )
 
    Import-DscResource -ModuleName 'SqlServerDsc'
 
    node localhost
    {
        SqlMemory 'Set_SQLServerMemoryByPercent'
        {
            Ensure = 'Present'
            DynamicAlloc = $false
            MaxMemoryPercent = 75
            MinMemory = 1024
            ServerName = 'sqltest.company.local'
            InstanceName = 'DSC'
 
            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}