How to Reduce Downtime Using Accelerated Database Recovery in SQL Server 2019?

Summary: In SQL server, the existing process (Algorithms for Recovery and Isolation Exploiting Semantics or ARIES) of recovering the database is directly proportional to the largest ongoing transaction at that moment of crash. In such case, if the ongoing transaction is too long then the database downtime is also prolonged.  Such database downtime situation becomes more critical, if it is impacting the availability of a vital web-application. However, with the introduction of Accelerated Database Recovery (ADR) in SQL Server 2019, this impact can be reduced.  Let’s check how to reduce downtime using ADR in SQL 2019.

In SQL Server 2019, the modifications in three-phase process of Accelerated Database Recovery (ADR) – Analysis, Redo, Undo has redesigned SQL database engine recovery process. It helps SQL administrators in rolling back long running transactions thus ensuring faster recovery of SQL database and reducing downtime.

Note: ADR is currently available for single databases

Why should SQL administrators use Accelerated Database Recovery?

ADR reduces downtime in SQL Server 2019 and provides the following benefits to SQL administrators:

1. Database Availability:

Provides fast and consistent recovery of SQL database. With ADR, the long running transactions do not impact overall recovery time and the database is available irrespective of the number and size of active transactions.

2. Immediate Transaction Rollback:

Performs instant rollback of active transactions, irrespective of the time-period and number of updates applied to the active transaction.

3. Forceful Truncation of Transaction Logs:

Prevents the active long running transactions from growing abnormally by truncating them, forcefully. 

How does ADR help in reducing downtime in SQL Server 2019?

In MS SQL 2019, ADR performs three-phase recovery process – Analysis, Redo and Undo. It is performed with the help of following key recovery components, which help in speeding up the database-recovery process:

• Persisted Version Store (PVS) – PVS, the new engine mechanism in SQL database enables resource isolation which helps in enhancing the availability of readable secondaries.  It contains previous versions of database as modified by any transaction. 
• Logical Revert – An asynchronous process that performs row-level version-based Undo. It provides an instant transaction rollback, and allows undo for all the versioned operations. In SQL database, a logical revert is performed with the help of the following functions:
  • Tracks all aborted transactions and ensures that these are invisible to other transactions
  • Performs rollback with the help of PVS for all user transactions
  • Releases all locks after the transaction is aborted
• sLog – sLog, a secondary in-memory log stream is used to store log record of non-versioned operations. The sLog is persisted on disk and gets serialized at the checkpoint process.

It periodically truncates the committed transactions and preserves only the required log records. Also, processes only the non-versioned operations thus accelerating undo and redo phases.

• Cleaner – Performs a periodic clean-up of the redundant page versions.

A comparative analysis of ADR and ARIES three-phase processes:

The Accelerated Database Recovery process

ARIES Database Recovery process

In short, the redesigned SQL database engine recovery process of ADR addresses all the SQL related issues and performs faster recovery due to the following reasons:

1. Avoids scanning of logs from the beginning of the oldest active transactions.
2. Transaction log is processed from the last successful checkpoint, so the recovery time is not impacted by long running transactions.
3. As there is no need to process the log for the whole transaction, the required transaction log space is minimized. 
4. Transaction logs are truncated aggressively with every checkpoint and backup.

Wrapping up

In SQL database, Accelerated SQL Database Recovery helps to achieve faster database recovery by versioning all modifications in physical database. ADR performs undoing of all limited logical operations, ensuring that these can be undone almost instantly. As the active transactions at the time of a crash are marked as aborted, any version generated by these transactions are ignored by the concurrent user queries.

However, the process of Accelerated Database Recovery is not activated by default. And as mentioned earlier, it is currently available for single database only. If there are multiple databases or if ADR is not activated in SQL 2019, then there are only two ways to recover MS SQL database – a restorable and updated backup or an effective SQL database repair software such as Stellar Repair for MS SQL.