Active/Passive Failover Configuration

You can configure Microsoft® SQL Server™ to run an active/passive virtual SQL Server on a Microsoft Windows NT® Clustering Service cluster. This configuration gives applications resiliency to failure with optimal performance.

The following illustration shows the main components of an active/passive failover configuration.

The main components are:

• The individual servers

  These are the physical Windows NT Servers hosting SQL Server. One acts as a primary server, the other acts as a secondary server. The primary server can view and access SQL Server files and data. The secondary server computer controls only the SQL Server virtual server in a failover situation, recovering the resources and workload of the active server.

• Each server’s local hard drive

  A copy of Windows NT, Enterprise Edition and Clustering Service is installed on each server’s local hard drive.

• One or more external shared drives

  In this configuration, one copy of SQL Server is installed on a drive shared by the two servers. Under usual conditions, the secondary server has no access to the shared drives. Only in a failover situation does the secondary server take over the SQL Server files and data on the shared drive. Windows NT Cluster Services permits only moving the physical device between servers. All logical drives will move with the physical device as it moves from server to server.

• A network connection between the two servers

  A working connection is necessary so that each server can detect whether the other server in a failover configuration is out of service. At regular intervals, the secondary server queries the primary server to see if it is functioning. If a query is not answered, a second query is sent to attempt logging onto the server If this query is not answered, a failure of the active server is assumed, and the secondary server takes over the functioning of the primary server.

• One SQL Server virtual server

  A virtual server is the composite entity to which clients connect. The virtual server has a network name, IP address, disk array, peripherals, and services. Clients are unaware of which physical server in the failover configuration currently has control of SQL Server (that is, the identity of the physical server). When one physical server fails, all services and resources migrate to the remaining server.

Summary of Steps: Setting Up an Active/Passive Failover Configuration

The following steps assume a new installation. To set up an active/passive virtual server configuration with SQL Server, you must:

• Install and configure a separate copy of Windows NT, Enterprise Edition on each server.
• Set up servers to communicate with each other over a network.
• Install Clustering Service (available on Windows NT) on both servers; first on the primary server, then on the secondary server. When installing Clustering Service on the secondary server, you should add that server as part of the cluster that includes the primary server.
• Install and configure any additional software required as a prerequisite of SQL Server installation, which includes the Windows NT 4.0 Service Pack 4, and Microsoft Internet Explorer version 4.01 Service Pack 1 or later. These components need to be installed on both servers.
• Install and configure a copy of SQL Server on a node owned by the primary server. Shut down and restart the primary server.
• Run the SQL Server Failover Setup Wizard for SQL Server on the primary server. This installs the software needed for SQL Server to interface with Clustering Service.
• Verify that the servers and cluster are set up properly by forcing the group to failover and by connecting a client.

For more information about these steps, see SQL Server Failover Support.

Note In this release, for active/passive failover installations using the TCP/IP network protocol, SQL Server running as nonclustered on the secondary node can only be configured to listen on one IP address. This only affects the installation where the secondary node is configured with multiple network interface cards (NICs) and SQL Server is configured to listen on more than one of the cards simultaneously.

If you want to add an existing server to a cluster and you are using replication on the server being added, you must uninstall replication. You can save the replication configuration to a script file, and reinstall replication after the server has been added to the cluster.

If you have an existing failover configuration running SQL Server version 6.5 and want to upgrade to SQL Server 7.0, you must first uncluster by running cluster setup from the SQL Server 6.5, Enterprise Edition compact disc, upgrade the software, recluster and then run the SQL Server 7.0 Failover Setup Wizard again.

See Also