SQL Server 2000 & 2005 Clustering

4-Node Clustering Active / Passive Failover Scenario

When failover occurs on Node X, SQL Server Instance A will get transferred with all its running processes, connections, and responsibilities to Passive Node Y, and now Node Y will be an Active Node. When failover occurs on Node XX, SQL Server Instance AA will get transferred with all its running processes, connections, and responsibilities to Passive Node YY, and now Node YY will be an Active Node.

Active / Active

An Active/Active SQL Server cluster means two separate SQL Server instances are running on both nodes of a two-way cluster. Each SQL Server acts independently, and users see two different SQL Servers instances. If one of the SQL Servers in the cluster should fail, then the failed instances of SQL Server will failover to the remaining server. This means that then both instances of SQL Server will be running on one physical server, instead of two. As you can imagine, if two instances have to run on one physical server, performance can be affected, especially if the server’s have not been sized appropriately. Remember that two separate SQL Server instances in this configuration are entirely isolated entities by default.

If all severs in a cluster are active and a node fails, the applications and services running on the failed node can be transferred to another active node. Since the server is already active, the server will have to handle the processing load of both systems. The server must be sized to handle multiple workloads or it may fail as well.

2-Node Clustering Active / Active Scenario

In this case, let's look at an example of two nodes, Node X and Node Y. Node X and Y both will be configured as Active Nodes, Primary Owner of SQL Server Instances A and B on each of them. As you can see below, Node X and Y both are active and running an instance of SQL server on each of them.

2-Node Clustering Active / Active Failover Scenario

When failover occurs on Node X, SQL Server Instance A will be  transferred with all its running processes, connections, and responsibilities to Active Node Y, and now Node Y will have to share all its memory, CPU and network resources with Instance A and B.

4-Node Clustering Active / Active Scenario

In the 4-node configuration illustrated below, where nodes X, Y, XX and YY are configured as active and failover could go to between nodes X and Y or nodes XX and YY, this could mean configuring servers so that they use about 25% of CPU and memory resources under average workload. In this example, node X could fail over to Y or node XX could fail over to YY.

4-Node Clustering Active / Active Failover Scenario