Change SQL Server Parameters in a Clustered Environment when SQL Server is not Online

  • Article

When you use SQL Server Configuration Manager to change Database Engine parameters in a clustered environment, you must make changes on the active node while the SQL Server cluster resource is online. If SQL Server is not online, you must bring SQL Server online first. However, in some circumstances, you may be unable to bring SQL Server online.

This article describes how to change SQL Server parameters in a clustered environment when SQL Server is not online or when you cannot bring SQL Server online.

To change Database Engine parameters in a clustered environment when SQL Server is not online, use one of the following methods.

Method 1

Try to use this method first.

Warning

This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base KB 322756: How to back up and restore the registry in Windows.

  1. On the Start menu, click Run, type regedit, and then click OK.

  2. Locate the quorum disk. To do this, locate the following registry key: HKEY_LOCAL_MACHINE\Cluster\Quorum

    The Path entry contains the path of the quorum disk. For example, the Path entry contains the following path: <QuorumDrive>:\MSCS

  3. Locate the GUID of the SQL Server cluster resource. To do this, follow these steps:

    1. Locate the following registry key: HKEY_LOCAL_MACHINE\Cluster\Resources

    2. Examine the Name column of the registry entries.

      Note

      Several registry entries include GUID in the name of the entry.

    3. For the default instance, locate the SQL Server cluster resource that includes SQL Server in the Name column. For named instances, locate the SQL Server cluster resources that include "SQL Server (<InstanceName>)" in the Name column.

  4. Locate the checkpoint file name. To do this, follow these steps:

    1. Locate the following registry key: HKEY_LOCAL_MACHINE\Cluster\Resources\{GUID}\RegSync

    2. In the details pane, view the checkpoint registry hives and the corresponding numbers that resemble the following:

      • For the default instance: 00000004 SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLSERVER

      • For a named instance: 00000004 SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLSERVER

        Note

        For a named instance, X corresponds to the instance ID.

    The number is the checkpoint file name. In this example, the checkpoint file name is 00000004.cpt.

  5. In Registry Editor, click HKEY_LOCAL_MACHINE.

  6. On the File menu, click Load Hive.

  7. In the <QuorumDrive>:\<GUID> folder, locate the checkpoint file that you found in step 4.

  8. In the Key Name box, type 1, and then click OK.

  9. Locate the following registry key to correct the invalid checkpoint registry key value: HKEY_LOCAL_MACHINE\1\<YourRegistryKey>

    Note

    The following examples correct the MSSQLSERVER checkpoint registry key:

    Example 1: To correct the invalid path of the master.mdf file, locate the registry key: HKEY_LOCAL_MACHINE\1\Parameters. Correct the SQLArg0 key.

    Example 2: To disable the incorrectly enabled VIA protocol, locate the following registry key: HKEY_LOCAL_MACHINE\1\SuperSocketNetLib\Via. Change the value of the Enabled entry from 1 to 0.

  10. After you correct the registry key, click HKEY_LOCAL_MACHINE\1, click the File menu, and then click Unload Hive.

Note

After you follow these steps, this checkpoint is fixed and is replicated to the specific node automatically during failover. You can bring the instance of SQL Server online.

Method 2

Checkpointed registry keys are saved only when the associated resources are online. When the resources are not online, changes to the checkpointed keys will be overwritten by the last saved values from the checkpointed file. For more information, see KB 174070: Registry replication in Microsoft Cluster Server

Important

  • Do not perform SQL Server cluster group failover between step 2 and step 4. Also, if you do not complete all these steps, you may experience unexpected behavior on failover.

  • For step 2 and step 4, MSSQL.x, MSQL10.x, and MSSQL10_50.x are placeholders for the respective instance ID of 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\<InstanceName>\SQL\

  • For step 2 and step 4, <InstanceName> is a placeholder for the name of the instance of SQL Server.

  1. At a command prompt, obtain the list of checkpointed keys by using the following command:

    cluster res /checkpoints

  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 2012, run the following command:

      cluster . resource "SQL Network Name (<InstanceName>)" /removecheckpoints:"Software\Microsoft\Microsoft SQL Server\MSSQL110.x\MSSQLSERVER"

    • For an instance of SQL Server 2008 R2, run the following command:

      cluster . resource "SQL Network Name (<InstanceName>)" /removecheckpoints:"Software\Microsoft\Microsoft SQL Server\MSSQL10_50.x\MSSQLSERVER"

    • For an instance of SQL Server 2008, run the following command:

      cluster . resource "SQL Network Name (<InstanceName>)" /removecheckpoints:"Software\Microsoft\Microsoft SQL Server\MSSQL10.x\MSSQLSERVER"

    • For an instance of SQL Server 2005, run the following command:

      cluster res "SQL Server (<InstanceName>)" /removecheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.x \MSSQLSERVER"

  3. Change the 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 2008, run the following command:

      cluster . resource "SQL Server (<InstanceName>)" /addcheckpoints:"HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"

    • For an instance of SQL Server 2005, run the following command:

      cluster res "SQL Server (<InstanceName>)" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"

  5. Bring the instance of SQL Server online.

Example output for method 2 on SQL Server 2008 servers

cluster res /checkpoints
No resource name specified.
Listing registry checkpoints for all resources …
Resource                            Registry Checkpoint
--------------------                --------------------------------------------------------

SQL Network Name (<Instance name>) 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<INSTANCE NAME>\Cluster'

SQL Network Name (<INSTANCE NAME>) 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<INSTANCE NAME>\MSSQLServer'

SQL Network Name (<INSTANCE NAME>) 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<INSTANCE NAME>\Replication'

SQL Network Name (<INSTANCE NAME>) 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<INSTANCE NAME>\Providers'

SQL Network Name (<INSTANCE NAME>) 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<INSTANCE NAME>\SQLServerSCP'

SQL Network Name (<INSTANCE NAME>) 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<INSTANCE NAME>\CPE'

SQL Network Name (<INSTANCE NAME>) 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<INSTANCE NAME>\SQLServerAgent'

SQL IP Address 1 (<INSTANCE NAME>) None

SQL Server (<INSTANCE NAME>)       None

SQL Server Agent (<INSTANCE NAME>) None

C:\Windows\system32>cluster . resource "SQL Network Name (<INSTANCE NAME>)" /removecheckpoints:"Software\Microsoft\Microsoft SQL Server\MSSQL10.<INSTANCE NAME>\MSSQLSERVER" 

Removing registry checkpoint 'Software\Microsoft\Microsoft SQL Server\MSSQL10.EI

PRPTDB\MSSQLSERVER' for resource 'SQL Network Name (<INSTANCE NAME>)'...

See Also

Concepts

Configure Server Startup Options (SQL Server Configuration Manager)

Other Resources

KB 912397: The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid

KB 244980: How to change the network IP addresses of SQL Server failover cluster instances