Advantages and disadvantages of SQL Server Cluster Server

A virtual server composed of two or more physical independent servers is called a cluster server. A MicroSoft service called MicroSoft cluster service (MSC) manages cluster servers. An SQLServer cluster is a virtual server composed of two or more servers (nodes) running SQL Server. If a node in the cluster fails

A virtual server composed of two or more physical independent servers is called a cluster server. A MicroSoft service called MicroSoft cluster service (MSC) manages cluster servers. An SQL Server cluster is a virtual Server composed of two or more servers (nodes) running SQL Server. If a node in the cluster fails

A "virtual" server composed of two or more physical independent servers is called a cluster server. A MicroSoft service called MicroSoft cluster service (MSC) manages cluster servers. An SQL Server cluster is a virtual Server composed of two or more servers (nodes) running SQL Server. If one node in the cluster fails, the other node in the cluster is responsible for this faulty node.

It is a common misunderstanding that an SQL Server cluster can bring load balancing to two nodes in the cluster. Although this seems useful, it is incorrect. This also means that the cluster SQL Server cannot really improve the performance. Cluster SQL Server can only provide failover. Failover means that when one machine in the system fails and loses its function, the other machine will take over the SQL Server instance that runs it. The failure may be due to hardware faults, service faults, manual faults, or various other reasons.

Why cluster the SQL Server environment?

In terms of practicality, the cluster SQL Server environment is satisfactory. During failover, it usually takes 3 to 7 seconds to transfer a database instance from one server to another. Although the connection needs to be rebuilt, the Failover process is usually transparent for end users of the database. Low failover costs can also help you maintain nodes in the cluster, without causing completely inaccessible servers.

SQL Server Cluster type

There are two types of SQL Server Clusters: active/passive clusters and active/active clusters. They are described as follows (based on the SQL Server cluster of the two nodes ).

Active/passive clusters

In this type of cluster, only one node controls SQL Server resources at a time. The other node remains in standby mode, waiting for the fault to occur. When performing failover, the slave node obtains control of SQL Server resources.

Advantage: because only one instance is running on the Server, you do not need another Server to take over two SQL Server instances during failover, and the performance will not decrease.

Disadvantage: because only one SQL Server instance is running on the virtual Server, the other Server is always in standby mode and idle state. This means that you have not made full use of the hardware you purchased.

Active/active Cluster

In this type of cluster, each node in the cluster runs an independent and active SQL Server instance. When a node fails, the other node can control the SQL Server instance of the faulty node. The normal node then runs two SQL Server instances-its own instance and faulty instance.

Advantage: With this configuration, you can make full use of your hardware. In such a system, both servers are running, instead of only one server, and the other is in standby mode waiting for a fault to occur. Therefore, you can make full use of the machine you purchased.

Disadvantage: If a Failover occurs, when one Server runs two SQL Server instances, the performance will be adversely affected. However, the performance reduction is always better than the failure of the virtual server. Another failure of this configuration is that it requires more licenses than active/passive clusters. Because the cluster is running two active SQL Server instances, You need to purchase two separate Server licenses. In some cases, this may also impede you.

Cluster considerations

In terms of high practicability, the cluster SQL Server environment has some advantages. However, high practicability is indeed accompanied by some compromise.

First, creating a cluster SQL Server environment is very expensive. This is because the nodes in the cluster must comply with the compatibility list of the cluster nodes. In addition, you also need to build a complex network, and the machine configurations must be almost the same. At the same time, you need to share the database file disk subsystem. A storage zone network (SAN) is a good choice for building such subsystems, but SAN is not necessary and expensive. In addition, if you are running an active/active cluster, you need to buy a license for the processor of each machine running the SQL Server instance in the cluster.

Because the local cluster is mainly confined to the same geographic area, a natural disaster may make the cluster completely unavailable. In that case, you need to transfer to the disaster recovery site for further operations. You can also create geographically dispersed SQL Server clusters, but such systems are more complex and expensive.