7

So I have a SQL Always-On group that requires a file share witness. I want that file share witness to be redundant and since I have no other need for a file server on this network, I'd like to do it with the least number of servers.

I thought I'd set up 2 servers with DFS but this article says not to do that because DFS could sometimes use one server's data and sometimes use another, messing up the quorum: http://windowsitpro.com/high-availability/q-why-cant-i-host-file-share-witness-cluster-dfs-share

So it seems I need a true/real Windows Failover Cluster, set up in the File Server role. The problem is that all the articles I read talk about using shared storage. But shared storage (e.g. a SAN) would require a 3rd server and then again I have a single point of failure (the SAN). And, I'd really prefer to buy just 2 new servers instead of 3. I see I can also use Windows' Storage Spaces as an alternative to a SAN but that requires 3 discs so that's even worse insofar as buying hardware.

What's the best way to set up a redundant file share for a witness without buying too many servers or having a single SAN point failure? Obviously, I'd like to use local storage but can I set up the file cluster so that it uses server 1's hard drive all the time when server 1 is the primary and server 2's hard drive all the time when server 2 is the primary, and use DFS to replicate data in case one of the servers died? I think this way would avoid the "only DFS" concern mentioned in the above article and still keep me at just 2 servers.

stackonfire
  • 221
  • 1
  • 3
  • 6
  • 1
    `because DFS could sometimes use one server's data and sometimes use another` it's possible to set target priority for a given folder, such that a particular server will always get used if it's online. Either way, you'd need to setup DFS-R between nodes to keep the shares contents in sync. – GregL Jan 16 '16 at 00:23

3 Answers3

7

Based on the shared storage requirement I assume it's AlwaysON FCI (Failover Cluster Instances) The easiest solution for you would be deploying a virtual SAN. Virtual SAN will take the local storage of the 2 SQL nodes you have and present it back to them as a highly available virtual disk. Now if one of the SQL cluster nodes fail you still have one live copy of your data and graceful SQL failover. Although most of the Virtual SAN products are commercial only, it is also possible to get free ones with different level of restrictions:

  1. Datacore vitrual SAN - Emulates FC storage only, free only for non-production use. Need to submit a form and answer some questions to get it.
  2. Free HP VSA - emulates iSCSI, has 1 TB limitation (shouldn't be an issue with SQL), but requires at least 3 servers. Not sure if production use is allowed.
  3. StarWind Virtual SAN Free - Emulates iSCSI, NFS & SMB3. There also at least 2 free license offerings. One is a 2-node license available for all, it can provide fault tolerant NFS & SMB3 storage. Production use is supported. Second one is a full blown Free 2-node Virtual SAN which delivers fault tolerant storage via iSCSI, DMA, NFS, and SMB3. Production use also supported. To get that one you need to be either an MCP/MVP or VCP/vExpert or an active online communities contributor. (I currently got the second one, you'll have to contact them to get one)

Here's a video on SQL AlwaysOn FCI deployment with virtual SAN storage (it's in Azure but the process is the same)http://www.edwinmsarmiento.com/running-a-sql-server-failover-clustered-instance-on-microsoft-azure/

Stuka
  • 5,181
  • 13
  • 11
2

Windows Server does not support shared-nothing Failover Clusters as of yet. You need a shared device which would support SCSI reservations to serve as cluster storage (in any kind of role). This is going to change with Windows Server 2016 which is introducing Storage Replicas:

Storage Replica is a new feature in Windows Server 2016 Technical Preview that enables storage-agnostic, block-level, synchronous replication between clusters or servers for disaster preparedness and recovery, as well as stretching of a failover cluster across sites for high availability. Synchronous replication enables mirroring of data in physical sites with crash-consistent volumes ensuring zero data loss at the file system level.

But your problem will be the one of a hen-and-egg type. You want a witness share so your cluster will be able to build up a quorum for a failover decision in cases of failure. You want to place this witness share on a cluster of machines consisting of 2 physical nodes. So what you need now all of a sudden is another witness for your file server cluster. This is not going to solve your problem.

The bottom line is: you should not create a file server cluster just for the Availability Group's witness share. The witness' availability is not as crucial as it will not affect operations of your SQL server instances. If the witness is unavailable, all you lose is the automatic failover capability to other member(s) of your AG.

This being said, you still should take care to keep the time-to-repair for the witness share low. Especially since it likely will cost you nothing but documentation of procedures - a standby backup machine which would need to cope with just 2 connections at low data rates can literally be your boss' domain-joined desktop over a VPN connection.

the-wabbit
  • 40,319
  • 13
  • 105
  • 169
  • 5
    Storage Replica is Disaster Recovery tool so it will NOT do "shared nothing" clusters, it still needs compatible cluster-aware storage at both sides for stretch cluster. Storage Spaces Direct will do what you say though. https://technet.microsoft.com/en-us/library/mt126102.aspx – BaronSamedi1958 Jan 18 '16 at 16:50
  • 3
    Agreed, but to get all Storage Spaces Direct features you should start from at least three physical nodes. – Stuka Dec 19 '16 at 14:24
-2

You ca also use DFS with two servers and local storage. Just configure the DFS correctly. I'm sorry, but I cant provide more Infos at this time.

Andreas Schmidt
  • 1
  • 6
    DFS-R is nice but it has set of drawbacks. It cannot copy open files (without use of 3d party software like Peerlock) and this limiation technically disables ALL of the applications never closing files during work including Hyper-V, Exchange, SQL Server etc. DFS FAQ can be handy on DFS limits. https://technet.microsoft.com/en-us/library/cc773238(v=ws.10).aspx – BaronSamedi1958 Jan 18 '16 at 16:44