I've just bumped into a new situation regarding alwaysOn availability. We have a setup with a large database (terabyte range) with one primary and one secondary replica. Geographically the servers are located a small distance apart. Log and full backups are taken from the secondary replica, and index maintenance is obviously run on primary. Sometimes these schedules overlap, if the full backup takes particularly long to complete.

For several years now, this setup has worked perfectly, until recently. Now we have had several nights where at a certain, specific time the log backups on secondary fail, giving an error on primary having a more recent backup than secondary (when there are no backups taken from primary at all). At the same point, the synchronization state is "synchronized and healthy", but the dm_hadr_database_replica_states system view shows that last_received_time is stuck at the same time as the failure of log backup on secondary, and the redo queue is ever growing. As a result, the log backup jobs fail and the log keeps increasing indefinitely. Once suspending and resuming the replication helped, but otherwise we've had to boot the entire secondary replica server (booting the service just got it stuck).

At first we thought it was a combination of some network error at a specific time, and some hitherto unknown bug in alwaysOn not resuming or reporting its state correctly. But then we reproduced this during the day: We were taking a full backup and reading a high volume of data for an ETL package, from the secondary replica, during which the log backups started failing. As we aborted the backup, after a while the replication caught up and started working again.

So the question: What can cause alwaysOn to behave this way? It seems extremely dangerous to me that a high availability feature can get into a state where the alarms don't work, because the synchronization state is on and supposedly healthy, while in reality it's not. And that it can't restore itself even during no additional load, but just gets stuck until rebooted.

  • 1
    There's a series of articles on this starting centinosystems.com/blog/sql/… - might be of help when figuring this out. – Thomas Rushton Jan 5 '17 at 9:27
  • 1
    That was great, thanks! Still doesn't answer the question about how the synchronization isn't resumed properly, given enough time, but a great article nevertheless! – Kahn Jan 5 '17 at 9:57
  • @Kahn, Did you checked through 'show dashboard' apart from TSQL script dm_hadr_database_replica_states; in primary replica. There is option to check 'View Alwayson Health Events' through that you shall get exact events. which has happened in your alwayson availability group. – Md Haidar Ali Khan Jan 5 '17 at 10:20
  • @MdHaidarAliKhan, I don't know what you mean. Is that some 3:rd party addon? I'm working with a standard SQL Server 2012 Enterprise version with no addons. – Kahn Jan 5 '17 at 11:29
  • 1
    @Kahn,It's not 3rd party addon. it's microsoft alwayon feature , which comes from SSMS tools.Just right click on 'primary replica' server node then 'show Dashboard'. Look out the right top of the Dashboard window. – Md Haidar Ali Khan Jan 5 '17 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.