SQL Server Clustering Alternative Options

Simple two-node, active/passive clusters running a single SQL Server instance are only one of many options you have when clustering SQL Server. Two other popular options include active/active clustering and multi-node clustering. Additionally, it is available to cluster multiple instances of SQL Server on the same server. This article discusses these alternatives in detail.

Active/Active Cluster

The examples so far have described what is called an active/passive cluster. This is a two-node cluster in which there is only one active instance of SQL Server 2011. Should the active node fail, the passive node takes over the single instance of SQL Server 2011, becoming the active node.

To save hardware costs, some organizations like to configure an active/active cluster. Like active/passive, this is also a two-node cluster, but instead of only a single SQL Server instance running, there are two instances, one on each physical node of the cluster.

The advantage of an active/active cluster is that you make better use of the available hardware. Both nodes of the cluster are in use instead of just one, as in an active/passive cluster. The disadvantage is that when a failover occurs, both SQL Server instances are running on a single physical server, which can reduce performance of both instances where memory may need to be readjusted to ensure that each has adequate memory. To help overcome this problem, both of the physical servers can be oversized to better meet the needs of both instances should a failover occur. Chances are good, however, that the perfect balance will not be met and there will be some performance slowdown when failing over to the other node. In addition, if you have an active/active cluster running two SQL Server instances, each instance needs its own logical disk on the shared disk array. Logical disks cannot be shared among SQL Server instances.

In the end, if you want to save hardware costs and don’t mind potential application slowdowns, use an active/active two-node cluster.

Multi-node Clusters

If you think you will be adding even more clustered SQL Server 2012 instances in the future you may want to consider a third option: multi-node clusters. For the more conservative, a three-node cluster, or active/active/passive, is a good option that provides more redundancy and it won’t cause any application slowdown should a failover occur with the passive node. For those that don’t mind the complexity of large clusters, you can add even more nodes.

The number of physical nodes supported for SQL Server clustering depends on which version of the software you purchase, along with which version of the operating system you intend to use.

Purchasing the Right Software

One of the reasons it is important to research your clustering needs is that they directly affect what software you need, along with licensing costs. Following are your options:

• SQL Server 2012 Standard Edition (32-bit or 64-bit): Supports up to two-node clustering.
• SQL Server 2012 BI Edition (32-bit or 64-bit): Supports up to two-node clustering.
• SQL Server 2012 Enterprise Edition (32-bit or 64-bit): Supports up to sixteen-node clustering.

If you need only a two-node cluster, you can save by licensing Windows Server 2008 Enterprise Edition and SQL Server 2012 Standard Edition. If you want more than a two- node cluster, your licensing costs will escalate quickly because you will need SQL Server 2012 Enterprise Edition.

Number of Nodes to Use

As covered earlier, in a two-node cluster, a SQL Server instance runs on the active node, while the passive node is currently not running SQL Server but is ready to do so when a failover occurs. This same principle applies to multi-node clusters.

As an example, say that you have a three-node cluster. In this case, there are two active nodes running their own individual SQL Server instances, and the third physical node acts as a passive node for the other two active nodes. If either of the two active nodes fails, the passive node can take over. You can set up a failover preferred node to predetermine the failover sequence from node to node.

Now look at an eight-node cluster. In this case, you have seven active nodes and one passive. Should any of the seven active nodes fail, then the passive node takes over after a failover. In this case, with a large number of nodes, it is more preferable to have a passive node to avoid multiple node failures that cause the surviving nodes to carry all that additional workload.

In an active/passive configuration, the advantage of many nodes is that less hardware is used for failover needs. For example, in a two-node cluster, 50 percent of your hardware is used for redundancy; but in an eight-node cluster, only 12.5 percent of your cluster hardware is used for redundancy.

Ultimately, deciding how many nodes your cluster has should depend on your business restrictions like your budget, your in-house expertise, and your level of aversion, if any, to complexity. Some organizations have many different SQL Server instances that they need to cluster, but choose to use multiple two-node active/passive clusters instead of a single multi-node cluster, working under the impression that it is best to keep things as simple as possible.

Clustering Multiple Instances of SQL Server on the Same Server

As indicated in the types of clustering discussed previously, a single SQL Server instance can run on a single physical server, however, this is not a requirement. SQL Server Enterprise Edition can actually support up to 25 SQL instances on a single clustered configuration. This is a restriction of drive letter limitations though so you need mount points to achieve this. The effectiveness of this depends on the business requirements, the capacity of the hardware, SLAs, and the expertise of the IT organization managing it.

The purpose of clustering is to boost high availability. Adding many SQL Server instances to a cluster adds complexity, and complexity can increases risk and failover points in the solution. But complexity can also be managed depending on the IT expertise to support it; speak to your IT support when considering this option.