We have an application that needs access to the database all the time 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.

  • yes , you can. What is the Domain and WorkGroup ? AlwaysOn Availability is depend on your Domain & workgroup. sqlpassion.at/archive/2012/03/21/… – Md Haidar Ali Khan Aug 10 '15 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 '15 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. – Md Haidar Ali Khan Aug 10 '15 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 '19 at 23:30

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.

| improve this answer | |

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.