Recovers lost or deleted Office documents, emails, presentations & multimedia files.
Recovers deleted files, photos, videos etc. on Mac.
Recover photos, videos, & audio files from all cameras and storage on Windows or Mac.
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 the crash. In such a case, if the ongoing transaction is too long then the database downtime is also prolonged. Such a 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
ADR reduces downtime in SQL Server 2019 and provides the following benefits to SQL administrators:
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.
Performs instant rollback of active transactions, irrespective of the time-period and number of updates applied to the active transaction.
Prevents the active long running transactions from growing abnormally by truncating them, forcefully.
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:
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.
A comparative analysis of ADR and ARIES three-phase processes:
Recovery or Transaction Log with ADR Redesigned database engine recovery process | Recovery or Transaction Log with ARIES ARIES database recovery model |
Analysis phase: In addition to ARIES recovery model, ADR also performs the following functions: 1. Reconstructs sLog, where slog is the oldest uncommitted transaction up to last checkpoint. 2. Copies log records for non-versioned operations. | Analysis phase: SQL server determines the state of each transaction at the time SQL server stopped. It performs forward scanning of transaction log from the beginning of the last successful checkpoint (also named as oldest dirty page LSN). |
Redo phase: • Phase 1 Redo from sLog. Redo is faster as it processes only a few records from sLog. • Phase 2 Redo from Transaction Log starts from last checkpoint (instead of oldest uncommitted transaction). | Redo phase: SQL server performs following functions: • Bring the database to the state it was during the time of crash by redoing all committed transactions. • Performs forward scanning of the oldest uncommitted transaction till the end. |
Undo phase: The Undo phase with ADR uses sLog to undo non-versioned operations and Persisted Version Store with Logical Revert. It performs row level version-based Undo. Process of Undo is completed in no time. The three-step process of ADR is explained with the help of the following figure: | Undo phase: During the time of crash, there are some active transactions. SQL server traverses back for each active transaction to undo the operation performed during that transaction. The three-step process of ARIES is explained with the help of the following figure: |
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:
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.
Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.