MS SQL FAILOVER CLUSTER (Complete reference)

MS SQL FAILOVER CLUSTER (Complete reference)

Before getting hands on experience in SQL server on failover cluster , I used to read many article about failover clustering. Unfortunately I was not able to digest the concept till I wet my hands.I will keep this point in my mind while explaining the failover cluster in this post.

What is a windows Cluster ? A cluster is a group of independent computer systems, referred to as nodes, working together as a unified computing resource. When we say unified computing , it does not mean that a single application can access the resources (cpu/memory) of multiple computer at any point of time. A cluster provides a single name for clients to use it services.There are two type of cluster:

Network Load Balancing Cluster : A Network Load Balancing Cluster (NLB) distribute the load (request from the client)  to the different nodes which are part of the cluster based on predefined rules.The  client application has to communicate to a single cluster IP address (virtual server)  and client does not know which node in the cluster exactly serving its request.Network Load Balancing cluster helps to enhance the the availability and scalability of  application. We are not going to talk much about this on this post.

Fail over Cluster:  Failover cluster is a collection of servers that by working together increase the availability of applications and services that run on the cluster. It can be  described as a technology that automatically allows one physical server to take over the tasks and responsibilities of another physical server that has failed.A failover cluster provides high availability for cluster aware application like SQL server ,exchange server,etc. The major difference between NLB cluster and failover cluster is, failover cluster will not help to improve the scalability of the application. It can only support high availability.

There are many misunderstanding among people about the advantage of having SQL server on a failover cluster environment. Below table will give a clear picture about the capability of SQL server on cluster environment.


To understand the failover cluster it is important to familiar with the terminologies. Let us have look on the terminologies used in the windows clustering.

Server Node: Physical node with operating system that support windows clustering. Each server node should have minimum two network card for public and private network.Minimum one local hard disk is required for OS and other application binaries.

Private Storage: Local disks are referred as private storage. There will be minimum of one private disk for OS and SQL binaries.Server node can have a additional local disk for bigger page file.
Shared Disk Array: Each server needs to be attached to the shared external storage.In non-clustered SQL server instance, databases (system/user/resource) are store on locally attached disk storage but in clustered SQL server instances databases are store data on a shared disk array.That mean all the server nodes in the cluster setup are physically connected to the disk array.This shared storage configuration allow application to failover between server in the cluster.

Quorum Disk: Quorum is the cluster's configuration file.This file (quorum.log) resides in the the quorum disk (one disk from shared disk array).Quorum is the main interpreter between all nodes. It stores latest cluster configuration and resource data. This helps the other nodes to take ownership when one node goes down.
In real life, it is like emergency contact number and other medical information in your wallet which help others to take action in case of emergency.

Public Network and Private Network: Each server needs at least two network cards. Typically, one is the public network and the other is a private network between the nodes. The private network is used for internal communication of cluster.This is referred as heart beat. Public network is used to connect the external world or intranet.

Heart Beat: Heart beat is health check mechanism in cluster A single UDP packet sent between nodes in the cluster via the private network to confirm that  nodes are still online. By default cluster service will wait five seconds(one heart beat sent in every second)  before considering a cluster node to be unreachable.

Looks Alive check:Looks alive check is a basic resource health check to verify that the service(SQL service in our context) is running properly.To perform this , cluster service queries the windows service control manager to check the status of the service.By default looks alive check will happen in every five seconds.

Is Alive check: An exhaustive check to verify that a resource is running properly. If this check fails, the resource is moved offline and the failover process is triggered. During the Is alive check  the cluster service connects to the SQL server instance and execute select @@SERVERNAME.It will check only the SQL server instance availability and does not check the availability of user databases.

Failover: Clusters service monitor the health of the nodes in the cluster setup and the resources in the cluster. In the event of a server/resource failure, the cluster service  re-starts the failed server's workload on one of the remaining servers based on possible owners settings. The process of detecting failures and restarting the application on another server in the cluster is known as failover .


Virtual Server:Virtual server consist of a network name and IP address to which the client connect.This helps the client to connect the service  which hosted in the cluster environment with out knowing the details of the server node on which the service is running.In simple word , virtual server helps the client application to connect the SQL server  instance with out changing the connection string in case of failover of SQL instance.

Possible Owners :Possible owners are the server nodes on which cluster group (in our context SQL instance) can failover

Preferred Owner: Preferred owner is the server node which best suited to running an application or group.

Cluster Setup: Below schema diagram will give a clear picture about the windows cluster setup.

This picture depicts  two  node cluster setup.At this stage Node A and Node B will have only OS and windows cluster service.One of the disk from cluster disk will be designated as the quorum disk. This setup basically done by windows administrators and storage specialists.On top of this,  DBA's install  and configure the SQL instances. We are not going talk about the SQL server installation on cluster environment on this post.

To make it more clear, let us describe our cluster environment as given below.

Let us look into the pictorial representation current setup.

Fig 1

From the Fig-1, we can see that INST1 and INST2 are installed on both physical node , but at any point of time one instance will be online only in one node.At this stage INST1 is running on physical node Pnode-A and INST2 is running on node Pnode-B.The cluster disk D and E will be visible in the node Pnode-A and F and G will be visible in the node Pnode-B.The request from the App1 will be routed through the virtual server A and landed in Pnode-A where the INST1 is running.In the same way the request from the App2 will be routed through the virtual Server B and landed in Pnode-B where the INST2 is running.

Let us see what will happen if there is a hardware/network failure in Pnode-A. Fig 2 depicts the failover of  scenario of INST1 to Pnode-B.

Fig 2

Now the INST1 went offline in Pnode-A and came online Pnode-B.The connection from the App1 will be routed through the same virtual Server A and landed in Pnode-B.All four cluster disk will be visible in Pnode-B and only C (Local disk) will be accessible from Pnode-A. Note that even after the failover of INST1,App1 will be connecting to the virtual server A.It helps to make the failover transparent to the application.

Windows Cluster : Understanding the Quorum settings

In my earlier post , I have explained about the windows cluster and how Sql server works on cluster environment. In this post let us try to understand the quorum settings of windows cluster environment. When I say quorum, do not interpret as quorum disk. Quorum has literal meaning in the cluster environment. In this post I will use the word witness disk to refer the quorum disk.Let us see what are all the possible quorum settings and how it will affect the windows cluster.

What is a quorum ?
As per Wikipedia, quorum is the minimum number of members of a deliberative assembly necessary to conduct the business of that group. In short quorum is minimum number of votes required for majority.As I explained in my earlier post, the nodes participating in the windows cluster are connected through a private network and communicate through User Datagram Protocol (UDP) port 3343.The quorum configuration in a failover cluster determines the number of failures (failure of nodes) that the cluster can sustain while still remain online. If additional failure happened beyond this threshold, the cluster will stop running.Quorum is designed to handle the Split Brain scenario. When nodes are unable to communicate each other, each node assume that, resource groups owned by other nodes have to brought online. When same resource brought online on multiple nodes at the same time,data corruption can occur. This scenario is called Split Brain.

Let us assume that we have four node cluster and one instance of sql server is running on each node. Node1 and Node2 lost the communication with Node3 and Node4. Node1 and Node2 can communicate each other and  Node3 and Node4 can communicate each other. In this scenario each group does not know what happened to other  two nodes. Are they offline or just a communication failure ?. In this scenario, Node1 and Node2 try to bring online the Sql instance(resource) owned by Node3 and Node4. In the same way Node3 and Node4 will try to bring online the Sql instance (resource) owned by the Node1 and Node2, which will lead to disk corruption and many other issues.The windows cluster quorum setting is designed to prevent this kind of scenario.By having the concept of quorum, the cluster will force the cluster service to stop in one of the subsets of nodes to ensure that there is only one true owner for the particular resource group.

Voting
Having quorum (majority) is based on the voting algorithm where more than half of the voters must be online and able to communicate each other. The cluster knows how many node are used to form the the cluster and will know how many votes constitutes a quorum. If the number of votes drop below the majority, the cluster service will stop on the nodes of that group.Cluster requires more than half of the total votes to achieve the quorum.This is to avoid the tie in the number of votes. In a 8 node cluster , 5 voters must be online and able to communicate each other to have quorum. Because of this logic, it is recommended to always have an odd number of total voters in the cluster and the quorum setting define the the voters in a cluster.This does not necessarily mean an odd number of nodes is needed to form the cluster since both a witness disk (quorum disk) and a file share can contribute a vote, depending on the quorum settings.

Quorum Settings
Windows 2008 cluster supports four quorum models.



1 Node Majority 

2 Node and Disk Majority 

3 Node and File Share Majority

4 No Majority (disk only)



Node Majority: Node majority option is recommended for cluster with odd number of nodes.This configuration can handle a loss of half of the number of cluster nodes rounded off downwards. For example , a five node cluster can handle failure of two nodes. In this scenario three of the nodes (N1,N2,N3) can communicate each other but other two(N4 and N5) are not able to communicate. The group constituted by three node have the quorum (majority) and cluster will remain active and cluster service will be stopped on the other two nodes (N4 and N5). The resource group (sql server instance) hosted on that two nodes goes offline and come online on one of the three nodes based on possible owner settings.

Node and Disk Majority: This option is recommended for cluster with even number of nodes.In this configuration every node gets one vote and witness disk (quorum disk) gets one vote which makes total votes a odd number. The witness disk is a small ( approx 1 GB ) clustered disk.This disk is highly available and can failover between nodes. It is considered as part of the cluster core resource group.In a four node cluster, if there is a partition between two subsets of nodes, one of the subset will have witness disk and that subset will have quorum and cluster will remain online. This means that the cluster can lose any two voters,whether they are two nodes or one node and the witness disk.


Node and File Share Majority: This configuration is similar to the the Node and Disk Majority, but in this case the witness disk is replaced with a file share which is also known as File Share Witness Resource (FSW). This quorum configuration usually used in multi-site clusters (nodes are in different physical location) or where there is no common storage. The File Share Witness resource is a file share in any server in the same active directory which all the cluster nodes have access to. One of the node in the cluster will place a lock on the the file share to consider that node as owner of the file share.When this node goes offline or lost the connectivity another node grabs the lock and own the file share.On a standalone sever, the file share is not highly available , however the file share can also put on a clustered file share on an independent cluster,making the FSW clustered and giving it the ability to fail over between node. It is important that, this file share should not put in a node of the same cluster, because losing that node would cause for loosing two votes. A FSW does not store cluster configuration data like witness disk. It contain information about which version of the cluster configuration database is most recent.

No Majority (Disk only) : This configuration was available in windows server 2003 and has been maintained for compatibility reason and it is highly recommended not to use this configuration. In this configuration,only witness disk has a vote and there are no other voters in the cluster. That means if all nodes are online and able to communicate , but when witness disk failed or corrupted, the entire cluster will go offline.This is considered as single point of failure.



Hope you got a fair idea about various quorum settings available in windows 2008 cluster.

Compare features in different High Availability options in SQL Server.

Since Sql Server 2005, High Availability (HA) has become very easy to implement in a Sql Server environment. Also, starting Sql 2005, there are 4 methods to implement HA.

• Failover Clustering
• Database Mirroring
• Replication
• Log Shipping

Though, Failover clustering still is the most popular option, my guess is the other options might make it a more level field going forward.

In the following matrix, I compare some features of each HA option in one table. This gives a good comparison picture of the features available in each option.

Property \ Feature	FailoverClustering	DB Mirroring	Replication	Log Shipping
Server Level	Yes	No	No	No
Database Level	No	Yes	Yes	Yes
Automatic Failover	Yes	Yes (Only HA Option)	No	No
Automatic Failure Detection	Yes	Yes (Only in HA option)	No	No
Hot, Warm or Cold standby	Hot	Hot and Warm Options	Warm	Warm
Server Level Objects Failover	Yes (Automatic)	Manual	Manual	Manual
Is data on Failover available for queries while the Primary server is running?	No	Yes (use Database Snapshots)	Yes	Yes (Standby Mode)
Are changes allowed at Failover location?	No	No	Yes (Merge and TRN Replication with updating subscribers)	No
Is Main and Failover server in a 1-1 relationship?	Yes	Yes	Not Required	Not Required
Is 1-Many relationship possible between the Main and Failover servers?	No	No	Yes	Yes
Is Many-1 relationship possible between Main and Failover server?	No	No	Yes	No
Is Automatic Application redirection possible after failover?	Yes	Yes	No	No
Recovery model required for the participating databases?	Any	FULL	ANY	FULL & BULK
Can you filter what data is sent to the Failover server?	No	No	Yes	No
Where does the logic HA reside?	OS + Sql	Sql	Replication Agents (they run outside Sql Server	Sql Agents (Run outside Sql Server)
If you want a reports server, what is the best solution?	Not Possible	Works, but needs constant refresh of the Snapshot database for latest data.	Best Option.	Works, but users connections need to be kicked out, when new log is applied.
Best solution if the Main and Failover server are separated by long distance?	Not good.	Works, but not recommended.	Works, but with high latency	Best Option. (Log compression reduces latency significantly.