Know Accelerated Database Recovery Feature in SQL Server

Read time 4 minutes

In this blog post, we’ll explore SQL Server’s Accelerated Database Recovery feature and its benefits for users patiently waiting for running recovery transactions to complete.

There are various situations where SQL Server initiates automatic database recovery, such as when an administrator is executing a substantial DML query update. If, for unfortunate reasons like deadlocks or performance issues, the query needs to be terminated midway, it enters the Rollback state, a time-consuming recovery process, leaving the user waiting for its completion.

Consider another scenario: if a user is inserting a substantial amount of data into a table and the SQL Server system crashes. Upon system restart, restarting SQL Services may show them online, but they remain in recovery mode for an extended period, requiring users to endure another waiting period.

To address the prolonged wait during SQL database recovery, a new feature called Accelerated Database Recovery has been introduced. Let’s delve into the details of this feature.

Accelerated Database Recovery (ADR) – Introduction

Recently introduced in the SQL database engine, Accelerated Database Recovery (ADR) enhances the database recovery process, ensuring faster recovery of databases and ongoing transactions. This feature is accessible for both single and pooled databases in Azure SQL Database, as well as for all databases in Azure SQL Data Warehouse. ADR comprises the following components:

1. Persistent Version Store (PVS) Mechanism –
   This mechanism allows persisting the row versions in the database only rather keeping it in the old tempdb store. Through PVS,
   • More Readable Secondaries
   • Resource isolation is enabled
2. Logical Revert Process –
   It is an asynchronous procedure which offers quick rollback/undo operations. Through Logical Revert,
   • Rollback for all user transactions via Persistence Version Store
   • Immediate releasing of all locks once transaction is aborted
   • Storing track for all the aborted transactions
3. sLog Service –
   It is a low-volume, secondary in-memory log stream for storing log records for non-versioned operations. Through sLog,
   • Periodic truncation of sLog
   • Accelerated Redo and Undo processes via non-versioned operations only
   • Faster log transaction with only required log records
4. Page versions Cleaner –
   As the name suggests, it cleans unrequired page versions, works periodically and in non-synchronized manner.

So, we now know what Accelerated Database Recovery feature is and about its components. Let us discuss more on its working process.

Read Also : How to Recover Deleted Records in SQL Server?

ADR incorporates a revamped SQL database engine recovery mechanism, achieving accelerated recovery by versioning all physical database alterations while undoing specific logical operations. Transactions active during a system crash are marked as aborted, enabling concurrent queries to bypass transactions generated by these affected operations.

The 3 improved phases in Accelerated Database Recovery are as follows:

This phase has improvements like

• Introduction of reconstructing sLog
• Storing log records for non-versioned operations

This phase is improved by breaking it into 2 phases.

• In the first phase, Redo is performed through sLog which starts from the last uncommitted transaction to the latest checkpoint.
• In the second phase, Redo is performed through transaction log which starts from the latest checkpoint.
• First phase is faster operation than the second phase as few records are needed to be processed in sLog.

Phase 3 – Undo

This Undo phase with ADR is performed very quickly.

• Using sLog to undo non-versioned operations.
• Row-level version -based Undo is performed using Persisted Version Store with Logic Revert.

Accelerated Database Recovery offers a solution for SQL users dealing with prolonged transactions and extended database downtime. Additionally, for severely corrupted databases beyond regular access, Kernel for SQL Database software provides an effective remedy. This SQL Recovery software handles all errors and corruption issues quite smoothly and recovers database of all SQL versions in a very short time.