Active/Active Failover Configuration

In an active/active configuration, the cluster runs two independent copies of Microsoft® SQL Server™, one on each server. This enables each server to act as the primary server for one virtual server and as the secondary server for a second virtual server. Each virtual server has its own master, model, msdb, tempdb, and user databases.

In contrast to the active/passive configuration, the active/active configuration offers the advantage of maximizing the use of both servers, while still maintaining a failover solution. In this configuration, two separate physical drives are used, each with a copy of SQL Server. Under normal conditions, the primary server for each virtual server can view and access SQL Server files and data, and perform work. The backup server for each virtual server can view files and data only on the shared drive it owns.

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

The main components are:

• The individual servers

  These are the physical Microsoft Windows NT® Servers hosting SQL Server. Each physical Windows NT Server acts as a primary for one virtual server and a secondary server for a second virtual server. Within each virtual server, the primary server can view and access SQL Server files and data. The secondary server hosts the SQL Server virtual server only 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 Windows NT Clustering Service is installed on each server’s local hard drive.

• Two shared drives or shared arrays

  In this configuration, one copy of SQL Server is installed on each physical shared drive or array. Only the server (primary or secondary) that currently owns the physical device is capable of controlling or manipulating the data and files that reside on that device.

• A network connection between the two physical Windows NT Servers

  A working connection is necessary so that each server can detect whether the other server 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 failure of the server hosting SQL Server is assumed, and the secondary server takes over the functioning of the primary server.

• Two virtual servers

  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 functional server). When one server fails, all services and resources migrate to the remaining node. In an active/active configuration, there are two functioning virtual server configurations to which clients can connect.

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

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

• Perform the same steps for setting up a SQL Server in an active/passive failover configuration, to set up the first virtual server.
• Set up the second SQL Server virtual server. This procedure is the same as installing the first SQL Server virtual server, except that a different set of shared physical disks must be used (both copies of SQL Server cannot be installed on the same physical disk in the cluster). Make sure that cluster setup for the first SQL Server virtual server was completed and exited prior to starting the installation of the second SQL Server virtual server.
• Run the SQL Server Failover Setup Wizard for each copy of SQL Server on the cluster. This installs the software needed for SQL Server to interface with clustering.
• Verify that the servers and cluster are set up properly.

Important A separate copy of SQL Server must be installed on both nodes (primary and secondary servers) before creating a virtual server. To set up an active/active failover configuration, make sure the copy of SQL Server for the secondary server is installed on a Clustering Service shared drive, and not the server's local drive.

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

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 uncluster both 6.5 servers by running cluster setup from the SQL Server 6.5, Enterprise Edition compact disc, upgrade the software, and run the SQL Server Failover Setup Wizard again.

See Also