SQL Clustering and Fail-Over Support

By taking advantage of the clustering capabilities of Windows 2000 Advanced Server, SQL Server 2000 provides the high availability and reliability required of an enterprise class database management system. You can install up to 16 instances of Microsoft SQL Server 2000 in a Microsoft cluster service.

As alluded to before, SQL Server 2000 implements fail-over clustering based on the clustering features of the Microsoft Clustering Service in Windows NT 4.0 EE and Windows 2000 Advanced Server. The type of MSCS fail-over cluster used by SQL Server consists of multiple server computers. Windows 2000 would normally handle up to two servers, whereas Windows 2000 Datacenter can handle up to four servers. The fail-over cluster shares a common set of cluster resources (or cluster groups), such as clustered disk drives. You can install SQL Server on as many nodes as you want. This is only limited by the operating system limitations. Each server in the cluster is called a node. Each node must be connected to the network and be able to communicate with each other. In addition, each node must be running the same version of MSCS. Figure 24.4 shows a typical SQL Server clustering con-figuration for a two-node cluster. The cluster nodes are named CL Node A and CL Node B.

Figure 24.4. SQL Server fail-over clustering.


When you install SQL Server on a clustered server using SQL Server Setup, you create it as a virtual server. Figure 24.5 shows the SQL Server Setup dialog box where you will be able to specify the creation of a virtual server. You must first install it from one of the cluster servers (CL Node A, in this example). Figure 24.5 shows the virtual server option grayed-out because MSCS hasn't been installed properly on the server yet. Therefore, the first step in creating a SQL Server clustering configuration entails installing the MSCS feature. After MSCS has been installed and a cluster has been configured (as explained earlier), you can create a virtual server such as one named SQL A.

Figure 24.5. SQL Server Setup Wizard for virtual servers.


It is this virtual server that the client applications will see and to which they will connect. When an application attempts to connect to an instance of SQL Server 2000 that is running on a fail-over cluster, the application must specify both the virtual server name and the instance name, such as SQL B\Instance 1. The application does not have to specify an instance name if the instance associated with the virtual server is a default instance because it has no name. Additionally, you cannot access the SQL Server by specifying the machine name and instance name because the SQL Server is not listening on the IP address of the local server. It is listening on the clustered IP addresses created during the setup of a virtual server.

As part of the SQL Server setup, you will identify the nodes available to run this SQL Server. As shown in Figure 24.4, the nodes CL Node A and CL Node B will be able to run SQLA virtual server. This allows either of these nodes to take over in the event of a hardware or network failure. You will now create another virtual server from the other cluster server node of CL Node B named SQLB. SQLB will also be identified to run on both CL Node A and CL Node B.

This configuration yields two virtual servers running in the MSCS cluster consisting of CL Node A and CL Node B. Each virtual server resides in a different MSCS cluster group, and each has a different set of IP addresses, a distinct network name, and data files that reside on separate sets of shared cluster disks. In this example, these resources are identified as Instance 1 data and Instance 2 data.

Now, if a failure does occur, the MSCS automatically handles the fail-over process. In the event of a fail-over, any transactions in process at the time of the fail-over are rolled back, and the clients must reconnect to SQL Server.

Don't confuse fail-over support in a clustering scenario with a standby server. SQL Server 2000 supports the concept of a standby server using techniques, such as data replication or log shipping, which are covered in other chapters.

Heading Off Issues

Many potential issues can arise during setup and configuration. Following are some things for you to watch out for:

  1. SQL Server service accounts and passwords should be kept the same on all nodes or the node will not be able to restart a SQL Server service.

  2. Drive letters for the cluster disks must be the same on both node servers. Otherwise, you might not be able to access a clustered disk.

  3. You must use Cluster Administrator in MSCS to automatically start a fail-over cluster.

  4. You might have to create an alternative method to connect to SQL Server if the network name is offline and you cannot connect using TCP/IP. The method is to use Named Pipes specified as \\.\pipe\$$\SQLA\sql\query.

  5. Very often, you will run into trouble getting MSCS to install due to hardware incompatibility. Be sure to check Microsoft's hardware compatibility list before you venture into this install.

Recovery Steps Example

Using the SQL Server clustering example in Figure 24.4, if failure is caused by hardware failure in CL Node A like a bad SCSI card, then the following summarized events occur:

  1. After CL Node A fails, the SQL Server 2000 fail-over cluster fails over to CL Node B.

  2. Run SQL Server Setup and remove CL Node A.

  3. Evict CL Node A from MSCS.

  4. Install new hardware to replace the failed hardware in CL Node A.

  5. Install/reconfigure the operating systems on CL Node A.

  6. Install MSCS and join the existing cluster.

  7. Run the SQL Server Setup on CL Node B and add CL Node A back to the fail-over cluster.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features