Reviewing and Testing SQL Server Cluster Configuration for Failover Scenarios
Windows Clustering and SQL Server allow you to run SQL Server in Active/Active mode where each node of the cluster is “active” and running one or more SQL Server instances. This would allow you, for example, to have the MessageBox database on one node and all other BizTalk Server databases on the other node. This allows you to maximize cluster hardware usage.
If you use this configuration, however, you must verify that each node can simultaneously handle the load of all SQL Server instances during a SQL Server cluster node failover.
Evaluating Failover for an Active/Active Cluster
Considerations when verifying that a single node can handle the load of all SQL Server instances in the event of a SQL Server cluster node failover include:
Does the failover node have sufficient CPU resources?
Does the failover node have sufficient memory?
Is there sufficient network bandwidth?
Can the failover node handle the increased disk I/O contention?
The following scenarios should be evaluated when testing failover:
Power failure on the active server
Power failure on the passive server
Loss of disk connection
Broken public network connection on the Active node
Broken private network connection on the Active node
Broken public network connection on the Passive node
Broken private network connection on the Passive node
Failed SQL Server service
Failed SQL Server Agent service
Using an Active/Active/Passive Cluster
If you determine that one node cannot handle all SQL Server instances in a failover scenario, an alternative is to use an Active/Active/Passive clustering model. The Active/Active/Passive clustering model greatly increases the likelihood that there will always be one Passive node available for failover scenarios.