Why you should … use Accelerated Database Recovery

Timeline Description automatically generated

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.

Accelerated Database Recovery timeline

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.

Please share this
Tags
#microsftcertifedmaster #microsftpartner #parallelism #azure #SQLAB #SQlatino #SQlatinoamerica #sqldatabase #sqldeveloper #SQLgroupie #sqlimer #sqlimerbymay #sqlinjection #sqlinternals #sqlite #sqlite3 #SQLLearning #SQLMagazine #sqlmanagementstudio #sqlmanager #Sqlmap #sqlrun #sqlsaturday2017 #sqlsatvienna #sqlserver #SQLserver2012 #sqlserver2014 #sqlserver2017 #sqlserver2022 #SQLServeronLinux #SQLsolutionsgroup #SQLTraining #SQLYog Events Extended Events learning SQL SQL Administration SQL Internals SQLPASS SQLSaturday SQL Saturday SQL Server SSG SSIS T-SQL
Picture of Jason Russell

Jason Russell

For as long as I can remember, I’ve enjoyed writing. It started with short stories and grew into articles and opinion pieces in the school newspaper. This passion for communicating clearly and well—and, when warranted, with some flair—lead to a Bachelor’s Degree in Public Relations from Brigham Young University in 1996. After two years in Mexico, Mo. as the Director of Information and Publications at the Missouri Military Academy in Mexico, Mo., (exactly two hours from Busch Stadium) I joined the public relations team at Morinda, Inc. Since that time, I’ve been more involved in marketing communications, helping to shape and share the messages of several companies, including MONAT Global in Miami, FL, where I and my team won numerous awards for our work.

This Post Has One Comment

Leave a Reply

Related Articles