Using SQL Server Failover Support

Before installing SQL Server Failover Support, ensure the servers are configured properly and are ready to be installed. The following sections discuss information that supports a smooth installation.

Selecting Cluster Hardware

Refer to your Microsoft Cluster Server Administrator's Guide for a list of supported hardware configurations and hardware configuration information. Also, to ensure high availability operation, select Microsoft® Windows NT® Clustering Service compatible hardware from the Hardware Compatibility List (HCL). The list contains approved hardware configurations where a manufacturer has been able to validate a specific cluster configuration, two servers, an interconnect, and a storage solution. This list of clustering compatible servers can be found at http://www.microsoft.com.

Preparing Servers for SQL Server Failover Support

Building your server configuration to use SQL Server Failover Support can be done in two stages. The first stage is to prepare both servers for Microsoft® SQL Server™ version 7.0 installation. This stage requires selecting servers from the HCL and installing identical versions of Windows NT 4.0 Enterprise Edition, Microsoft Internet Explorer version 4.01 Service Pack 1, and Windows NT SP 4 on each server. After this is completed, you can install Clustering Service.

The second stage of preparation requires that Clustering Service be operational. Before installing SQL Server 7.0, ensure that a SQL Server resource group is identified and contains at least one shared disk resource. During the installation of SQL Server, be prepared to supply a static IP address and an SQL Server virtual server network name.

Naming Services and Assigning IP Addresses

Prior to installing Clustering Service and SQL Server Failover Support, ensure that you have all of the necessary IP addresses and subnet masks for your Windows Internet Name Service (WINS) and Domain Name Service (DNS) services, for proper name resolution. You are also required to provide a name for each node, each Clustering Service, and each instance of the SQL Server Failover Support service. Each server node can use Dynamic Host Configuration Protocol (DHCP) to obtain an IP address, but each SQL Server virtual server requires a separate static IP address for the virtual server.

Configuring New Installations of SQL Server 7.0 and MS DTC

Installing SQL Server 7.0 requires at least one clustering shared disk resource for Microsoft Distributed Transaction Coordinator (MS DTC). Before installing SQL Server, open Cluster Administrator and view a resource group that contains an IP address and a network name. If there is no disk resource in the group, drag a shared disk resource into the cluster group. If you have only one disk resource in your cluster, MS DTC and SQL Server can coexist on the same common disk resource.

Note If the MS DTC installation fails with no Compatible resource groups found, cancel the install, check that the SQL resource group contains an IP address and a shared disk resource, and then restart the installation process.

Configuring MS DTC for use with SQL Server 7.0 in a Clustered Configuration

In a single disk resource configuration, upgrading to SQL Server 7.0 causes SQL Server and MS DTC to be installed to the same cluster resource group. This configuration requires MS DTC and SQL Server 7.0 to share the same resources assigned to the cluster resource group including, the IP address, network name, and shared disk.

If there is a need to use the version switch to revert one or both nodes to SQL Server 6.5, you need to uncluster the node first. Using the version switch to revert to SQL Server 6.5 does not remove MS DTC. SQL Server 6.5 has a restriction that the cluster resource group it belongs to cannot host more than one service and cannot have more than one IP address. This restriction prevents any node reverted to SQL Server 6.5 from reclustering.

The same qualifications apply to a multiple shared disk configuration except that MS DTC and SQL Server 7.0 may be placed on separate disks in the group.

Configuring SQL Server Memory for a Cluster

SQL Server requires operating system resources to manage its databases. The most important of these resources is memory. To ensure adequate response times to clients during a failover and to ensure SQL Server Failover Support functions as expected, a review of memory requirements and anticipated server usage must be performed.

SQL Server allocates memory dynamically if the sp_configure option min server memory is set to 0. Using 0 as your min server memory value allows SQL Server to reallocate memory usage during a failover dynamically. Dynamic allocation of memory is important during a failover because it allows SQL Server to adjust to its new environment by optimizing its memory usage. In an active/active configuration, both servers running SQL Server will optimize their memory usage automatically during a failover.

If you are running other services on your cluster nodes or you are not using the automatic configuration option to manage SQL Server memory requirements, you may want to review and manage SQL Server virtual server resources during a failover. This can be done by reviewing Object Memory statistics in Windows NT Performance Monitor. If Windows NT Performance Monitor shows a large number of object memory page faults, consider configuring less memory for one or both SQL Server virtual servers, or stopping other programs or processes.

For example, assume that the SQL Server virtual server on node 1 uses 60 percent of the available memory. On node 2, the operating system requires 20 percent and two additional services each require 15 percent of the systems available memory continuously. The SQL Server virtual server on node 2 has the remaining 50 percent of the memory available for its use. The total memory usage of all of these services on node 2 is 100 percent.

If the SQL Server virtual server on node 1 fails over to node 2, the physical memory requirements for all services on node 2 will now be 160 percent. Both SQL Servers virtual servers will reallocate their memory requirements dynamically to the available memory, which is 50 percent, the amount of memory originally available to SQL Server. By viewing Windows NT Performance Monitor, you may decide to stop one or both of the additional services gaining 15 percent more memory for each service that is stopped.

Identifying SQL Server Failover Support Configuration Parameters

You need to identify which server will be the primary node and which server will be the secondary node for each installation of SQL Server. In an active/passive configuration, you will install only a single instance of the SQL Server virtual server. In an active/active configuration, you will install two instances of the SQL Server virtual server. When creating the SQL Server virtual server, you must identify these parameters for your installation.

To upgrade SQL Server 6.5 to SQL Server 7.0 on a cluster

To run Clustering Services setup on a new SQL Server 7.0 installation using the Failover Setup Wizard