Clustering SQL Server 2005

Believe it or not, the procedure to install a SQL Server 2005 instance onto a cluster is one of the easiest parts of getting your SQL Server 2005 cluster up and running. The SQL Server 2005 setup program is used for the install, and it does the hard work for you. All you have to do is make a few (but critically important) decisions, and then sit back and watch the installation take place. In fact, the setup program even goes to the trouble to verify that your nodes are all properly configured, and if not, will suggest how to fix any problems before the installation begins.

When the installation process does begin, the setup program recognizes all the nodes, and once you give it the go-ahead to install on each one, it does, all automatically. SQL Server 2005 binaries are installed on the local drive of each node, and the system databases are stored on the shared array you designate.

In the next section are the step-by-step instructions for installing a SQL Server 2005 instance in a cluster. The assumption for this example is that you will be installing this instance in a two-node active/passive cluster. Even if you will be installing a two-node active/active or a multinode cluster, the steps in this section are virtually the same. The only real difference is that you will have to run SQL Server 2005 setup for every instance you want to install on the cluster, and you will have to specify a different logical drive on the shared array.

Clustering SQL Server

To begin installing your SQL Server 2000 cluster, you will either need the installation CD or DVD. You can either install it directly from the media, or you can copy the install files from the media to the current active node of the cluster, and run the setup program from there.

To begin the installation, run Setup.exe, and after an introductory screen, you will get the first install screen, shown in Figure 20-10.

Figure 20-10

This screen is used to list what prerequisites need to be installed before SQL Server 2005 can be installed. The number of components may vary from the Figure 20-12, depending on what you have already installed on your nodes. What is interesting to note here is that these prerequisite components will only be installed immediately on the active node, and they will be installed on the passive node later during the installation process. This is done automatically and you don't have to worry about it.

Click Install to install these components, and when they are installed, you will get a screen telling you that they were installed successfully, and you can click Next to proceed. On occasion, we have seen these components fail to install correctly. If this happens to you, you will have to troubleshoot the installation. Generally speaking, try rebooting both nodes of the cluster and try installing them again. This often fixes whatever caused the first try to fail.

Once the prerequisite components have been successfully installed, the SQL Server Installation Wizard launches. Click Next to proceed. The next step is for the SQL Server Installation Wizard to perform a System Configuration Check, shown in Figure 20-11. This is very similar to the check that was performed with clustering services was set up when you installed Windows Clustering. As before, ideally you want all checks to be successful, with a green icon. If you get any yellow warning or red error icons, you need to find out what the problem is, and correct it before proceeding. In some cases, yellow warning icons can be ignored, but red error icons cannot. If you have any yellow or red icons, you may have to abort the setup process, fix the problem, and then restart the setup process. Assuming all is well, click Next to proceed.

Figure 20-11

The next screen is the Registration screen where you enter your company name and license key, if applicable.

Next, you must select the SQL Server 2005 components to install. See Figure 20-12.

Figure 20-12

We won't go into a lot of detail on this screen, because this same material is covered in Chapter 2. We do want to point out the options Create a SQL Server failover cluster and Create an Analysis Server failover cluster (currently grayed out). Since you are creating a SQL Server 2005 cluster, you must select the Create a SQL Server failover cluster. And if you are going to install Analysis Services (we are not in this example) then you must select Create an Analysis Server failover cluster. Once you have selected all the components you need to include, click Next.

As with any install of SQL Server 2005, the next step is to select the name of the instance to be installed, as shown in Figure 20-13. You can choose between a default instance and a named instance. Click Next to proceed.

Figure 20-13

Now, here is a very important step, shown in Figure 20-14. This is when you enter the name of the virtual SQL Server 2005 instance you are currently installing. This is the name that clients will use to connect to this instance. Ideally, you have already selected what name to use that makes the most sense to your organization. Click Next to proceed. If you ever need to change this virtual name, you will have to install and then reinstall SQL Server 2005 clustering.

Figure 20-14

The next step is also a very important screen, shown in Figure 20-15. This is where you enter the virtual IP address for this instance of SQL Server 2005. Like the cluster virtual name, it is used by clients to connect to this instance of SQL Server 2005. The IP address must belong to the same subnet as the IP addresses used by all of the nodes.

Figure 20-15

In addition, in this screen you must select the network to be used for the public network (the network used by the clients to connect to this instance of SQL Server 2005). All of the available networks will be available from the drop-down box next to Network to use. If you had previously taken our advice to name the public and private networks, Public and Private, respectively, it will be very easy for you to select the correct network, as we have above.

Once you have entered the IP address and selected the public network, click Add, so that the information you just selected is in the Selected networks and IP addresses box; then click Next.

In the Cluster Group Selection screen, shown in Figure 20-16, select the SQL Server Group as the group where you want to create the SQL Server resources. In addition, be sure that the Data files will be created on the correct logical drive of the shared array using the folder name you choose. Select Next to proceed.

Figure 20-16

In the Cluster Node Configuration screen, shown in Figure 20-17, you get to specify which nodes you want to install this instance of SQL Server on. Because our example is for only two nodes, the default setting works for us. Notice that under Required node is SQL2005A, which is the name of the physical node where we are running the setup program. And under Selected nodes is SQL2005B, the second physical node in your two-node cluster. Select Next to proceed.

Figure 20-17

In the Remote Account Information screen, shown in Figure 20-18, you must select an account (with password) that has administrative rights on all of the nodes where we want to install this instance of SQL Server 2005. This can be any domain account that is an administrator of all the nodes. Select Next to proceed.

Figure 20-18

The Service Account screen is identical to the screen you see when you install SQL Server 2005 on a non-cluster, and it is configured the same. Click Next to proceed.

In the Domain Groups for Clustered Services screen, shown in Figure 20-19, you must select pre-existing global domain groups that are used to contain the startup account for each clustered service. You can choose to add all three services to the same global domain group, or to create separate global domain groups, one for each service, as has been done above. Once you have selected appropriate domain groups, click Next to proceed.

Figure 20-19

The next four screens of the Installation Wizard, not shown here, are the same as for any other installation of SQL Server 2005, and are discussed booking Chapter 2. After you have completed these screens, the installation of this instance of SQL Server 2005 begins, and you see the Setup Progress screen, shown in Figure 20-20.

Figure 20-20

The installation process will take some time as it is installing the binaries on both nodes of the cluster and installing the system data files on the shared array. The Setup Progress Screen shows the status of the first node's install. If you want to see the status of the second node's install, you can change the drop-down box next to Node to the second node and watch its progress.

As the installation proceeds, you will want to see all green icons next to each installation step. If any step should fail, then the entire installation process will need to be rolled back, any problems fixed, and SQL Server 2005 installed fresh. In most cases, canceling a bad installation will uninstall what has already been installed, but not always. Sometimes, if the installation breaks; it just dies and a rollback of what has been done so far will not occur. If this is the case you can either choose to reinstall on top of the existing bad install (which often does not work), manually uninstall the failed installation (check Microsoft's Web site for assistance in this area), or rebuild your cluster from scratch (starting with the operating system).

It the install was a success, you will see a final screen, where you can click Finish.

Clustering Analysis Services

SQL Server 2005 Analysis Services can be clustered just like SQL Server 2005, and in fact, is installed using the same setup program used to install SQL Server 2005. The following are some points to keep in mind if you decide to cluster SQL Server 2005 Analysis Services.

• SQL Server 2005 Analysis Services can be installed by itself or with SQL Server 2005. Because some of the features of Analysis Services require components of the SQL Server 2005 database engine, it is generally a good idea to install both of them in your cluster.

• SQL Server 2005 Analysis Services is installed using the same setup program as SQL Server 2005. When running the setup program, you select, or not select, Analysis Services to be installed in the Components to Install screen.

• Because SQL Server 2005 Analysis Services needs to store program files, data files, and shared files, you will have to specify the location of your shared array, where they will reside. These files must reside on a shared array if you want Analysis Services to run after a failover. To specify the location of the shared array, you must select the Advanced button from the Components to Install screen in the setup wizard.

Other than these points, installing SQL Server 2005 Analysis Services in a cluster is virtually identically to installing SQL Server 2005 in a cluster.

Installing the Service Pack and Hot Fixes

Once you have installed SQL Server 2005 clustering, your next step is to install the latest SQL Server 2005 service pack and hot fixes, which can be downloaded from Microsoft's Web site. Installing a service pack or hot fix is fairly straightforward because it is cluster-aware. Once the service pack or hot fix setup program is started, it detects that you have a cluster and will upgrade all nodes simultaneously. Once setup is complete, you may need to reboot your servers and failover the nodes. Generally, once you have run the service pack, you should reboot the active node first. Once it has rebooted, you reboot the passive node. This way, failover and failback is automatic.

Checking the SQL Server 2005 Installation From Cluster Administrator

Once an instance of SQL Server 2005 clustering is installed, you can view its cluster resources by going to Cluster Administrator and opening up the SQL Server Group resource.

Figure 20-21 shows the cluster resources for the SQL Server 2005 cluster you just built. You can see all of the names of the resources, their state, and which node the resources are running on. As we have already mentioned, Cluster Administrator is a great tool for seeing if all the resources are up and running and which node is the current active node.

Figure 20-21

Here is a brief run-down on each of the SQL Server 2005 cluster resources:

• Disk F: This is the shared disk array where the SQL Server data files and logs are stored.

• SQL Network Name (sqlcluster): This is the virtual SQL Server name used by clients to connect to this clustered instance of SQL Server 2005. The name "sqlcluster" is the name we have assigned this cluster instance, and will not be the same as your cluster, unless you name yours the same as ours.

• SQL IP Address (sqlcluster): This is the virtual SQL Server IP address used by clients to connect to this clustered instance of SQL Server 2005. Again, the name "sqlcluster" is the name of the virtual server, and is the one we have used for this cluster. Your name will most likely be different.

• SQL Server: This is the SQL Server service.

• SQL Server Agent: This is the SQL Server Agent service.

• SQL Server Fulltext: This is the SQL Server Fulltext service. Even though you may not use this service, it is automatically installed as a cluster resource.

Installing Clustering on Multiple Nodes

When we talk about installing SQL Server 2005 clustering on multiple nodes, we are 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, the failed active instance would fail over 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. If any of the active nodes fails, 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 we described. 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, 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 will 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, we 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 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.

The following 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

As with your previous cluster 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 that 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.

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

Manually Initiate a Failover in Cluster Administrator

This test is also performed from Cluster Administrator. Select any of the resources found in SQL Server Group resource group (not the group itself), right-click it, and select Initiate Failure. Because the cluster service always tries to recover up to three times from a failover, if it can, you will have to select this option four times before a test failover is initiated.

As before, after the first failover, check for any problems; then failback using the same procedure; then check again for problems.

Manually Fail Over Nodes by Turning Them Off

Turn off the active node. Once this happens, watch the failover in Cluster Administrator and the clients. As before, check for any problems. Next, turn on the node and wait until it boots back up successfully. Then turn off the now current active node by turning it off hard. And again, watch the failover in Cluster Administrator and the clients, and check for problems. Turn the node back on when done.

Manually Fail Over Nodes by Breaking the Public Network Connections

Unplug the public network connection from the active node. This will cause a failover to a passive node, which you can watch in Cluster Administrator and the clients. Check for any problems. Now plug the public network connection back into the server, and unplug the public network connection from the now active node. This will cause a failover to the current passive node, which you can watch in Cluster Administrator. And again, watch the failover in Cluster Administrator and the clients, and check for problems. Once the testing is complete, plug the network connection back into the server.

Manually Fail Over Nodes by Breaking the Shared Array Connection

From the active node, remove the shared array connection. This will cause a failover that you can watch in Cluster Administrator and applications. Check for any problems. Next, reconnect the broken connection from the now active node, remove the shared array connection. Watch the failover in Cluster Administrator. And again, watch the failover in Cluster Administrator and the clients, and check for problems. When done, reconnect the broken connection.

If you pass all of these tests the first time, it would almost be a miracle. But miracles do happen. If you run into problems, you have to figure them out before continuing.