Why you should … use Accelerated Database Recovery

Included in SQL Server 2019 (and also the upcoming SQL Server 2022), Accelerated Database Recovery (ADR) improves database availability. Microsoft outlines the main benefits of ADR as:

• Fast and consistent database recovery
• Instantaneous transaction rollback
• Aggressive log truncation

As with the Query Store tip we shared earlier, the key point with ADR is: You need to turn it on. (FYI, Azure SQL Database and Azure SQL Managed Instance enable it by default.)

ADR utilizes the same Analysis, Redo, and Undo steps of the earlier recovery process, but differently. Redo first uses the system log from oldest uncommitted transaction to last checkpoint, and then from the last transaction, rather than oldest uncommitted transaction. Undo works similarly. As a result, SQL Server does not have to scan the log from beginning to end.

Quoting Microsoft again, “ADR achieves fast database recovery by versioning all physical database modifications and only undoing logical operations, which are limited and can be undone almost instantly.”

So turn on ADR and enjoy faster recoveries!

Accelerated Database Recovery Explanation and Demos

Here’s a brief explanation of ADR (with demos) from Senior Consultant Scott Klein from a September 2022 webinar.