Written By Pallavi
Updated on February 4th, 2020
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.
ADR decreases downtime in SQL Server 2019 and provides the following benefits to SQL administrators:
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.
It also gets instant rollback of active transactions, irrespective of the time period and number of updates applied to the active transaction.
It prevents the active long-running transactions from growing abnormally by truncating them, forcefully.
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:
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.
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:
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.
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:
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.
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.
About The Author:
Pallavi is a content writer in the field of data recovery and email migration. She has a passion for creating engaging and thought-provoking content. With several years of experience in the field, she has developed a deep understanding of various writing styles and formats,
© Copyrights 2017-2023 Database File Recovery. All Rights Reserved.