2

Say we have two datacentres, DC-A and DC-B. Say in each DC, there are two SQL servers. DC-A has SQL-A1 and SQL-A2 and DC-B has SQL-B1 and SQL-B2.

Is it possible to have SQL with an AlwaysOn availability group running synchronously within each datacentre, and an asynchronous push from the primary site to the secondary? Such that:

SQL-A1 and SQL-A2 are synchronously replicating
and
SQL-B1 and SQL-B2 are synchronously replicating
and
SQL servers in DC-A are asynchronously replicating their synchronous copies across to DC-B SQL servers.

Is this possible?

1 Answer 1

1

No that is not possible. The commit mode (sync/async) is between primary and secondary logical roles, and not between secondary and secondary (which is why are you aiming for).

I'm not sure what you'd even gain from that. All your logical design would be adding (even if it was possible) would be a second hop of log blocks for SQL-B2. You would want your data to go from SQL-A1 (primary) -async-> SQL-B1 (secondary) -sync-> SQL-B2 (secondary). Like I said before, that isn't possible. But what is possible would be SQL-A1 (primary) -async-> SQL-B2 (secondary).

The only thing your projected design would optimize would be network traffic between the data centers. To reiterate, this discussion is just theory only. Functionality dictates moving forward.

5
  • I think it would make more sense (given the # of instances and DCs) to make A1 and A2 clustered syncing to B1 and B2 clustered as well. Depends on what your real goal is here, but that would be a more common setup given the resources outlined. Mar 27, 2015 at 16:00
  • The client wishes to have High Availability for their SQL databases at both sides - such that if DC-A was to fail, DC-B was still highly available and resilient to 1 hardware/server failure. Using AlwaysOn where the two synchronous nodes at DC-A are pushing asynchronously to distinct SQL nodes at DC-B would not provide this.
    – Ashley
    Mar 27, 2015 at 16:15
  • @AshleySteel that sounds like what I described: two-node HA clusters in each data center that replicate from A to B via AlwaysOn. Are you familiar with Failover Cluster Instances ? Mar 27, 2015 at 17:41
  • Ok, great! The client had previously dismissed FCIs as they didn't want (shared) storage to become the single point of failure. I'll persuade them to reconsider.
    – Ashley
    Mar 28, 2015 at 0:57
  • Thomas have awarded to you, but if you could incorporate a suggestion to instead consider FCIs as per @John's comment, that'd be grand.
    – Ashley
    Mar 31, 2015 at 23:01

Your Answer

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

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