Architecture: I have 2 Node Sync-Commit AlwaysOn configuration running on Multi-Subnet Failover Cluster. Primary node is in Europe and Secondary node is in US. I have only one database in the Availability Group which is OperationsManager db of SCOM.
- Primary and Secondary Hosts are identical.
- SQL Server Version on both boxes: 13.0.5237 & Windows Core
- Update: I patched both servers to 10.0.5270.0 , it didn't help.
- DB VLF count is only 27.
Problem: When I initiate a failover, database fails over from Primary to Secondary node successfully in seconds. However, new secondary(old primary) database goes into Reverting / In Recovery phase and stays there for 30 minutes approximately.I also experienced same thing while failing back to original primary box so it's a problem which occurs both ways.
Findings: I searched about this on internet and read documentation to investigate the issue. When role change from Primary to Secondary is finished, new secondary database goes through 3 phases:
Synchronization State: “NOT SYNCHRONIZING” ; Database State: ONLINE
Synchronization State: “NOT SYNCHRONIZING” ; Database State: RECOVERING
Synchronization State: “REVERTING” ; Database State: RECOVERING
In my case, all time has spent on last step. I also monitored the undo process by looking into perfmon counter "SQLServer:Database Replica Log remaining for undo"
I checked primary site before failover tests to spot any long running transactions or open transactions but couldn't find one. After failover, "Log remaining for undo" was around 30MB and it took 30 minutes for secondary database to go back to "Synchronized" state. When taking into consideration that we are running in Sync-Commit mode and there is a little workload on primary, it shouldn't take 30 minutes for redo phase imho.
SQL Server Error Log: I found this strange messages.
Remote harden of transaction 'RECEIVE MSG' (ID 0x000000004d52c65a 0001:01c4e415) started at Feb 22 2019 2:55PM in database 'OperationsManager' at LSN (2558:107841:1) failed.
Remote harden of transaction 'GhostCleanupTask' (ID 0x000000004d6d15aa 0001:01c4eaa0) started at Feb 22 2019 2:59PM in database 'OperationsManager' at LSN (2558:107843:46) failed.
All in all
Have you ever seen this problem before? Do you have any recommandations?