Lesson 2: Using Failover Clustering

3 4

SQL Server 2000 failover clustering can be used to provide almost instantaneous availability of a secondary node in a failover cluster configuration in case the primary node fails for any reason. Failover clustering provides a higher level of availability than the use of standby servers. In this lesson, you will be introduced to the Microsoft Cluster Service (MSCS). You will learn about the use of active/active and active/passive failover clusters. You will also learn how to create and configure a SQL Server virtual server on a failover cluster. Finally, you will learn about maintaining a failover cluster.

---

After this lesson, you will be able to

• Install SQL Server on multiple nodes in a failover cluster
• Describe types of failover clusters
• Maintain a failover cluster
• Recover from a cluster node failure

Estimated lesson time: 15 minutes

---

Introducing MSCS

MSCS is a service of Windows 2000 and Windows NT 4.0 that is used to form server clusters (also called failover clusters). A server cluster is a group of independent computer systems working together as a single system and sharing a common storage system (generally a RAID system). Each computer frequently has identical hardware. Windows 2000 Data Center supports up to four nodes (each server in a cluster is called a node). Windows 2000 Advanced Server and Windows NT 4.0 Enterprise Edition support two nodes. Applications run on a server cluster, rather than on a specific node in the server cluster. The MSCS clustering software monitors each node and ensures that an application running on the server cluster continues to run regardless of the failure of an individual node. See Figure 16.21.

Figure 16.21

Failover cluster.

Note

---

Installing and administering a server cluster on Windows 2000 or Windows NT 4.0 is beyond the scope of this book.

Creating a Virtual Server on a Server Cluster

SQL Server 2000 runs as a virtual server across two or more nodes in the server cluster. To install SQL Server 2000 on an existing server cluster, run the SQL Server Setup program on one of the nodes in the server cluster. The Setup program automatically detects the presence of the server cluster. Select the Virtual Server option button in the Computer Name page to define a virtual server and install the executable files on each node in the server cluster (for example, to C:\Program Files\Microsoft SQL Server on each node). This enables each node to run SQL Server executable programs if necessary. The data files (for system and user databases) for the virtual server can only be installed in the shared RAID system. This enables the data files to be available to any node.

During setup, you define a virtual server name and assign an IP address to the virtual server. A virtual server can use multiple IP addresses to support multiple subnets that provide redundancy in the event of a network adapter or router failure. SQL Server clients connect to this virtual server, rather than to any individual node. By default, the virtual server runs on the node on which you are installing SQL Server, although you can select another node as the primary node.

One or more secondary nodes in the server cluster function as the failover nodes, ready to pick up the load of the primary node in the event of failure.

A node in a server cluster can be either active or passive. You can create up to 16 instances of SQL Server 2000 in a server cluster. Each instance can run on any node in the server cluster. You must plan to have sufficient hardware resources (memory and processor) in each failover node to provide services in the event a primary node fails. There are several ways to accomplish this.

• Active/passive clusters—In an active/passive cluster, one node serves exclusively as the failover node for another node. In the event of failure of the primary (active) node, the failover (passive) node has sufficient hardware resources to handle the load of the primary node with no degradation in throughput or performance. Using an active/passive configuration is generally required for mission-critical applications. However, in an active/passive cluster, the passive node in the server cluster is essentially unused unless the primary node fails.
• Active/active clusters—In an active/active cluster, each node serves as the primary node for a virtual server as well as the failover node for another virtual server. In the event of failure of one of the nodes, the remaining node (or nodes) must handle the load of both virtual servers. This generally means some degradation of service for both virtual servers until the failed node is repaired. You must plan to leave sufficient headroom on each active node to provide adequate service to all services running on the failover node in the event of a node failure. The use of an active/active cluster is a more cost-effective use of computer hardware when all nodes are functioning properly.

Planning Issues

There are a number of installation and configuration considerations you must be aware of when running SQL Server 2000 in a clustered environment.

• If you plan to use MS DTC in a clustered environment, you must run the Cluster Wizard on each node before you install SQL Server 2000. This enables MS DTC to run in clustered mode.
• If you have configured a minimum server memory setting for a node, you must ensure that the failover node has sufficient memory capacity to provide this minimum memory in the event of a failover event. Generally, you should allow SQL Server to dynamically allocate memory in a server cluster.
• If you are using AWE on one node, all nodes should have the same amount of AWE. In addition, ensure that the total value of the max server memory setting for all instances is less than the lowest amount of physical memory available on any of the nodes in the failover cluster.
• Ensure that the recovery interval is set to zero to allow SQL Server to set the recovery interval. This setting affects the length of time SQL Server will take to fail over to a failover node.
• Service account passwords for each SQL Server service must be identical on all nodes. If you change a password using SQL Server Enterprise Manager, it will change the password on all nodes automatically.
• If you use an internal disk controller, disable caching. To optimize performance, use an external disk controller that is certified for database use.
• On Windows NT 4.0, the domain user account used by the SQL Server and SQL Server Agent services must be a member of the local Administrators group on each node in the server cluster.
• If you are using replication, use the shared disk system for the snapshot files so they remain always available in the event of a node failure.

Disaster Recovery

In the event the primary node used by a virtual server fails, a secondary node takes over. A recovery of the database occurs automatically, and the failover node takes over and handles all user connections (user connections to not need to be reestablished). User processes are oblivious to the change, other than a minor wait for the failover to complete.

To recover, run the Setup program to remove the failed node from the configuration. This takes approximately one minute. Next, repair or replace the failed node. Finally, rerun the Setup program and add the rebuilt node. The Setup program takes care of the rest of the process. The active node does not have to be stopped during this process, so users can continue working.

Lesson Summary

Failover clustering provides the highest level of availability for SQL Server 2000 installations. Failover clustering is based on MSCS and is available only on Windows 2000 Data Center, Windows 2000 Advanced Server, and Windows NT 4.0 Enterprise Edition. When the SQL Server Setup program detects a server cluster, you can choose to install a virtual server, selecting one of the nodes in the server cluster as the primary node. If this node fails, the virtual server will automatically fail over to a secondary node. The secondary node can either be inactive or function as the primary node for another virtual server.