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?
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] - [SQL server AlwaysOn Availability Group data latency issue on secondary replicas and synchronous state monitoring] :