Automatic Memory Management for SQL Server Failover Cluster Instances

automatic-memory-management

One of the challenges while using Failover Cluster Instances (FCI) is to manage the memory of the SQL server in case of failover. In general, most SQL Server configurations do not require much attention to memory management beyond the initial configuration. However, Failover Cluster Instances (FCI) architecture is very different from others. Therefore, special attention should be paid to automatic memory management. Here You can read more about FCI.

Initial memory configuration

Before automatic memory management begins, the appropriate settings for minimum and maximum memory must be applied. You should treat this as one of the steps to configure the SQL instance. The SQL script as below is only an example to use. You should automate it in the most convenient way for you. In our case, the script is part of the puppet module. The numbers below are only examples. You will have to come up with your own. Here you can read more about memory configuration.

Automatic memory management

In order to implement automatic memory management in FCI you will need two components.

  • Powershell script adjusting maximum memory for the SQL instance in case of failover
  • Windows schedule task that will trigger the powershell script in case of a specific event

Get-TargetMemory function

Firstly, You need to evaluate the amount of the host memory that can be dedicated to SQL Server. Assuming that there is nothing specific running on the host other than SQL server instances. The following Powershell function will be used for this purpose. This will ensure that the required memory is left for the operating system.

Set-TargetMemoryForSQLInstance function

Secondly, You will create a function that will adjust maximum memory setting for a single SQL instance. Select your favorite method to connect to a SQL Server instance and authenticate. I use a dedicated function that uses sql login authentication. This section has been removed to highlight the most important parts of the code.

Powershell main script

Thirdly, You will use both functions in the main script. Moreover, it will iterate through all nodes of the SQL cluster to estimate the number of SQL instances located on each of them. Then on each SQL instance, the maximum server memory will be set according to the equation: total target memory / number of SQL instances. Even though the script was triggered in case of single SQL instance failover, all SQL instances will be taken into account when setting the memory. In this way, you will ensure that all available memory in the entire SQL cluster will be appropriately distributed.

The above script should be equipped with error handling as well as output logging. This section has been removed to highlight the most important parts of the code.

Windows scheduled task

Finally, You will need to configure the automatic trigger for the script. Windows scheduled task perfectly fits the purpose. Based on my experience trigger on EventID = 17126SQL Server is now ready for client connections works very well in this case. Below is an example of the xml export from scheduled task that I’ve been using to configure this to work. Just take it and import it in your environment. It assumes that powershell script is located in D:\Set-TargetMemoryForSQLInstances.ps1. It will be executed as NT AUTHORITY\SYSTEM user.

Another way to deal with a similar situation can be found here.

References:

Leave a Reply