Updated on August 10th, 2023
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.
ADR decreases downtime in SQL Server 2019 and provides the following benefits to SQL administrators:
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.
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 the 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. Within a SQL database, you perform a logical revert with the assistance of the following functions:
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:
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.
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.