Rebuilding SQL Server Cluster Nodes

Wednesday Feb 2nd 2005 by Muthusamy Anantha Kumar aka The MAK
Share:

Active/Passive SQL Server 2000 clustering gives more reliability and fault tolerance to Production SQL Server environments. When a failure occurs, all of the resources fail over from the active node to the passive node and make the passive node active. This article explains how to rebuild the node that failed and attach it back to the cluster.

Active/Passive SQL Server 2000 clustering gives more reliability and fault tolerance to Production SQL Server environments. When a failure occurs, all of the resources fail over from the active node to the passive node and make the passive node active. This article explains how to rebuild the node that failed and attach it back to the cluster.

First, let's assume we have the CLUSTER server "VCLUSTER" with nodes "SQLNode1" and "SQLNode2." Further, we will assume the virtual name of the SQL server is SQLCLUSTERA and that VCLUSTER, SQLNode1, SQLNode2 and SQLCLUSTERA belong to the same domain "VDOMAIN."

In this example, the node "SQLNode1" has failed due to a major hardware failure, including local hard disks. Now all the failed resources have failed over from SQLNode1 to SQLNode2. [Refer Fig 1.0]

Click for larger image

[Fig 1.0]

Step 1 - Evicting the failed node

Open the Cluster Administrator and evict the node "SQLNode1" from the cluster "VCLUSTER." [Fig 1.1 and 1.2]


[Fig 1.1]


[Fig 1.2]

Step 2 - Install new hardware, network and update IP address.

Install new hardware, replacing the failed hardware and install the operating system in node "SQLNode1." After that, you have to setup a Public and Private network. [Refer Fig 1.3]


[Fig 1.3]

Verify the IP Address from the DNS [Fig 1.31] and update the IP information of the Public network.


[Fig 1.31]

Update the IP address of the private network as well.

Step 3 - Add the node "SQLNode1" to the domain.

Update the machine name and add the machine to the domain. [Refer Fig 1.4 and 1.5]


[Fig 1.4]


[Fig 1.5]

Step 4 - Assign drive letter to Quorum and Shared Disk.

Shut down the node "SQLNode2." Restart the node "SQLNode1." We have to shutdown the node "SQLNode2" so that the shared SCSI disk will be available for SQLNode1.

Now assign a drive letter for the Data shared disk and Quorum disk. [Refer 1.6]

Click for larger image

[Fig 1.6]

Step 5 - Add Logins.

Add the necessary logins and users in the node "SQLNode1" exactly as they
appear in "SQLNode2." [Refer Fig 1.7]

Click for larger image

[Fig 1.7]

Step 6 - Add the node "SQLNode1" to the cluster "VCLUSTER."

Now start the node "SQLNode2." Open Cluster Administrator and select "Add nodes to the cluster" [Refer Fig 1.8]


[Fig 1.8]

Click Next. [Refer Fig 1.9]


[Fig 1.9]

Type "SQLNode1" in the computer name and click "Add." [Refer Fig 2.0]


[Fig 2.0]

Click Next to continue. [Refer Fig 2.1]


[Fig 2.1]

Type the password for the corresponding account you used. [Fig 2.2]


[Fig 2.2]

Click Next. [Refer 2.3]


[Fig 2.3]

Click Next. [Refer 2.4]


[Fig 2.4]

Click Finish. [Refer Fig 2.5]


[Fig 2.5]

Now in the cluster administrator, you can verify the SQLNode1 resources. [Refer Fig 2.6]


[Fig 2.6]

Step 7 - Make sure you can failover both the nodes

This step is to make sure that we can failover the Cluster group from the node "SQLNode2" to "SQLNode1" and from "SQLNode2" to "SQLNode1."

Right click on the "Cluster group" and click "move group." [Refer Fig 2.7 and 2.8]


[Fig 2.7]

Click for larger image

[Fig 2.8]

Move all cluster groups again and point back to the node "SQLNode2."

Step 8 - Add the node "SQLNode1" to the SQL Cluster "SQLCLUSTERA"

Insert the SQL Server 2000 Enterprise CD in the CD Drive of the node SQLNode2 and run setup.

Type the SQL Server Virtual server name "SQLCLUSTERA." [Refer Fig 2.9]


[Fig 2.9]

Select "Advanced options." [Refer Fig 3.0]


[Fig 3.0]

Click Next. [Refer Fig 3.1]


[Fig 3.1]

Click Next. [Refer Fig 3.2]


[Fig 3.2]

Select "SQLNode1" and click on the "Add" button. [Refer Fig 3.3]


[Fig 3.3]

Enter the password. [Refer Fig 3.4]


[Fig 3.4]

The following message appears. [Refer Fig 3.5]


[Fig 3.5]

Click Finish. [Refer Fig 3.6]


[Fig 3.6]

Step 9 - Install the latest Service pack for SQL Server.

Download the service pack from the Microsoft website. Extract the file and run the setup.bat. [Refer Fig 3.7]


[Fig 3.7]

Type the SQLServer Virtual server name. [Refer Fig 3.8]

Click for larger image

[Fig 3.8]

Step 10 - Move the "SQL Group"

Step 10 - Move the "SQL Group" from the node "SQLNode2" to "SQLNode1" and vice versa and make sure it fails over properly. [Refer Fig 3.9 and 4.0]


[Fig 3.9]


[Fig 4.0]

Login to SQL Server and run the following commands to make sure SQL Server cluster is working properly. [Refer Fig 4.1]

xp_cmdshell 'Ping -a 127.0.0.1' to see which node is active.
select * from ::fn_servershareddrives()
select * from ::fn_virtualservernodes()


[Fig 4.1]

Conclusion

This article has explained how to rebuild a failed node and attach it back to the cluster.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved