principleMinor
File share witness vs 3rd Node for WSFC + AG with only 2 nodes?
Viewed 0 times
nodesfile3rdwithnodewsfcwitnessforshareonly
Problem
I am migrating from Mirroring to AGs on SQL Server 2014 / Windows 2012 R2. I will have a 2 node cluster. I need this cluster to be online at all times for a production environment.
I am reading Microsoft documentation for Availability Groups but I cannot find anything along lines of "Best Practices" for having a 2 node AG/WSFC, except that not having a witness is NOT "Best Practice". I am simply exploding with questions.
If I implement a File Share Witness
What is the best place to put my (file share) witness? Is putting it on a DC a good idea or a security risk?
Why does it have to be Windows Server as opposed to (say) a linux/NAS?
How do I prevent someone from simply deleting the file share because they don't know what it is or what its for, while not locking myself/all sysadmin out of it?
If I don't Implement a File Share Witness
Could I just remove dynamic quorum and exclude witness altogether? Is that even possible?
Is it better to just add another SQL node to my cluster, if I have one, to act as a 3rd voting party but not include any of its databases? Is that perhaps more durable?
I am reading Microsoft documentation for Availability Groups but I cannot find anything along lines of "Best Practices" for having a 2 node AG/WSFC, except that not having a witness is NOT "Best Practice". I am simply exploding with questions.
If I implement a File Share Witness
What is the best place to put my (file share) witness? Is putting it on a DC a good idea or a security risk?
Why does it have to be Windows Server as opposed to (say) a linux/NAS?
How do I prevent someone from simply deleting the file share because they don't know what it is or what its for, while not locking myself/all sysadmin out of it?
If I don't Implement a File Share Witness
Could I just remove dynamic quorum and exclude witness altogether? Is that even possible?
Is it better to just add another SQL node to my cluster, if I have one, to act as a 3rd voting party but not include any of its databases? Is that perhaps more durable?
Solution
In a 2 node Availability Group (or mirror) a 3rd node is important if you want automatic HA. If your secondary cannot connect to the primary, the secondary has to work out if it can come online as the primary, or if the error is actually with the secondary. Without a 3rd party, this is impossible.
The File Share Witness will be simpler than adding another Windows node just to work as a witness. It can be anywhere, what is important is that both nodes will be able to see the witness if the other node goes offline. You might need to think extra hard if you have a virtual environment, you need to consider if the physical node goes offline will your fileshare still be available.
Additional thoughts for multi data centers
If the 2 nodes are in different data centers (you mentioned DR, so I'm assuming) then having a file share in the cloud may be helpful (you can use Azure Blob Storage as a witness). This means if one of your primary data center goes offline, SQL will automatically failover to the secondary. If you host the fileshare at your secondary and the secondary goes offline, then your primary server will go offline as well (can't contact the secondary or the file share so assumes that it is no longer primary).
The File Share Witness will be simpler than adding another Windows node just to work as a witness. It can be anywhere, what is important is that both nodes will be able to see the witness if the other node goes offline. You might need to think extra hard if you have a virtual environment, you need to consider if the physical node goes offline will your fileshare still be available.
Additional thoughts for multi data centers
If the 2 nodes are in different data centers (you mentioned DR, so I'm assuming) then having a file share in the cloud may be helpful (you can use Azure Blob Storage as a witness). This means if one of your primary data center goes offline, SQL will automatically failover to the secondary. If you host the fileshare at your secondary and the secondary goes offline, then your primary server will go offline as well (can't contact the secondary or the file share so assumes that it is no longer primary).
Context
StackExchange Database Administrators Q#193569, answer score: 2
Revisions (0)
No revisions yet.