On a two-node Windows Failover Cluster, I have two SQL instances installed. Each instance runs on its own node in the cluster, so that it can make maximum use of the available memory on that server. However, when a failover occurs, it is necessary to reduce the maximum server memory setting for both instances, so that they can share the available memory on the one server. Rather than have to do that manually, however, I decided to automate the process using PowerShell and the Windows Task Scheduler, and here’s how.
Note: the script will work for two SQL instances in a cluster. For additional instances, the script will need to be updated accordingly.
- Save the PowerShell script (download from Technet Gallery) to each server in the cluster.
- Update the PowerShell script setting the required variables in the parameters section, such as the log file location, the SQL instance names, the SQL service names, the path to the SQL SMO dll, the maximum server memory limit you want to set, and the timeout period.
- Create a scheduled task on each server, running as an account that has the appropriate permissions on each instance. Add 2 triggers – one for each SQL instance – and use the event trigger. You can use event ID 101, for example, to identify when a SQL instance becomes active on that node. As the source, use the “SQLAgent$<instancename>”.
As the task action, use Start a program:
- Program: Powershell.exe
- Arguments: -ExecutionPolicy Bypass -WindowStyle Hidden -File “<PathToScript>\Set-SQLClusterMaximumMemory.ps1”
When a SQL instance fails over to the other node, the script will be triggered and will set the maximum server memory limit for both instances on that node.
The script will also log the process:
Automation. Gotta love it 🙂
