In some situations, be it for the need to make some troubleshooting or to make some improvement in our environment, we decide to change SQL Server startup parameters.
The procedure to make this change is described on this article from Microsoft, including the way to do it on cluster environments (see the note on the beggining of the article).
What you should do is to access SQL Server Configuration Manager (SSCM), go to the service properties and then to the “Advanced” tab and on it look for the item “Startup Parameters” and make the change, as you can see on the picture below:
It is worth mentioning that on SQL Server 2012 this process has been made easier, with a tab just for it:
The important detail here is that when this process is executed on a clustered environment the steps needed are a little bit different from what is mentioned on Microsoft’s document that I have just mentioned.
In this case the process you should follow is:
- Access the ACTIVE node on the cluster and with SQL Server Service Online
- Access SQL Server Configuration Manager (SSCM) and change the startup parameters
- Put SQL Server resources OFFLINE, using Failover Cluster Manager (FCM) (this won’t make the service to failover)
- Put SQL Server resources ONLINE, using FCM
This procedure will allow the changes made to windows’ registry to be replicated from the active node to the other nodes and ensure that the new startup parameters are going to work. If you try to make these changes using SSCM they will be lost/ignored.
In the case you make changes to windows’ registry I should remind you that the odds that something wrong happens are high and, if the service is offline, the service might even not startup anymore! So follow the recommended procedure and don’t put your environment in risk.
If you wish to understand how the process of registry synchrony happens between nodes in a cluster environment and how to proceed in the case you make a change in registry and it does not work well you should read the articles below: