I experienced an issue recently where a cluster could not start SQL Server as I had modified the startup parameters and mistakenly entered one incorrectly. I didn’t notice until after I’d stopped SQL Server, and was unable to restart it. I changed the value again in SQL Server Configuration Manager, but discovered that every time I attempted to bring SQL Server back up using Cluster Administrator, the incorrect value was back in place.
The reason for this is that the cluster controls the startup parameters, and will replicate the value through all nodes of the cluster. The only way to change this parameter is to change it on the active SQL Server node. However, if you don’t have an active node, because you’ve just broken it and it won’t start…
Now that you understand the Catch-22, what can you do to fix it? There’s really only two solutions – tell the cluster to replicate a different value to all the nodes (impossible without PSS’s help, as far as I know), or get SQL Server running without the cluster.
There’s two ways of booting SQL Server in this instance. The first is to start SQL Server (sqlsrvr.exe) from the command line, specifying the paths to the master database (remember, we can’t read from the registry due to the cluster updating it). The second option is to modify the registry, and then start SQL Server via the Services control panel. Note that modifying the registry is highly NOT recommended, but may be your only option.
Once SQL Server has started locally, use Cluster Administrator to start SQL Server on the cluster. As SQL Server is already running, the Cluster Administrator hooks right into it. From there, you can use Configuration Manager to fix the startup parameter, have it replicate through the cluster, and restart SQL Server.
Phew. The moral of the story is to double check your parameters before shutting down SQL Server!
I’m happy to hear any other possible solutions to this problem that I might have missed.