Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server 2012 AlwaysOn synchronous replica is not actually synchronous for reading

I'm trying to read data from synchronous secondary replica just after writing data to primary replica. But my data is not present on secondary.

There is a latency about 100-500ms before data appears on secondary.

Is it possible to make secondary replica to be really synchronous for read queries?

like image 673
Dmitriy Startsev Avatar asked Jan 16 '14 11:01

Dmitriy Startsev


1 Answers

From MS in the article "AlwaysOn: Offloading Read-Only Workloads to Secondary Replicas":

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.

like image 101
Dima Avatar answered Oct 06 '22 18:10

Dima