SQL Server : changes from primary replica are not reflected on secondary replica for an Always On availability group

The client application completes an update on the primary replica successfully, but querying the secondary replica shows that the change is not reflected. This case assumes that your availability has a healthy synchronization state. In most cases, this behavior resolves itself after a few minutes.
If changes are still not reflected on the secondary replica after a few minutes, there may be a bottleneck in the synchronization work flow. The location of the bottleneck depends on whether the secondary replica is set to synchronous commit or asynchronous commit.

Following article has detail around it but i just thought to jot down exact point which helped me to resolve this issue. 

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/troubleshoot-primary-changes-not-reflected-on-secondary?view=sql-server-2017

As stated above, AlwaysOn dashboard was showing , secondary replica in synchronized state so ideally, both copies should have exact same data but that was not the case. 

Uopn further checking, I observed that that was heavy blocking to I've extracted root of blocking chain and killed those post approval obviously.  

As soon as blocking got clear, data got in sync.

Diagnosis and resolution

You can use the following DMV query to see how far the redo thread has fallen behind, by measuring the difference between the gap between last_redone_lsn and last_received_lsn.

select recovery_lsn, truncation_lsn, last_hardened_lsn, last_received_lsn,   
   last_redone_lsn, last_redone_time  
from sys.dm_hadr_database_replica_states  

Brgds,



Chhavinath Mishra

Sr. Database Administrator





Microsoft Certified IT Professional (MCITP)