0

For SQL Server Enterprise's Always On availability groups in Synchronous Commit mode, is there some amount of latency that can't be exceeded? For example, can it work between very distant data centers, such as between the US and Europe?

  • The answer will depend much on your workload and performance SLAs. Add those details to your question. – Dan Guzman Oct 18 at 11:46
  • @DanGuzman: I'm only talking about communication latency. Even with a very low workload, I'm trying to find out if SQL Server will be able to handle the two-phase commit if the slave's write confirmation message takes too long to be received by the master, simply as a result of the huge distance between the master and slave. – uncaged Oct 18 at 13:05
0

The answer you're looking for is in the docs

⚠ Note

If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode.

When this happens, a synchronous secondary will fall back to be asynchronous (what some folks call "pseudo synchronous" because you set it to synchronous, but it's actually async). This is not specific to network latency, or physical distance, but can happen under a number of scenarios. As an example, if you restart a synchronous replica and that server is offline, your AG will fall back to asynch and continue to process transactions.

Synchronous AGs only pay a performance penalty on writing to the transaction log (which needs to write to the secondary replica). Write activity is often a minority of the database activity, with the majority of traffic being read operations, which are not affected by synch/async.

Running an AG in sync commit mode between US & EU is totally possible. Write transactions will be slower, as they need to write to the log on the other side of the ocean. Generally, a better design is to keep synch replicas closer to avoid the network latency. However, depending on the IO patterns of your database, your business needs, and your network backbone between geographies, it's possible. On the systems I support, we can tolerate synchronous commit latency across hundreds of miles--but when the distance gets longer than that the performance impact becomes too great. It's not that SQL Server can't support it--it's that the users will not tolerate that slowness.

Generally, I find most folks end up running synchronous commit within a data center/geography for high availability, and asynchronous commit between geographies/data centers for disaster recovery. For a planned failover across geographies, you can temporarily switch the remote replica to synchronous mode to ensure no data loss during the failover.

The easiest way to find out the actual impact on your application is to test it.

| improve this answer | |
-3

A Secondary replica should almost never be configured in synchronous mode and in particular if there is latency between the primary and secondary as in your case as it will result in a degraded performance at the primary replica leading to contention issues and lots more. In addition, remember that the 2 data centers can always have a network disconnection... When you work in synchronous mode you can think of it as almost like a 2-phase commit.

| improve this answer | |
  • This answer doesn't consider that for many workloads, uptime and preventing data loss may take priority over write performance. Synchronous AGs only pay a performance penalty on writing to the transaction log which is often a minority of the database activity. In my work, I ALWAYS want a synchronous replica, sometimes even being willing to switch to synchronous across data centers. – AMtwo Oct 18 at 16:35
  • @AMtwo: Exactly! I'm trying to determine how far those data centers can be (in terms of message latency), for SQL Server's synchronous replication to work, and not fail because of some timeout. – uncaged Oct 18 at 17:38
  • @uncaged and you can only get the answer by testing. – Shanky Oct 19 at 6:48
  • @AMtwo: Working with this feature since the day of it's release in so many environments I am confidence in backing up my answer. It is important to make the distinction between the theory and real life. Deploying synchronous commit mode between the 2 replicas at the network described in the question is something I believe would not stand the real life scenario. – Yaniv Etrogi Oct 19 at 8:20
  • Yes, I am also answering from experience. – AMtwo Oct 19 at 10:53

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