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
Why should SQL administrators use Accelerated Database Recovery?
ADR reduces downtime in SQL Server 2019 and provides the following benefits to SQL administrators:
- Database Availability:
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.
- Immediate Transaction Rollback:
Performs instant rollback of active transactions, irrespective of the time-period and number of updates applied to the active transaction.
- Forceful Truncation of Transaction Logs:
Prevents the active long running transactions from growing abnormally by truncating them, forcefully.
How does ADR help in reducing downtime in SQL Server 2019?
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:
- Persisted Version Store (PVS) – PVS, the new engine mechanism in SQL database enables resource isolation which helps in enhancing the availability of readable secondaries. It contains previous versions of database as modified by any transaction.
- 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 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
- sLog – sLog, a secondary in-memory log stream is used to store log record 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.
- Cleaner – Performs a periodic clean-up of the redundant page versions.
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:
- Avoids scanning of logs from the beginning of the oldest active transactions.
- 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.
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.
