3

We are planning to upgrade our SQL 2012 to SQL 2014. We currently use a mirror for our production databases but are planning to move to availability groups. We are going to be running 2 SQL Servers on our primary site and a single asynchronous replica at DR.

Our link to DR is sometimes quite patchy and so we don't want to rely on DR to have a quorum, which is also the advice from Microsoft: https://msdn.microsoft.com/en-us/library/hh270280.aspx. To provide an odd number of votes we are planning to have a node and file share majority (2 nodes + 1 file share at the primary site = 3 votes).

A couple of times a year we need to fail over to DR, either to test or because of maintenance (Electrical/Connectivity/etc) work at our primary site.

To failover to DR, we can set the DR replica to be synchronous, wait for it to catch up and then failover either using a script or the failover wizard. I've setup a test using 4 servers in Hyper-v (3x SQL + 1x AD). Once I do the failover to DR and then shutdown the SQL Servers the cluster goes offline.

I notice that the same msdn article I mentioned above says

• Re-assess vote assignments post-failover. You do not want to fail over into a cluster configuration that does not support a healthy quorum.

What would be the best adjustments to make to maintain quorum after a planned failover to DR and the Primary site being completely offline (including the 2 primary SQL nodes and file share)?

  • Also, we have considered hosting the file share elsewhere, however our internet connection runs through DR (don't ask, it just does) so if DR drops offline then a remote file share would as well. – Greg Mar 24 '15 at 5:08
1

It looks like the answer is Server 2012 R2. 2012 R2 only includes the file share when there are an even number of nodes. So if DR goes offline then the file share will be included in the quorum until such time that DR comes back online.

It also looks like from testing that if you gracefully shutdown connections on secondary's then the primary stays up. So to run from DR for a planned outage means we could failover gracefully and then shut down the servers at the primary site. Because DR became the primary, it will remain up as the last node standing until such time that quorum is restored (and synchronization is complete)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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