Wednesday, February 18, 2009

How to change SQL Server parameters in a clustered environment when SQL Server is not online

INTRODUCTION

When you use Microsoft SQL Server 2008 Configuration Manager, SQL Server 2005 Configuration Manager, SQL Serverhttp://images.intellitxt.com/ast/adTypes/mag-glass_10x10.gif 2000 Enterprise Manager, or SQL Server 2000 Setup to change SQL Server parameters in a clustered environment, you have to make changes on the active node while the SQL Server cluster resource is online. If SQL Server is not online, you have to 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.

http://support.microsoft.com/library/images/support/kbgraphics/public/en-us/uparrow.gifBack to the top

MORE INFORMATION

Important 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:

322756Â (http://kbalertz.com/Feedback.aspx?kbNumber=322756/ ) How to back up and restore the registry in Windows

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

http://support.microsoft.com/library/images/support/kbgraphics/public/en-us/uparrow.gifBack to the top

Method 1

Note We recommend that you try to use this method first.

  1. Click Start, click Run, type regedit, and then click OK.
  2. Locate the quorum disk. To do this, follow these steps:
    1. Locate the following registry key:

HKEY_LOCAL_MACHINE\Cluster\Quorum

    1. The Path entry contains the path of the quorum disk. For example, the Path entry contains the following path:

<QuorumDrive>:\MSCS

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

. Locate the following registry key:

HKEY_LOCAL_MACHINE\Cluster\Resources

a. Examine the Name column of the registry entries.

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

    1. 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.
  1. Locate the checkpoint file name. To do this, follow these steps:

. Locate the following registry key:

HKEY_LOCAL_MACHINE\Cluster\Resources\{GUID}\RegSync

a. 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.

  1. In Registry Editor, click HKEY_LOCAL_MACHINE.
  2. On the File menu, click Load Hive.
  3. In the <QuorumDrive>:\<GUID> folder, locate the checkpoint file that you found in step 4.
  4. In the Key Name box, type 1, and then click OK.
  5. 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, follow these steps:

a. Locate the following registry key:

HKEY_LOCAL_MACHINE\1\Parameters

b. Correct the SQLArg0 key.

    • Example 2
      To disable the incorrectly enabled VIA protocol, follow these steps:
      1. Locate the following registry key:

HKEY_LOCAL_MACHINE\1\SuperSocketNetLib\Via

a. Change the value of the Enabled entry from 1 to 0.

  1. 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.

http://support.microsoft.com/library/images/support/kbgraphics/public/en-us/uparrow.gifBack to the top

Method 2

Note Do not perform SQL cluster group failover between step 1 and step 3.

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

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

Note In this command, MSSQL.x is a placeholder for the 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\<Instance Names>\SQL\

    • 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"

    • For the default instance of SQL Server 2000, run the following commands:

cluster res "SQL Server" /removecheck: "Software\Microsoft\MSSQLServer\MSSQLSERVER"

cluster res "SQL Server" /removecheck: "Software\Microsoft\MSSQLServer\Cluster"

Note You have to run the second command only when you add or remove one or more IP addresses on which SQL Server 2000 listens.

    • For a named instance of SQL Server 2000, run the following commands:

cluster res "SQL Server (<InstanceName>)" /removecheck: "SOFTWARE\Microsoft\Microsoft SQL Server\Instance_Name\MSSQLSERVER"

cluster res "SQL Server (<InstanceName>)" /removecheck: "SOFTWARE\Microsoft\Microsoft SQL Server\Instance_Name\Cluster"

Note In these commands, Instance_Name is a placeholder for the name of the instance of SQL Server 2000. Additionally, you have to run the second command only when you add or remove one or more IP addresses on which SQL Server 2000 listens.

  1. Change the parameter for the clustered instance of SQL Server on all nodes.
  2. 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"

    • For the default instance of SQL Server 2000, run the following commands:

cluster res "SQL Server" /addcheck: "Software\Microsoft\MSSQLServer\MSSQLSERVER"

cluster res "SQL Server" /addcheck: "Software\Microsoft\MSSQLServer\Cluster"

    • For a named instance of SQL Server 2000, run the following commands:

cluster res "SQL Server (<InstanceName>)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\Instance_Name\MSSQLSERVER"

cluster res "SQL Server (<InstanceName>)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\Instance_Name\Cluster"

  1. Note The resource name "SQL Server (<InstanceName>)" may be different in your case. To confirm the resource name, start Cluster Administrator, click SQL Group, locate the SQL Server resource properties, and then determine the exact name of the resource.
  2. Bring the instance of SQL Server online.

http://support.microsoft.com/library/images/support/kbgraphics/public/en-us/uparrow.gifBack to the top

REFERENCES

For more information about how to configure server startup options in SQL Server Configuration Manager, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms345416.aspx (http://msdn2.microsoft.com/en-us/library/ms345416.aspx)

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

912397Â (http://kbalertz.com/Feedback.aspx?kbNumber=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

244980Â (http://kbalertz.com/Feedback.aspx?kbNumber=244980/ ) How to change the network IP addresses of SQL Server failover cluster instances


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

2 comments:

Unknown said...

Hail to you my friend. This was extremely helpful. Thank you for taking the time to write it up and posting out on the Internet. I was lost until I found this. Keep up the good work!

George Wen said...

Hi Juan, Thanks for your kind comment and I am glad that the information here can be helpful:-)

Elevating LLM Deployment with FastAPI and React: A Step-By-Step Guide

  In a   previous exploration , I delved into creating a Retrieval-Augmented-Generation (RAG) demo, utilising Google’s gemma model, Hugging ...