3

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?

Improve this question
4
  • 1
    Just one question if you can answer: what was your primary objective for this solution ? HA [...to be online at all times...]? DR?
    – Asteway
    Dec 20, 2017 at 4:34
  • DR yes and HA - my development team wants to use a non .net web server for production, which cannot be configured with a 'failover partner', so we need the listener (for automatic client connection routing between primary & mirror if there is a failover). Also, we only have 2 SQL 2014 Ent licences and I do not want to use FCI because we do not use SAN. Hence 2 node cluster. I do have 1 SQL standard licence, which I will use for replication (a reporting server). I could use this server as a 3rd node for quorum voting. Going to test this out now...
    – Dina
    Dec 20, 2017 at 15:31
  • Using this blog for reference... testing out both 3rd node & File Share Witness..derekseaman.com/2014/10/…
    – Dina
    Dec 20, 2017 at 16:22
  • some of your questions can be answered here and the links here:learn.microsoft.com/en-us/windows-server/failover-clustering/…
    – shiwanginio
    Oct 2, 2019 at 5:43

1 Answer 1

Reset to default
2

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).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.