SQL Server 7.0 Clustering

WARNING: Windows 2000 and SQL Server 7.0 clustering is an advanced topic, and you should have a good understanding of clustering before using any of these tips.

Don’t implement any of these tips into a production environment until you have successfully tested them in a test environment. Note that most of these tips are specific to SQL Server 7.0.

*****

Tip: For many administrative tasks, it is important to first remove SQL Server clustering from your cluster using the SQL Server Failover Cluster Wizard before performing the task at hand. Once the administrative task is complete, SQL Server clustering can be easily restored using the Wizard.

Explanation: Below are the steps you need to remove, and then re-install SQL Server clustering using the SQL Server Failover Cluster Wizard. Keep in mind that once you remove SQL Server clustering, that users will not be able to access the SQL Server cluster. This tip also assumes you are using Windows 2000 clustering.

Steps to Remove SQL Server Clustering Using the Failover Cluster Wizard

Log onto the primary cluster node using the SQL Server service account. This account should have local admin rights to both of the servers in the cluster.
If necessary, stop any non-SQL Server services that access SQL Server files. For example, some server monitoring software accesses SQL Server files for the purposes of monitoring.
Turn off the SQL Server Agent Service using the Cluster Administrator. Do NOT turn this service off using any other method.
Using Cluster Administrator, verify that all server resources are owned by the primary server. If this is not the case, move all shared server resources to the primary server node.
Using Cluster Administrator, stop the cluster service on the secondary cluster server node.
Using Cluster Administrator, take the SQL Server Resource Group off-line. Do this by right-clicking on the group and selecting “Off-line”.
Close Cluster Administrator.
Start the SQL Server Failover Wizard (in the SQL Server 7.0 program group).
Use the Wizard to remove the SQL Server virtual server. The steps are straight-forward and obvious.
Start the Cluster Administrator.
Go to the SQL Server Resource Group. In it you may find one or more disk resources (depending on how your disk resources are configured). Move these disk resource(s) from the SQL Server Resource Group to another resource group (it doesn’t matter which one).
Now delete the now empty SQL Server Resource Group by right-clicking on it and selecting “Delete”.
Bring the cluster service on the secondary node back online by right clicking on it and selecting “Bring Online”.
You are now done fully removing SQL Server clustering from your Windows 2000 cluster.

Steps to Re-Install SQL Server Clustering Using the Failover Cluster Wizard

Be sure the Windows 2000 cluster service is running successfully on both the primary and secondary nodes before beginning. Be sure you can successfully access all disk resources.
Start the SQL Server Failover Wizard (in the SQL Server 7.0 program group).
Using the wizard, set the IP address, subnet mask, and the name of the virtual SQL Server following the appropriate steps in the Wizard.
After the wizard is complete (it takes some time to complete), and you get a completion message, reboot the secondary cluster server node.
After the second cluster server node reboots, on the primary server, using Cluster Administrator, bring the SQL Server Resource group (its name is based on the name of the virtual server you entered in the previous steps) online by right clicking on the group and selecting “Bring Online”.

You are now done installing SQL Server clustering. You should now test the cluster by accessing it with Enterprise Manager. Once you can successfully connect to the cluster using Enterprise Manager, failover to the secondary cluster node to see if failover works. Test again to see if you can access the cluster using Enterprise Manager. And last, fail back to the primary cluster node, and then again test to see if Enterprise Manager can access the cluster.

Version: 7.0

Date Added: 5-15-2001

*****

Tip: Don’t restore the master database to a clustered SQL Server unless you have first removed SQL Server clustering.

Explanation: SQL Server services, such as the mssqlserver and the sqlserveragent service, should only be started and stopped using the Cluster Administrator. If you don’t follow this rule, it is possible to corrupt your SQL Server cluster. The problem with restoring the master database to a SQL Server cluster is that you have to start SQL Server in Single User Mode from the command line using the -m switch. And once you have restored the master database, SQL Server will automatically (without giving you any opportunity) stop SQL Server. This will result is corruption of your SQL Server cluster.

The work-around for this is to first remove SQL Server clustering from servers using the SQL Server 7.0 Cluster Wizard. This of course brings down SQL Server, but there is no way around that. Once SQL Server clustering is removed, you can then restore the master database. Once the master database has been successfully restored, you can then use the SQL Server 7.0 Cluster Wizard to re-install SQL Server Clustering to your cluster.

Version: 7.0

Date Added: 5-15-2001

Continues…

Leave a comment

Your email address will not be published.