6

If one refers to the MSDN documentation, on Synchronous-Commit Availability Mode, you may read:

Under synchronous-commit availability mode (synchronous-commit mode), after being joined to an availability group, a secondary database catches up to the corresponding primary database and enters the SYNCHRONIZED state. The secondary database remains SYNCHRONIZED as long as data synchronization continues. This guarantees that every transaction that is committed on a given primary database has also been committed on the corresponding secondary database. When every secondary database on a given secondary replica is synchronized, the synchronization-health state of the secondary replica as a whole is HEALTHY.

Lets assume I have an three node Availability Group with a synchronized database in a HEALTHY state. All replicas are using synchronous-commit mode.

Additionally assume, I have configured read-only routing so that requests with ApplicationIntent=Read-Only connect to a secondary replica.

If I commit a change via a Read-Write connection then, very quickly, select the changed record via another connection using a ApplicationIntent=Read-Only connection, can I expect consistent results to be returned from both replicas, every time?


EDIT - Further Information supporting the accepted answer.

In the Microsoft Technical Paper "AlwaysOn: Offloading Read-Only Workloads to Secondary Replicas (Sunil Agarwal, July 2012)" The section under heading Data Latency reads (emphasis mine).

The reporting workload running on the secondary replica will incur some data latency, typically a few seconds to minutes depending upon the primary workload and the network latency. The data latency exists even if you have configured the secondary replica to synchronous mode. While it is true that a synchronous replica helps guarantee no data loss in ideal conditions (that is, RPO = 0) by hardening the transaction log records of a committed transaction before sending an ACK to the primary, it does not guarantee that the REDO thread on secondary replica has indeed applied the associated log records to database pages. So there is some data latency. You may wonder if this data latency is more likely when you have configured the secondary replica in asynchronous mode. This is a more difficult question to answer. If the network between the primary replica and the secondary replica is not able to keep up with the transaction log traffic (that is, if there is not enough bandwidth), the asynchronous replica can fall further behind, leading to higher data latency. In the case of synchronous replica, the insufficient network bandwidth does not cause higher data latency on the secondary but it can slow down the transaction response time and throughput for the primary workload.

If your reporting workload cannot tolerate any data latency, you must run it on the primary replica. The good news is that generally most reporting workloads can tolerate some data latency and therefore can safely be migrated to secondary replica.

Whilst the breadth of Microsoft documentation is not contradictory I feel it could be more explicit. "Synchronous" does not imply Atomicity and Consistency, as used in the ACID acronym.

9

SYNCHRONIZED state only ensures that the writes are hardened by the secondary (log written to disk). It says nothing about them being applied (data changed).

can I expect consistent results to be returned from both replicas, every time?

Yes. The reads are consistent, always. But keep in mind that in relational parlance consistency (ACID) has a different meaning from the distributed (CAP) consistency. You are not guaranteed to read the most recent consistent state. Particularly, you are not guaranteed to read your own committed writes. And the reads from each replica, while each being consistent, may not match.

  • 2
    And don't forget too that two databases in one availability group may each be in their own consistent state that doesn't match precisely. This should be rare but is also not guaranteed. – Aaron Bertrand Dec 5 '14 at 13:09
  • Thanks a lot, sqlserver always_on is not Oracle RAC, I tried to use it but now I have understood – Diego Scaravaggi Feb 6 at 15:53

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.