2

We have an application that needs access to the database at all times to work properly and we are planning on deploying SQL Server 2012 AlwaysOn Availability Groups on Windows Server 2012.

We have two geographically separated data centers and we plan to keep one DB server in DC1 and the other in DC2.

All the information that I've seen shows a local synchronous copy and an asynchronous copy in another data center. I wanted to know if there is a way to configure the availability group with just two SQL servers which are separated geographically and can support automatic failover.

4
  • yes , you can. What is the Domain and WorkGroup ? AlwaysOn Availability is depend on your Domain & workgroup. sqlpassion.at/archive/2012/03/21/… Aug 10, 2015 at 8:55
  • Both the servers would be part of the same domain. Can the second database be kept as a synchronous commit replica which is geographically separated?
    – user72471
    Aug 10, 2015 at 11:02
  • yes you can in Synchronous Commit, but I do not recommend using this for servers which reside outside of your primary site. A large geographic distance increases the performance hit. Aug 10, 2015 at 11:15
  • With sync commit over long distance not only your performance will suffer but also the availability will be worse than any server under your desk. With added complexity comes downtime. Consider two sync servers on one site and a async dr partner on another site instead.
    – eckes
    Feb 20, 2019 at 23:30

2 Answers 2

0

You can find here some design pattern for always on solutions http://blogs.msdn.com/b/sqlcat/archive/2013/11/20/sql-server-2012-alwayson-high-availability-and-disaster-recovery-design-patterns.aspx

just note that for automatic fail-over you may want to have synchronous commit so you dont loose data. also to have automatic fail-over you must have node majority to decide to do the fail-over. this mean you must have extra voter in the cluster. (eg' File Share Witness or another node)

this will prevent the problem known as Split Brain problem (https://en.wikipedia.org/wiki/Split-brain_(computing))

to summarize, this is very common need to have local automatic fail-over (because local LAN can serve synchronous mode) and another replica off-site with manual fail-over and asynchronous commit which can cause data loss when main site failed and not all data replicated yet to the another site.

I hope this have been informative for you. I will be glad to add information if you have any questions about that implementation.

2
0

Yes, you can technically support automatic failover in this scenario.

However, there a few things you must be aware of and/or check first.

  1. To be able to offer automatic failover, your replicase must be in synchronous commit mode.
  2. when in synchronous commit mode, transaction must be able to successfully commit on your secondary replica before they commit on your primary replica.

Does your WAN offer low enough latency for your transactions to be able to commit in DR first, without causing undesired latency for your production commits?

The next thing you need to worry about is quorum. If you're only planning to have two nodes in your AG, you'll need a file share somewhere (or another form of quorum) to offer a tie-breaker vote. You'd need this file share to be somewhere where both replicas can see it.

Also, when failing over between subnets, you need to make sure your SQL Clients are multi-subnet aware. If they're not, you can face potential timeout issues with the default settings. To avoid that, you'll need to set RegisterAllProviderIP = 0 at the cluster level, so that SQL is not registering your Prod and DR listener IPs in DNS at the same time. Further to avoid as much as 20 minutes of downtimes while the old DNS records age out in this configuration, you'll also want to lower your TTL down to about 2 minutes or so.

In my opinion, I never want a replica to failover to my DR data center without that action being initiated manually. There are generally a lot of things that need to happen in coordination with that, outside of SQL Server. If you choose to go this route, make sure to test it well.

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.