AlWays on Availability group and Fileshare witness Quorum
What is AlWaysOn Availability Group
AlwaysOn Availability Groups is an enterprise-level high-availability and disaster recovery solution introduced in SQL Server 2012 to enable you to maximize availability for one or more user databases. AlwaysOn Availability Groups requires that the SQL Server instances reside on Windows Server Failover Clustering (WSFC) nodes.
What is a Quorum in Failover Cluster
Quorum is that it is a configuration database for the cluster and is stored on a shared location, accessible to all of the nodes in a cluster.
Different Type of Quorum
Node Majority (recommended for clusters with an odd number of nodes)
Can sustain failures of half the nodes (rounding up) minus one. For example, a seven node cluster can sustain three node failures.
Node and Disk Majority (recommended for clusters with an even number of nodes)
Can sustain failures of half the nodes (rounding up) if the disk witness remains online. For example, a six node cluster in which the disk witness is online could sustain three node failures.
Can sustain failures of half the nodes (rounding up) minus one if the disk witness goes offline or fails. For example, a six node cluster with a failed disk witness could sustain two (3-1=2) node failures.
Node and File Share Majority (for clusters with special configurations)
Works in a similar way to Node and Disk Majority, but instead of a disk witness, this cluster uses a file share witness.
Note that if you use Node and File Share Majority, at least one of the available cluster nodes must contain a current copy of the cluster configuration before you can start the cluster. Otherwise, you must force the starting of the cluster through a particular node. For more information, see “Additional considerations” in Start or Stop the Cluster Service on a Cluster Node.
No Majority: Disk Only (not recommended)
Can sustain failures of all nodes except one (if the disk is online). However, this configuration is not recommended because the disk might be a single point of failure.
Windows 2012:
It is continued to improve quorum On Windows 2012. In a majority node set, as soon as you lose a majority of your votes, the remaining nodes go offline. So for example, if you have a cluster with FIVE nodes, if you were to lose THREE nodes the remaining nodes will go offline, even though there are three nodes remaining. This might not be exactly what you want to happen. So in Windows Server 2012, Microsoft introduced Dynamic Quorum.
The quorum models are the same in Windows 2012 like Windows 2008 R2, however there are enhancements with Node Vote Assignment and there is a new concept called as “Dynamic Quorum Configuration”.
Dynamic Quorum Configuration:
Starting Windows Server 2012, there is a new concept called dynamic quorum configuration. This concept is based on node vote assignment, specifically it relies on the fact that cluster can manage node vote assignment automatically based on the state of each node. Votes are automatically removed from nodes that leave active cluster membership, and a vote is automatically assigned when a node rejoins the cluster. By default, dynamic quorum management is enabled.
In above scenario of five node cluster When node one went offline, we have four node cluster. When node two went offline, I would then have a THREE node cluster, and so on. In reality, It continues to lose cluster nodes one by one, all the way down to a two node cluster and still remain online. And, if I had configured a witness (Disk or File Share) I could actually go all the way down to a single node and still remain online.
However, best practice in Windows Server 2012 and earlier is to only configure a witness on clusters that have an even number of nodes. Here is an example of why you wouldn’t configure a witness in cluster with an odd number of nodes. Fore example you have a three node cluster and decide to configure a witness (Disk or File Share). If you happen to lose the Witness and a single node, the remaining two nodes will go offline because they only have two out of four votes, not a majority. So adding a witness to a cluster that already has an odd number of votes actually gives you less availability than if you didn’t add a witness at all.
That leads Windows Server 2012 R2 and Dynamic Witness. In the scenario above on Windows Server 2012, However, on Windows Server 2012 R2 has changed to ALWAYS configure a witness, regardless of the number of nodes. The cluster itself will determine whether the witness actually has a vote, depending upon how many cluster nodes are available at any given time. So in the example above with five node cluster, still configure a witness. Under normal circumstances when all FIVE nodes are running, witness would not have a vote. However, as a started losing cluster nodes, the witness vote would be turned on or off as needed to ensure that I always had an odd number of votes in my cluster.
How can I decide Which quorum is good and What is the recommended size of the File Share/Disk Witness Quorum ?
In Case of Even number of nodes (but not a multi-site cluster) Node and Disk Majority Qurum configuration is recommended . If you dont have no shared storage Node and File Share Majority is recommended. Requires One quorum disk (or File Share) per cluster.
It is recommended that you configure the quorum disk size to be 500 MB; this size is the minimum required for an efficient NTFS partition. Larger disk sizes are allowable but are not currently needed.
Reference
https://blogs.msdn.microsoft.com/microsoft_press/2014/04/28/from-the-mvps-understanding-the-windows-server-failover-cluster-quorum-in-windows-server-2012-r2/
What is AlWaysOn Availability Group
AlwaysOn Availability Groups is an enterprise-level high-availability and disaster recovery solution introduced in SQL Server 2012 to enable you to maximize availability for one or more user databases. AlwaysOn Availability Groups requires that the SQL Server instances reside on Windows Server Failover Clustering (WSFC) nodes.
What is a Quorum in Failover Cluster
Quorum is that it is a configuration database for the cluster and is stored on a shared location, accessible to all of the nodes in a cluster.
Different Type of Quorum
Node Majority (recommended for clusters with an odd number of nodes)
Can sustain failures of half the nodes (rounding up) minus one. For example, a seven node cluster can sustain three node failures.
Node and Disk Majority (recommended for clusters with an even number of nodes)
Can sustain failures of half the nodes (rounding up) if the disk witness remains online. For example, a six node cluster in which the disk witness is online could sustain three node failures.
Can sustain failures of half the nodes (rounding up) minus one if the disk witness goes offline or fails. For example, a six node cluster with a failed disk witness could sustain two (3-1=2) node failures.
Node and File Share Majority (for clusters with special configurations)
Works in a similar way to Node and Disk Majority, but instead of a disk witness, this cluster uses a file share witness.
Note that if you use Node and File Share Majority, at least one of the available cluster nodes must contain a current copy of the cluster configuration before you can start the cluster. Otherwise, you must force the starting of the cluster through a particular node. For more information, see “Additional considerations” in Start or Stop the Cluster Service on a Cluster Node.
No Majority: Disk Only (not recommended)
Can sustain failures of all nodes except one (if the disk is online). However, this configuration is not recommended because the disk might be a single point of failure.
Windows 2012:
It is continued to improve quorum On Windows 2012. In a majority node set, as soon as you lose a majority of your votes, the remaining nodes go offline. So for example, if you have a cluster with FIVE nodes, if you were to lose THREE nodes the remaining nodes will go offline, even though there are three nodes remaining. This might not be exactly what you want to happen. So in Windows Server 2012, Microsoft introduced Dynamic Quorum.
The quorum models are the same in Windows 2012 like Windows 2008 R2, however there are enhancements with Node Vote Assignment and there is a new concept called as “Dynamic Quorum Configuration”.
Dynamic Quorum Configuration:
Starting Windows Server 2012, there is a new concept called dynamic quorum configuration. This concept is based on node vote assignment, specifically it relies on the fact that cluster can manage node vote assignment automatically based on the state of each node. Votes are automatically removed from nodes that leave active cluster membership, and a vote is automatically assigned when a node rejoins the cluster. By default, dynamic quorum management is enabled.
In above scenario of five node cluster When node one went offline, we have four node cluster. When node two went offline, I would then have a THREE node cluster, and so on. In reality, It continues to lose cluster nodes one by one, all the way down to a two node cluster and still remain online. And, if I had configured a witness (Disk or File Share) I could actually go all the way down to a single node and still remain online.
However, best practice in Windows Server 2012 and earlier is to only configure a witness on clusters that have an even number of nodes. Here is an example of why you wouldn’t configure a witness in cluster with an odd number of nodes. Fore example you have a three node cluster and decide to configure a witness (Disk or File Share). If you happen to lose the Witness and a single node, the remaining two nodes will go offline because they only have two out of four votes, not a majority. So adding a witness to a cluster that already has an odd number of votes actually gives you less availability than if you didn’t add a witness at all.
That leads Windows Server 2012 R2 and Dynamic Witness. In the scenario above on Windows Server 2012, However, on Windows Server 2012 R2 has changed to ALWAYS configure a witness, regardless of the number of nodes. The cluster itself will determine whether the witness actually has a vote, depending upon how many cluster nodes are available at any given time. So in the example above with five node cluster, still configure a witness. Under normal circumstances when all FIVE nodes are running, witness would not have a vote. However, as a started losing cluster nodes, the witness vote would be turned on or off as needed to ensure that I always had an odd number of votes in my cluster.
How can I decide Which quorum is good and What is the recommended size of the File Share/Disk Witness Quorum ?
In Case of Even number of nodes (but not a multi-site cluster) Node and Disk Majority Qurum configuration is recommended . If you dont have no shared storage Node and File Share Majority is recommended. Requires One quorum disk (or File Share) per cluster.
It is recommended that you configure the quorum disk size to be 500 MB; this size is the minimum required for an efficient NTFS partition. Larger disk sizes are allowable but are not currently needed.
Reference
https://blogs.msdn.microsoft.com/microsoft_press/2014/04/28/from-the-mvps-understanding-the-windows-server-failover-cluster-quorum-in-windows-server-2012-r2/
Microsoft SQL Server 2019 Standard provides provides additional capability and improvements database features. like SQL Server database engine, SQL Server Analysis Services, SQL Server Machine Learning Services, SQL Server on Linux, and SQL Server Master Data Services.
ReplyDelete