Sql Server Availability Group database out of sync while status shows "Synchronized"
1
vote
1
answer
176
views
I have an availability group setup on Sql Server 2022. It was working fine for some days until some strange problem happened.
The workload that was querying data from secondary readonly replica, was showing invalid data. I have investigated the problem, and it turned out that the secondary database is not being synced with the primary and new changes was not present on it.
The database was not reporting any problem related to the AG. The AG's dashboard was all green, the status of databases on primary and secondary was "Synchronized", and no problem in terms of resources (cpu, memory and disk) was detected on any nodes. After a restart of Sql Server on the secondary node, the syncing started again and it is working till now.
This was a very disappointing experience, so I cannot trust this setup anymore. The automatic failover would fail for sure, if I did not detect the problem in time. Also scaling workloads on readonly replicas is now a very risky thing to do.
Have you any experience like that before? Am I missing something here? Do you know any ways to monitor and detect the situation (the reporting of the database itself is not good enough, as it seems)?
Asked by Ahmad
(153 rep)
Jul 27, 2024, 03:29 PM
Last activity: Jul 8, 2025, 04:02 AM
Last activity: Jul 8, 2025, 04:02 AM