0

I have the following problem / challenge:

We operate an HA SQL cluster with several Availability Groups.

In order to take full advantage of the load balancing, I redirect the SQL selects to the secondary host so that the primary is not additionally loaded.

This also works flawlessly and super fast.

BUT...

If I do an INSERT, UPDATE or other dml out of an application and immediately after the result with a SELECT, the newly updated entry is not displayed correctly.

My suspicion is that the replication on the secondary is simply too slow.

Can it be because of the latency for hardening the log?

How can I speed it up completely?

5
  • 1
    Is the readable secondary availability mode synchronous or asychronous? The answer depends on that setting.
    – Dan Guzman
    Oct 14 at 10:53
  • 2
    Just because the transaction commits on the primary, there can still be a delay in the data being available to read on a secondary, just because the data in the log is hardened doesn't mean the changes are applied to the data pages, that is done (afaik) by a background thread and can be impacted by other processes running on the secondary.
    – sTTu
    Oct 14 at 11:33
  • A quick search yielded lots of promising things to read: this, this, this, this, this... Oct 14 at 12:19
  • @Aaron Bertrand: thx 4 the links, I´ll have a look on it. Oct 14 at 13:58
  • @Dan Guzman it´s running in synchronous commit mode Oct 14 at 13:59
1

If I do an INSERT, UPDATE or other dml out of an application and immediately after the result with a SELECT, the newly updated entry is not displayed correctly.

That is correct. If the most up-to-date data is required, then you'll need to query the primary replica. This also goes for transactional replication in whatever form you're using (strict TR, P2P).

My suspicion is that the replication on the secondary is simply too slow.

Could it be slow? Maybe. However it's also "by design", whether people like that design or not, that there is latency in the process. Maybe one day I'll write something walking through it, but until then, I've been consistently updating the docs.

Specifically, the anchor linked to above was put in a while ago in response to these questions. We've had a few requests for a new type of synchronization which would be "don't move on until the transaction is replayed on the/all secondary replicas". You can imagine the insane amount of latency for that in any cloud environment and most if not all on-prem environments as most I see are using slow SANs, underwhelming networking, and undersized cpu/memory.

Implementing read-only access to secondary replicas is useful if your read-only workloads can tolerate some data latency. In situations where data latency is unacceptable, consider running read-only workloads against the primary replica.

Can it be because of the latency for hardening the log?

Shrugs. Could be.

There are a plethora of factors here. A simplified overview of the workflow is the first link that @Aaron Bertrand has in his comment (which is a great link). Hardening and Redo are decoupled. This also doesn't take into account version of SQL Server, load, hardware, serial/parallel redo, etc., and thus it'd need data to be captured which is also non-trivial for a Q&A site. Additionally you'll need to cut the data up, which is also non-trivial, it's why there isn't an easy right-click menu in SSMS to do it.

How can I speed it up completely?

Accept that it's not possible to "completely" speed it up. Depending on the size of the transaction and various other items I've eluded to previously, you might be able to get it redone and visible within 10s of milliseconds, but it wouldn't be instantenous.

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.