Advantages and disadvantages of SQL Server clustered servers

A "virtual" server made up of two or more physically separate servers is called a clustered server. A Microsoft service called the Microsoft Cluster Service (MSCS) can manage cluster servers. A SQL Server cluster is a virtual server consisting of two or more servers (nodes) running SQL Server. If one node in the cluster fails, another node in the cluster assumes responsibility for the failed node.

It is a common misconception that a SQL Server cluster can bring load balancing to two nodes in a cluster. Although this may seem useful, it is not true. This also means that cluster SQL Server does not really improve performance. Cluster SQL Server can only provide failover capabilities. Failover is when one machine in the system loses its function, and another machine takes over the instance of SQL Server that runs it. This failure may be due to hardware failure, service failure, manual failure, or various other reasons.

Why cluster SQL Server environments?

In terms of practicality, the clustered SQL Server environment is satisfactory. When a failover occurs, the time to transfer the database instance from one server to another is very short and typically takes only 3-7 seconds. Although a connection needs to be rebuilt, failover processing is usually transparent to the end-user of the database. Low cost of failover can also help you maintain the nodes in the cluster without causing the server to be completely inaccessible.

SQL Server Cluster type

There are two types of SQL Server clusters: Active/passive clusters and active/active clusters. They are described in the following sections (instructions are based on a two-node SQL Server cluster).

Active/Passive Cluster

In this type of cluster, only one node at a time controls the SQL Server resource. The other node has been in standby mode, waiting for the failure to occur. When a failover occurs, the standby node obtains control of the SQL Server resource.

Advantage: Because only one instance of the server is running, there is no need for additional servers to take over two instances of SQL Server in case of failover, and performance will not degrade.

Disadvantage: Because only one instance of SQL Server is running on a virtual server, another server always handles standby mode and idle state. This means you're not making the most of the hardware you buy.

Active/Active cluster

In this type of cluster, each node in the cluster runs an independent and active instance of SQL Server. When a node failure occurs, another node can control the instance of SQL Server where the failed node occurred. The normal node then runs two instances of SQL Server-its own instance and the failed instance.

Advantage: With this configuration, you can make the most of your hardware. In such a system, two servers are running, not just one server running, while the other is in standby mode waiting for failure, so you can make the most of the machines you buy.

Disadvantage: If a failover occurs, a single server running two instances of SQL Server will be adversely affected by performance. However, performance degradation is always much better than virtual server failure altogether. Another fault with this configuration is that it requires more licenses to purchase than active/passive clusters. Because the cluster is running two active SQL Server instances, this requires you to purchase two separate server licenses. In some cases, this may also be a hindrance to you.

Cluster considerations

The clustered SQL Server environment has some advantages in terms of high availability. However, high practicality does come with some kind of compromise.

First, creating a clustered SQL Server environment is very expensive. This is because the nodes in the cluster must conform to the list of compatibility for the cluster nodes. Also, a complex network needs to be built, and the machine must be configured almost identically, while the disk subsystem of the database file needs to be shared. The Storage Area Network (SAN) is a good choice to build this subsystem, but sans are not necessary and expensive. In addition, if you are running an active/active cluster, you need to purchase a license for the processor in each machine in the cluster that is running instances of SQL Server.

Because local clusters are mainly confined to the same geographical area, natural disasters may cause the cluster to fail completely. In that case, you need to move to the disaster recovery site to continue. You can also build geographically dispersed SQL Server clusters, but such systems are more complex and expensive.