1.4 Design Quorum for Failover Clusters (23m)
Before you touch anything with Always On in the name, you need to understand how cluster quorum works and how it can bring your SQL Server down. Learn how dynamic quorum and dynamic witness should be configured and see 3 real-world scenarios.
Note: this class is from 2017, and I no longer update it. I’ve made it freely available since I no longer do production DBA work, and I don’t intend to keep this course up to date. You’re welcome to leave comments for other viewers here, but I don’t answer questions on this material, nor do I update it to reflect changes in Microsoft SQL Server. Thanks for understanding, and enjoy the free videos! Hope they help.
- 1.1 Intro and DBA Skills Quiz (33m)
- 1.2 Building and Testing Reliable SQL Servers Part 1 (25m)
- 1.2 Building and Testing Reliable SQL Servers Part 2 (21m)
- 1.3 Architecture for HA and DR Part 1
- 1.3 Architecture for HA and DR Part 2
- 1.5 Triaging Failure in Availability Groups (32m)
- 1.6 Recovering from Failover Part 1 (40m)
- 1.6 Recovering from Failover Part 2 (12m)
- 1.7 Building an Inventory and a Support Matrix (39m)
- 2.1 Database Mirroring Field Medic Guide (40m)
- 2.2 Transaction Log Shipping Tips and Tricks (45m)
- 2.3 Troubleshooting Backup and Restore Problems (35m)
- 2.4 Optimizing DBCC CHECKDB (53m)
- 2.5 Availability Group Backup and CHECKDB Part 1 (8m)
- 2.5 Availability Group Backup and CHECKDB Part 2 (32m)
- 2.6 Cloud for the Senior DBA (38m)
- 2.7 Homework: Deciding Between Availability Solutions Part 1 (11m)
- 2.7 Homework: Deciding Between Availability Solutions Part 2 (27m)
- 3.1 Shared Storage Part 1 (28m)
- 3.1 Shared Storage Part 2 (32m)
- 3.2 Advanced SAN Features – Storage Tiering and Snapshots (31m)
- 3.3 Virtualization Management and Troubleshooting (61m)
- 3.4 Server Hardware Sizing (36m)
- 3.5 Homework Part 1 (11m)
- 3.5 Homework Part 2: Answers (28m)
- 3.6 Index Maintenance for Enterprise Environments (43m)
- 3.7 Recap and Q&A (28m)
17 Comments. Leave new
Great info. Just need to know how would i go about configuring below:
Current set up= 2 Node cluster [DataCenter1] [DB Principal]+ 2 Node cluster [Datacenter2] [DB mirror]: + @ node cluster [LS set up for reporting] ::Mirroring is set up as DR due to Quarter fail over events or you can say DR test+ All above are Windows 2012 standard edition ,Shared storage and disk quorum
Keeping the same above set up with 2node cluster in Dc1 and 2 Node in other Data center would setting up Always ON be complicated and bigger challenge? Management wants to keep the same looks and replacing mirroring with always on due to many benefits available specially reporting ? Any suggestions , appreciate your advise, thanks!
Kapil – wow, lots of information in there. Unfortunately, architecture design like that is pretty far beyond what I can do fast in a comment. That’s where our consulting comes in. If you’d like to hire us for a consulting engagement, click Consulting at the top of the screen. Thanks!
Hi Brent, I have a Always On AG 2016, configured on Windows Server 2012 R2 – 2 node cluster. Quorum configured as 2 nodes + Fileshare witness, totally 3 votes.
Scenario 1:
Now, either 1 node fails – remaining vote = 66.66 (majority) and cluster is up.
Scenario 2:
Now, fileshare witness fails – remaining = 66.66 (majority) and cluster is up
Kindly help me understand the dynamic quorum and dynamic witness on scenario 2.
My doubts, post scenario 2:
Will it automatically take away 1 vote from either one of the nodes? If yes, remaining vote will be 33% not majority and single point of failure.
What will be the role of dynamic witness in this scenario?
Thanks in advance!.
I’m not sure if you’re saying scenario 1 & 2 are related. If you lose both the node AND the file share witness, you’re down to just 1 node – in that case, you’re screwed regardless of what happens. Single point of failure is not good.
Can you rephrase your question, maybe?
Question is:
What is the role of dynamic quorum and dynamic witness in 2 node cluster?
However, I understood from the SE question, post scenario 2 (ie, FS is down), we have only 2 nodes and Dynamic quorum will zero down either one of the node and assign complete vote (100%) to other node. So now, quorum will be 1/1, ie 100% majority.
And there is no role of dynamic witness in this case. When we even even number nodes (example 4 nodes) with FS witness, dynamic witness will add one more vote to FS, make it odd and majority. Also, when we have even odd number of nodes with FS witness, dynamic witness will zero down the vote for FS and keep it as odd and majority quorum.
Kindly, correct me, if I am missing something. Thanks!
Correct, yep, you understand correctly, and we cover that exact scenario in the video. Thanks!
Thank you, Brent! Have a great day!
Another great video! Curious why you mentioned that you do not want to use a cloud witness for SQL?
When you’re doing failover clusters, adding a remote resource over the Internet doesn’t usually make your cluster more reliable (as opposed to a nearby resource).
Hi Brent ,
In Oracle we have Voting Disk configured in Odd Number’s to avoid split brain – in the similar manner can we configure Multiple redundant copies of Quorum in Windows accessible to each node ?
Regards,
Prashant
No.
Hi Brent! Thank you very much for this beautiful video.
I created a cluster with two nodes (node1 and node2).
The operating system on each machine is windows server 2016 data center edition.
There is no shared storage.
Both nodes use local storage.
I did not configure quorum witness.
There are two nodes and one voter (The current vote for node2 is zero).
The cluster is up and running when node1 is on and node2 is off (That means the computer is turned off).
Also, the cluster is up and running when node1 is off and node2 is on.
Kind regards and stay healthy.
Hi Brent!
Is the following sentence correct ?
It is most appropriate to not configure a witness, where there are an odd number of
nodes in a single data center.
No, you’ll always want to configure a witness. Windows dynamic quorum will automatically add or remove a vote from the witness based on the availability of the other nodes.
Thank you Brent!
Hi Brent!
What is cluster ownership?
What is the difference between cluster ownership and other nodes in the cluster?
Thank you for answering my questions!
For general Q&A, you’re best off hitting the product documentation, or posting questions at a general Q&A site like https://dba.stackexchange.com or https://SQLServerCentral.com.