February 12, 2015 at 12:30 pm
We are experiencing an issue when we perform an update to one column within one row of data against an AlwaysOn Availability Listener in a single transaction then commit the transaction and read the newly updated row from a new connection with Application Intent=ReadOnly connection string.
The synchronous secondary replica is not returning the updated data for quite some time. We added a delay to the read and it takes close to 1000 ms before the update is available on the synchronous secondary replica. Is this latency expected behavior?
We performed this test on multiple implementations of an AlwayOn Availability Group as detailed below with the exact same result.
The first test was conducted on the following configuration: 64-bit Windows Server 2008 R2 SP1 Enterprise, 64-bit SQL Server 2012 Enterprise SP2 Version 11.0.5058.0.
The AlwaysOn Availability Group consists of 3, bare metal Dell R710s with a RAID 1 for OS and RAID 5 for SQL Server.
The primary server and one secondary server are configured with Synchronous Commit Availability Mode, Automatic Failover, Readable Secondary set to yes for the pair. The third server is configured with Asynchronous Commit Availability Mode, Manual Failover, and Readable Secondary set to no. Read-Only Routing was configured to route Read-Intent traffic to the second node when the first node is in the Primary Role and vice versa.
A second test was conducted on the following configuration: 64-bit Windows Server 2012 R2 Standard, 64-bit SQL Server 2012 Enterprise SP2 Version 11.0.5058.0.
The AlwaysOn Availability Group consists of 2, bare metal Dell R620s with a RAID 1 on SSDs for OS and Fusion-io ioDrive2 785GB card for SQL Server.
The primary server and one secondary server are configured with Synchronous Commit Availability Mode, Automatic Failover, Readable Secondary set to yes for the pair. A file share was setup for a node and file share quorum. Read-Only Routing was configured to route Read-Intent traffic to the second node when the first node is in the Primary Role and vice versa.
A third test was conducted on the following configuration: 64-bit Windows Server 2008 R2 SP1 Enterprise, 64-bit SQL Server 2012 Enterprise SP2 Version 11.0.5058.0.
The AlwaysOn Availability Group consists of 3, bare metal Dell R720s with a RAID 1 on SSDs for OS and Fusion-io ioDrive2 785GB card for SQL Server.
The primary server and one secondary server are configured with Synchronous Commit Availability Mode, Automatic Failover, Readable Secondary set to yes for the pair. The third server is configured with Asynchronous Commit Availability Mode, Manual Failover, and Readable Secondary set to no. Read-Only Routing was configured to route Read-Intent traffic to the second node when the first node is in the Primary Role and vice versa.
February 13, 2015 at 3:38 am
Mike G. (2/12/2015)
Is this latency expected behavior?
Synchronous commit only guarantees the transaction is fully committed to the log on all partners, the log record will still have to be replayed by the redo thread which is single threaded. If the redo queue is large it can take a short while.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2015 at 9:56 am
Thanks Perry, our developers were hoping that the transaction was instantly available on the sync. secondary. We will adjust expectations accordingly!
February 15, 2015 at 3:51 am
Typically you will see a minute lag, much as you are now, you'll always have to allow for how busy the secondary may be.
Also, as the redo queue grows this will have an impact to.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply