Reduce Downtime with Accelerated Database Recovery – SQL

In the SQL Server, the process of recovering the database is directly proportional to the largest ongoing transaction at the moment of the crash. If the ongoing transaction is too long then the database downtime also becomes lengthy. Such a database downtime situation becomes more important if it is impacting the availability of a vital web application. However, by introducing Accelerated Database Recovery (ADR) in SQL Server 2019, this downtime can be reduced. Let’s check how to decrease downtime using Accelerated Database Recovery SQL 2019.

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

Why should SQL administrators use Accelerated Database Recovery?

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

• Database Availability:

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

• Immediate Transaction Rollback: 

It also gets instant rollback of active transactions, irrespective of the time period and number of updates applied to the active transaction.

• Forceful Truncation of Transaction Logs: 

It prevents the active long-running transactions from growing abnormally by truncating them, forcefully.

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

In MS SQL 2019, ADR performs a three-stage recovery process – Analysis, Redo, and Undo. You can perform this with the help of the following key recovery components, which help in speeding up the database-recovery process:

1. Persisted Version Store (PVS) – 

PVS, the new engine mechanism in the SQL database enables resource isolation which helps in enhancing the availability of readable secondaries.  It contains previous versions of the database as modified by any transaction. 

1. 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. Within a SQL database, you perform a logical revert with the assistance 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

3. sLog – 

The system uses a secondary in-memory log stream called sLog to store log records of non-versioned operations. The sLog gets persisted on disk and serialized during 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.

4. Cleaner – 

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

In short, the redesigned SQL database engine recovery process of ADR addresses all the SQL-related issues. In addition to this, it also performs faster recovery due to the following reasons:

• Avoids scanning of logs from the beginning of the oldest active transactions.
• Processing the transaction log starts from the last successful checkpoint, ensuring that long-running transactions do not impact the recovery time.
• Since there is no requirement to process the log for the entire transaction, the system minimizes the necessary transaction log space.
• The system aggressively truncates transaction logs with every checkpoint and backup.

However, by default, the Accelerated Database Recovery process is not activated. As mentioned earlier, it is currently available for a single database only. If there are multiple databases or if ADR is not activated in SQL 2019, then recovering the MS SQL database is only possible through either restoring an up-to-date backup or using effective SQL Database Recovery software.

Closure

In this article, we discussed about accelerated Database Recovery SQL Server 2019 to achieve faster database recovery by versioning all modifications in the physical database. But ADR is available for a single database only. So, when it comes to recovering multiple databases then you have to rely on a professional tool such as SQL Database Recovery Software.

Related Post