How to Decrease Downtime Using Accelerated Database Recovery SQL Server 2019?

HIn the SQL Server, the process of recovering the database is directly proportional to the largest ongoing transaction at that 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, with the introduction of 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 database. 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 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. In a 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

3. sLog – 

sLog, a secondary in-memory log stream is used to store log records 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.

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.
• The transaction log is processed from the last successful checkpoint, so the recovery time is not impacted by long-running transactions.
• As there is no need to process the log for the whole transaction, the required transaction log space is minimized. 
• Transaction logs are truncated aggressively with every checkpoint and backup.

However, the process of Accelerated Database Recovery is not activated by default. And 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 there are only two ways to recover MS SQL database – a restorable and updated backup or an 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