SQL 2005 Cluster Startup Params

Having recently deployed a new SQL 2005 cluster, I thought I’d share a couple things that I originally got hung up on. 

First, startup parameters, It took me some time to find that when more than one parameter is used as Startup Parameters in SQL Sever, they should be separated by semicolon rather than space.

For example:
-dC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mastlog.ldf;-T4616

Other, BIG lesson learned, is when moving SQL 2005 System Databases, you’re again required to update startup params.  If you happen to make a mistake, there’s a small hoop you have to jump through to remedy the problem, as simply fixing the startup parameters won’t recover the SQL Service, as it simply won’t restart.  In a clustered environment, when you change a protocol parameter for a clustered instance of Microsoft SQL Server 2000 or 2005 to a value that is not valid, the SQL Server service cannot start. Additionally, you cannot change the protocol parameter back to the original value.

This problem occurs because the cluster service obtains an incorrect protocol setting from a cluster checkpoint when you start the SQL Server service.  Use the steps below for a work around:

1. Bring the SQL Server resource offline by using Cluster Administrator.
2. At a command prompt, run one of the following commands to disable the cluster checkpoint for the specific registry subkey:
   

   •	For an instance of SQL Server 2005, run the following command: cluster res “SQL Server” /removecheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER” Note In this command, MSSQL.x is a placeholder for the instance ID for the instance of SQL Server. You can determine the corresponding value for the system from the value of the MSSQLSERVER registry entry in the following registry subkey: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\
   •	For a default instance of SQL Server 2000, run the following command: cluster res “SQL Server” /removecheck: “Software\Microsoft\MSSQLServer\MSSQLSERVER”
   •	For a named instance of SQL Server 2000, run the following command: cluster res “SQL Server” /removecheck: “SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLSERVER” Note In this command, InstanceName is a placeholder for the name of the SQL Server 2000 instance.

    

3. Correct the protocol parameter for the clustered instance of SQL Server on all nodes.
4. At a command prompt, run one of the following commands to enable the cluster checkpoint for the specific registry subkey:
   

   •	For an instance of SQL Server 2005, run the following command: cluster res “SQL Server” /addcheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER”
   •	For a default instance of SQL Server 2000, run the following command: cluster res “SQL Server” /addcheck: “Software\Microsoft\MSSQLServer\MSSQLSERVER”
   •	For a named instance of SQL Server 2000, run the following command: cluster res “SQL Server” /addcheck: “SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLSERVER”

    

5. Bring the SQL Server resource online.

Happy Recovery!