Installing Clustering on Multiple Nodes
When I talk about installing SQL Server 2005 clustering on multiple nodes, I am referring to two different scenarios. They include:
A 2-node active/active cluster, where you are running a single instance of SQL Server 2005 on each node. If one of the two active nodes should fail, then the failed active instance would failover to the other active node, with the end result that you are running two active instances of SQL Server 2005 on the same physical node.
A 3-node to 8-node SQL Server 2005 cluster where one of the nodes is designated as a passive node for failover purposes, and the rest are active nodes, with each one running a single instance of SQL Server 2005. Should any of the active nodes fail, then the failover would go to the designated passive node to run on.
Installing multiple instances of SQL Server in a cluster is virtually identical to installing a SQL Server 2005 cluster as described above. In general, here is what you need to know about installing multiple instances of SQL Server 2005 in a cluster:
- All of the nodes in the cluster should have identical hardware and software, and be configured identically.
- You will need a hub or switch for the private network connection among the nodes.
- You will need a separate shared drive for each instance of SQL Server 2005 installed. These are besides the shared drive required for the quorum. You only need one quorum drive for your cluster.
- You will need distinct virtual names and IP addresses for each SQL Server 2005 instance.
- Each SQL Server 2005 instance must be in its own distinct resource group in the cluster.
- You will need to run SQL Server 2005 Setup for each separate instance of SQL Server 2005 you want in the cluster.
- You will need to configure each active node, should a failover occur, to failover to the designated passive node.
Because running more than a single instance of SQL Server 2005 on a cluster is complex, I highly recommend that you build this cluster from scratch, and test it thoroughly before putting it into production.
Test, Test, and Test Again
Once you have installed SQL Server 2005 clustering on your nodes, you need to thoroughly test the installation, just as you did after first installing Windows Server 2003 Clustering. But not only do you want to test SQL Server 2005 clustering, you also want to test how your clients “react” to failovers. Because of this, the following testing section is very similar to the one you previously read, but has been modified to meet the more complex needs of the additional client testing you need to do.
Below are a series of tests you can perform to verify that your SQL Server 2005 cluster, and their clients, works properly during failover situations. After you perform each test, verify if you get the expected results (a successful failover), and also be sure you check the Windows log files for any possible problems. If you find a problem during one test, resolve it before proceeding to the next test.
Preparing for the Testing
Identify a workstation that has Cluster Administrator on it, and use this copy of Cluster Administrator for interacting with your cluster during testing.
Now for the hard part. Essentially, you need to test how each client will be accessing your SQL Server 2005 cluster. In other words, you want to test to see what will happen to each client should a failover occur. Some client software deals with clustering failovers automatically, while others choke and die. The only way to know for sure is to test them. Of course, to test your applications, you will have to install the appropriate databases on the cluster before you begin.
To test them, you must first identify all the client applications, which might be one product, or a dozen products. Each of these products will have to be configured to access the virtual server name (and IP address) on the new SQL Server instance. In addition, for the clients to work, you will have to have the appropriate databases restored or installed on the new cluster. Yes, this is a lot of work. But this is necessary if you want a highly available clustering solution you can count on.
Once you have at least one copy of each of your client applications connected to the SQL Server 2005 instance, you are ready for testing. Keep in mind, that while testing, you are testing multiple things, including the Windows Server 2003 cluster, the SQL Server 2005 cluster, and the client applications.
Move Groups Between Nodes
The easiest test to perform is to use Cluster Administrator to manually move the cluster and SQL Server resource groups from the active node to a passive node, and then back again. To do this, right-click on a resource group and then select Move Group. This will initiate the move of the resources groups from your active node to the designated passive node.
Once this happens, check Cluster Administrator and each of the client applications. Each should continue to operate as if no failover had occurred. Cluster Administrator should pass this test easily. The clients are another story. You will need to check each client to see if they continue to work as before. If not, you need to determine why not, which is not always easy. Most clients that stop working after a failover will reconnect if you exit and restart the client.
Once the group has been successfully moved from the active node to a passive node, then use the same procedure above to move the group back to the original node. And as before, check Cluster Administrator, the clients, and the Event Logs to see if there were any problems. If you have Cluster Service or SQL Server 2005 problems due to the test failover, you need to resolve them now before proceeding. If you have a client problem, you can continue with your testing and try to resolve them later. In most cases, if a client fails this first test, it will fail all of the tests.