Sql-server – Accelerated Database Recovery with AlwaysON — long recovery time for secondary unexpeccted

availability-groupshadrsql-server-2019

So I was intrigued by the new SQL 2019 Feature Accelerated Database Recovery. It has some great use cases, such as being able to kill a long-running transaction and have it instantly rollback.

I had read that it's supposed to improve database recovery much faster.

I was shocked to find out that in a load test comparison of an ADR-enabled and NON-ADR (default) database, the recovery time for the SECONDARY to begin syncing with the NEW primary was order of magnitude slower.

To date, I have been unable to find much information on ADR with AlwaysOn — except for a passing mention of it in a SQL 2019 talk he did, in which he claimed it improved the performance of AlwaysOn failover.

While the NEW Primary came up right away (although there was only milliseconds difference between the two databases), it took almost 3 minutes before the ADR-enabled database showed "Synchronized" in SSMS!

I ran several tests (which included serveral very large updates, and even included a large statement with a BEGIN TRAN, but no COMMIT. All test iterations had similar result: Recovery/Synchronizing of the SECODARY was order of magnitude long for the ADR-enabled database.

Has anyone else seen this behavior? enter image description here
enter image description here

Best Answer

I belive that Everything in the persisten version store would be transaction-log-causing activity, right?

This means that everything affected by that would receive a performance hit: – log size/backups – checkdb (gets hit by db size too) – ALWAYS ON – meaning it could get DELAYS FOR SYNCHRONOUS AG