David Bermingham looks at ways to ensure that business critical information held within SQL Server deployments can be protected via cluster quorums, including looking at the role of various ‘witness’ options.
If you're deploying SQL Server, you are likely running business critical applications on it. You need to ensure it’s protected from downtime and data loss. You typically have one of two options for high availability protection: Always On Availability Groups (AOAG), which was introduced with SQL Server 2012 Enterprise Edition, or Always On Failover Cluster Instances (FCI), which has been long been a proven high availability solution that comes with SQL Server Standard and Enterprise Editions. In both of those solutions, the underlying infrastructure for availability relies on Windows Server failover clustering (WSFC). They both involve running SQL Server on a primary node and setting up one or more secondary nodes also running SQL Server. AOAG has built in replication to keep the data in sync between the nodes. FCI requires a shared storage device, or alternatively a software-based replication solution.
In the event of a failure on the primary node, the clustering software moves operations to one of the secondary nodes, where it can continue to operate with a minimum of downtime or data loss. Both Microsoft AOAG and FCI clustering options rely on a quorum to decide which node is going to come online in the case of a failover. The quorum prevents ‘split brain’ where two nodes think they should be active, which could cause data loss if not prevented.
A closer look at cluster quorums
In a cluster, each node gets one vote indicating it’s online. In the SQL Server cluster shown in figure one, each of the three nodes gets one vote. If you have a failure – suppose the primary node goes offline – then nodes two and three have two votes out of three, forming a majority node set. The quorum will then move SQL Server to either node two or three.
Figure one: Three node cluster.
But, what happens, if you have a two-node cluster like the one in figure two with only two votes? If the primary node (one) fails, then node two remains online with one vote. One out of two is a tie, so node two will not failover, the cluster will be offline, and your SQL Server is no longer in service. You need a witness node to break the tie. There are three types of witness nodes in a cluster: disk witness, file share witness and cloud witness.
Figure two: two node cluster.
The disk witness
In a shared storage cluster, you can use a small partition on the SAN disk as a disk witness to act as a vote as in figure three. If node one fails, node two and the SAN disk witness form a two-out-of-three majority, SQL Server will failover and operation will continue. Interestingly, a disk witness is not a single point of failure. If the witness fails, you still have two votes (nodes one and two) that represent a two out-of-three majority. It doesn’t matter if you lose the witness whether it's disk, file share or cloud witness because you still have a two out of three votes. Because nodes one and two are still healthy, SQL will keep working on node one without any failure or failover.
Figure three: Majority node set with disk witness.
The file share witness
Microsoft introduced the concept of a file share witness for systems that don’t use shared storage. The original use case was for Exchange Servers, but since then it has also been used for AOAG and SQL Server FCI that use SIOS DataKeeper to create SANless clusters. You can designate any other Windows server in your domain to act as a vote. You simply make a file share, and give the cluster read-write access to it, creating a simple file share that acts as a vote in your cluster. This is how to implement high availability for all availability groups or for SANless clusters. And like a disk witness, you can lose a file share witness, and the cluster will keep working.
Cloud witness
The Cloud Witness was introduced with Windows Server 2016. You can leverage the Microsoft Azure blob storage to act as a witness. You simply provision a storage account, and using the Failover Cluster Manager, configure a cloud witness which points to this storage account. Note that these two nodes need to access the public cloud to leverage a cloud witness. This option that gives you high availability without requiring a third server to act as a witness.
Easy, cost-effective disaster recovery
Cloud witnesses (figure four) enable you to configure multi-site clusters without requiring a third site to hold a disk witness as previously required. For example, suppose node one is in Chicago and node two in Dallas, previously you would need to configure a third site for your file share witness to ensure that the failure of a single site would only impact one of your quorum votes. To ensure automatic failover without a cloud witness, the file share witness had to be in a separate (potentially very costly) location for site-wide disaster recovery.
Figure four: cloud witness.
Cloud witnesses are also important in small offices or remote locations where you only have two physical servers. For example, if you have two servers running Hyper V and two virtual machines running on each server, and you want redundancy, but can’t afford a third server or a SAN. Instead, you can build a two-node cluster and use an Azure cloud witness for your cluster. This allows you to build a very cost effective two node cluster for your remote office or branch office.
Dynamic quorum
What if you have a four-node cluster, and extra servers for more redundancy? If node one fails, SQL will move to node two. But what if node two fails as well? Now, even though you have two nodes still operational, two out of four is not a majority, and so SQL goes offline. Recognizing this problem, Microsoft introduced the dynamic quorum in Windows Server 2012. Dynamic quorums adjust the number of votes in your cluster as nodes start to fall out.
If you have two nodes in your cluster, Microsoft adjusts the quorum. It always creates an even number of votes. With just two nodes remaining, it will change the quorum count to one, and remove the vote from one of these nodes. You could potentially lose all three nodes, and as long as there is a witness available, the last node will remain online.
Dynamic witness
The dynamic witness adds or removes votes to the file share witness to ensure the proper number of votes is maintained for failover protection. In a four-node configuration, if you lose node one, node two will come online with SQL Server, but without the dynamic witness, the file share witness gives you four votes in the cluster. Now suppose they all sit in the same rack, and that rack had a failure causing you to lose two of them at the same time. You would lose two out of four votes. Even though node two is fully operational and can talk to the file share witness, it would go offline, and SQL Server operation would stop.
If this happens with a dynamic witness, SQL would continue to operate. As you lose node one, Microsoft recognizes that nodes two, three, four, and the witness represent four votes, which is an even number. It will remove the file share witness vote, so the cluster has three votes. Now you can lose this server and it will still failover. After you lose node one, Microsoft sees that there are only two nodes left so it gives a vote back to the file share witness. Now, you could lose node three, and node four will come online because nodes four and the file share witness represents two votes, for a two-out-of-three majority. The dynamic witness will actually control whether any witness - cloud, file share, or disk witness - has a vote in the cluster, so it gives you a much higher level of availability than just dynamic quorum alone.
Prior to 2012, you only configure a witness if doing so gave you an odd number of votes. In a two-node cluster, you configure a witness; in a three-node cluster, you don't, because it would make four votes. As of 2012 R2 or later, you always configure witness and the dynamic quorum ensures you have the proper number of votes.
Quorums in the cloud
Figure five is a typical 100 percent cloud configuration. Each cloud provider has very similar concepts of geographic regions with availability zones (AZs) (sometimes called fault domains) within each region. Within a geographic region, AZs or fault domains, are completely independent of one another. A hardware failure in AZ one should not affect AZ two at all. In many cases, these AZs are in different buildings attached to different power grids and cooling systems. They are provided with as much redundancy and as few single points of failures as possible.
Figure five: cloud configuration.
When you build a cluster to protect your SQL Server implementation, whether you use AOAGs or FCI in a SANless cluster with SIOS DataKeeper, begin by putting each node of your cluster in a different AZ. In the cloud you're limited to using either a file share witness or the Azure cloud witness. If you're using a file share witness, make sure it resides in its own AZ. Doing so gives you maximum availability by ensuring that the failure of an entire zone would only affect one vote of the cluster at a time.
Azure cloud witness
If you are using the cloud witness, you don't have to worry about that, because the cloud witness is its own entity, and the failure of a single AZ won’t affect it. Note that the Azure cloud witness is not limited to Azure deployments. Even if AWS is your platform, you can still us Azure cloud witness as part of your cloud quorum and it will cost you next to nothing.
The author
David Bermingham is recognized within the technology community as a high availability expert and has been elected by his peers to be a Microsoft Cloud and Datacenter Management MVP. David’s work as director, Technical Evangelist at SIOS Technology Corp., has him focused on evangelizing Microsoft high availability and disaster recovery solutions as well as providing hands on support, training and professional services for cluster implementations. David hold numerous technical certifications and draws from more than twenty years of experience IT, including work in the finance, healthcare and education fields, to help organizations design solutions to meet their high availability and disaster recovery needs. David has recently begun speaking on deploying highly available SQL Servers in the Azure Cloud and deploying Azure Hybrid Cloud for disaster recovery. Learn more at www.us.sios.com or ClusteringforMereMortals.com. Email David.Bermingham@us.sios.com
SIOS is the provider of the SIOS DataKeeper software based replication solution.
