Home > SQL server DBA administration > How we can move the Master database location in SQL server 2008 R2 and 2012 cluster environment.

How we can move the Master database location in SQL server 2008 R2 and 2012 cluster environment.


In our project we have to maintain certain criteria regarding the data file and log file location. We have to use specific location where all the system databases data and log file should be placed. We can fixed the settings during installation but got an server in  our support where we have to change the master database location as per our process  .To do the same we have to made the changes in the SQL Server Configuration manager .

 

As per the Microsoft article there are two method is mentioned [How to change SQL Server parameters in a clustered environment when SQL Server is not online (http://support.microsoft.com/default.aspx/kb/953504)] but we are used the below method.

 

It is an cluster environment so we have to change  the  below Registry value  and checkpoint in the Quorum Drive’s  in SQL Server Registry settings. We  did it successfully in sql server 2012 environment by  used the Microsoft support’s article

Method

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:
  2. Locate the following registry key:

HKEY_LOCAL_MACHINE\Cluster\Resources

  1. Examine the Name column of the registry entries.

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

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

  3. Locate the checkpoint file name. To do this, follow these steps:
    1. Locate the following registry key:

HKEY_LOCAL_MACHINE\Cluster\Resources\{GUID}\RegSync

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

  • o Example 1
    To correct the invalid path of the Master.mdf file, follow these steps:
  1. Locate the following registry key:

HKEY_LOCAL_MACHINE\1\Parameters

  1. Correct the

SQLArg0

key.

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

HKEY_LOCAL_MACHINE\1\SuperSocketNetLib\Via

  1. Change the value of the Enabled entry from 1 to 0.
  2. 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.

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: