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?
Best Answer
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.