Explore the benefits and drawbacks of clustering SQL Server 2000

It is advantageous to have a clustered SQL Server environment in terms of high availability; however, high availability does come with certain tradeoffs. Tim Chapman discusses the benefits and drawbacks of clustering in SQL Server 2000.

A server cluster is a group of two or more physically separate servers working together to form one "virtual" server. A Microsoft service called Microsoft Cluster Services (MSCS) is used to manage the clustered server(s). A SQL Server cluster is a virtual server composed of two or more server machines (nodes) running SQL Server. If one node in the cluster fails, another machine in the cluster assumes the responsibilities of the downed node.

It is a common misconception that a SQL Server cluster is able to load balance between the two nodes in the cluster. As useful as that would be, it is untrue. This also means that there really is no gain in performance from clustering your SQL Server. Clustering in SQL Server is only available for failover capabilities. A failover is when one machine loses its responsibility to run its SQL Server instance. This loss of responsibility can be due to hardware failure, service failure, manual failover, or a plethora of other reasons.

Why cluster your SQL Server environment?

A clustered SQL Server environment is desirable in terms of availability. In the event of a failover, the time it typically takes to failover the database instance from one server to another is very low, usually ranging from three to seven seconds; and this failover is typically transparent to end users of the database, although connections will need to be reestablished. This low failover cost also enables you to perform maintenance on the nodes in the cluster without requiring that the server be totally inaccessible.

Types of SQL Server clusters

There are two flavors of SQL Server clusters: Active/Passive and Active/Active. Below is a description of each (the description is based on a two-node SQL Server cluster).

In this type of cluster, only one node has control of the SQL Server resources at one time. The other node is continuously in standby mode waiting for a failover to occur. In the event of a failover, the standby node will acquire control of the SQL Server resources.

Benefit: Because only one instance is being run on a server, no server will be required to assume two SQL Server instances in the event of a failover, so performance should not be degraded.

Drawback: Because only one instance of SQL Server is being run on the virtual server, one server will always be in standby mode and idle. This means that you are not getting the most out of the money you used for the hardware.

In this type, each node in the cluster is running a separate and active instance of SQL Server. In the event of a node failure, the other node is able to take control of the failed node's SQL Server instance. This healthy node will then be running both instances of SQL Server—its own instance and the failed node's instance.

Benefit: With this configuration, you are fully using all of your hardware. Rather than having one server that is operational and another that is in standby mode waiting for a failover, both servers are operational, which allows you to get use out of the machines you have purchased.

Drawbacks: If failover occurs and one server is running both SQL Server instances, performance can be adversely affected. However, this slower performance is probably better than the virtual server totally being down. An additional drawback is that this configuration requires more licenses to be purchased than with an Active/Passive cluster. Because the cluster is running two active instances of SQL Server, you will be required to purchase the licenses for two separate server machines, which depending on your situation, may be a deterrent.

Cluster considerations

It is advantageous to have a clustered SQL Server environment in terms of high availability; however, high availability does come with certain tradeoffs.

Weekly SQL tips in your inbox
TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

First, it is typically very expensive to create a clustered SQL Server environment. This is because the nodes in the clustering need to conform to the hardware compatibility list for clustered nodes. Also, a complex network is required for setup, and the machines need to be close to identical, along with a required shared disk subsystem for the database files. A storage area network (SAN) is a decent choice for this subsystem, but a SAN is not required and is an expensive option. Also, if you are running an Active/Active cluster, you will need a SQL license for each processor for each machine running an instance of SQL Server in the cluster.

Because local clustering is typically confined to the same geographic area, a natural disaster could potentially leave your cluster useless. In that event, you would need to convert to your disaster recovery site for continued operations. It is possible to set up geographically dispersed SQL Server clusters, but the setup is much more complex and expensive.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.