SQL Server AlwaysOn - Very Confused About Witness. Necessary or not?

Question

_{Tuesday, September 29, 2015 3:29 AM}

So I am trying to set up a two-replica SQL Server 2012 AlwaysOn Availability Group running on Windows Server 2012 R2 on Azure ARM. This is the first time that I am doing this, and I am reading through available documentation and asking questions here (example: https://social.msdn.microsoft.com/Forums/azure/en-US/e5b3fa8f-546c-4c41-b9dc-197fe4809558/sql-server-alwayson-requirements?forum=WAVirtualMachinesforWindows#e5b3fa8f-546c-4c41-b9dc-197fe4809558)

I am very confused about the 3rd VM to configure the AlwaysOn Availability Group. Specifically, it seems to me that there is a discrepancy over whether a Witness role is necessary, or whether a Windows Server Failover Clustering (WSFC) cluster node is necessary.

This website says that "in contract to database mirroring, there is no witness role in AlwaysOn Availability Groups".

https://msdn.microsoft.com/en-us/library/hh510230.aspx

However, this other website talks specifically about a "quorum file share witness".

https://msdn.microsoft.com/en-us/library/azure/jj870963.aspx

Essentially, I am very confused about the role of this "witness". Where can I get better clarification on this?

All replies (3)

_{Tuesday, September 29, 2015 9:20 AM ✅Answered | 3 votes}

Hi,

The 'Quorum file share witness' is a quorum model used with Windows Failover Clustering. You can use instead a 'witness disk', but on the Azure case, using the disk model is not straightforward, this is why we use File Share witness (You don't have to create a third node, you can use any File Share, even in an existing VM)

Your question is about confusion : In some documents we state a No, in other we state a Yes.

This is because they talk about different things. There is a 'Witness' concept when creating SQL mirroring. In the DAG configuration (SQL server AlwaysON DAG), even if in the shadow, it uses mirroring, you will not use such witness.

Why?

Because DAG relies on  Windows Clustering. And the WSFC needs the commanded quorum model to achieve right decisions. For an even nodes number (2, 4, 6...), you need a Witness. This is why you have to use a 'File Share witness'. This is needed by the Failover Cluster and not by SQL

Regards, Samir Farhat || Datacenter Consultant || The way to share my knowledge with the community Visit my blog : buildwindows.wordpress.com

_{Tuesday, September 29, 2015 2:33 PM ✅Answered | 2 votes}

Because SQL Server Availability Groups run on top of Windows Server Failover Cluster(WSFC), you would need to properly configure quorum for the WSFC. This could mean creating an additional VM (a 3rd VM if you are running a 2-replica Availability Group), a file share witness or a disk witness. Configuring a disk witness in Azure is not straight forward and creating a 3rd VM will add up to the operational cost. A recommended approach is to use a file share witness. However, the file share witness needs to be running on a machine that is not a node in the WSFC.

For more details on the concept of a quorum and the different types of witness, refer to the article below. While it says Windows Server 2008, the concepts apply to Windows Server 2012 and higher

Understanding Quorum Configurations in a Failover Cluster

Edwin Sarmiento SQL Server MVP | Microsoft Certified Master/Solutions Master
Blog | Twitter | LinkedIn
Learn SQL Server High Availability and Disaster Recovery

_{Wednesday, September 30, 2015 3:26 AM}

Thank you both Edwin and Samir. This helps a lot.