question

long avatar image
long asked

how to sync alwayson secondary node without delay at all

I have a sqlserver 2016 always-on with one primary and one secondary database. However I got nothing when I query the secondary database right after I get confirmation from primary database that insert is done. So I think this is because primary db confirm the transaction after log received at secondary db, however secondary database takes some time to write record into db after it received the log from primary db. So there are definitely some delay between primary and secondary db, question is how can I query secondary db instantly after I get confirmation from primary db?
sqlserveralwaysonsync
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What mode is the secondary replica in?
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
There will always be latency on a secondary node, even in synchronous-commit mode. Synchronous refers to the fact that the transaction has been hardened in the log file on the secondary, but the process to get the database updated happens asynchronously in the redo step. Typically the latency is low, but you can experience blocking in the REDO thread on the secondary nodes, possibly showing as REDO_SYNC wait types. If the secondary is asynchronous, then obviously there are even more moving parts that could delay the replica from being updated. I would suggest looking at these articles: - [MSDN Blogs - Monitor Performance for AlwaysOn Availability Groups][1] - [SQL server AlwaysOn Availability Group data latency issue on secondary replicas and synchronous state monitoring][2] [1]: https://msdn.microsoft.com/en-us/library/dn135338(v=sql.110).aspx [2]: http://geekswithblogs.net/FrostRed/archive/2017/04/30/244536.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.