For many businesses, database performance and availability are crucial elements. A database crash or a lengthy transaction can cause a lot of downtime and have a negative effect on business operations. However, with the introduction of Accelerated Database Recovery (ADR), Microsoft SQL Server has added a potent new feature that greatly enhances database availability and performance. In this post, we’ll explore how ADR works and its benefits for SQL Server users.

Let's first take a look at the traditional SQL Server database recovery process before moving on to ADR. The SQL Server engine uses a three-phase process to restore a database to a stable state after an unexpected restart or crash

1. Analysis - The SQL Server engine reads all transactions in the log file.
2. Redo - Completed transactions are redone.
3. Undo - Incomplete transactions are undone.

Based on this the time it takes the database engine to recover from an unexpected restart is almost proportional to the size of the longest active transaction in the log file at the time of the crash.

Recovery requires a rollback of all incomplete transactions. The length of time required is proportional to the work that the transaction has performed and the time it has been active. Therefore, the SQL Server recovery process can take a long time in the presence of long running transactions. Cancelling, or rolling back, a large transaction can also take a long time as it is using the same undo recovery phase SQL will undo each transaction.

Additionally, when there are lengthy transactions, the database engine cannot truncate the transaction log because the corresponding log records are required for the recovery and rollback procedures. As a result, some transaction logs expand significantly and take up a significant amount of drive space.

Now, let's see Accelerated Database Recovery.

Accelerated Database Recovery (ADR), a potent new feature in SQL Server vastly increases database availability and performance. ADR is introduced in SQL Server 2019 (15.x) and improved in SQL Server 2022 (16.x)., ADR adds new mechanisms to SQL Server's handling of transaction logs, speeding up the process of recovering a database from a crash or lengthy transaction.

IN ADR  process it follows same three steps but each step get divided in sub phases

• Analysis phase
  • Sub phase 1 -     Read the all transactions from log file
  • Sub phase 2 -     Of reconstructing the SLOG (system log stream) and copying log records for non-versioned operations.

• Redo phase
  • Sub phase 1-       Redo from SLOG (oldest uncommitted transaction up to last checkpoint). Redo is a fast operation as it only needs to process a few records from the SLOG.
  • Sub phase 2 -     Redo from transaction log starts from last checkpoint (instead of oldest uncommitted transaction).

• Undo phase - use SLOG to undo non-versioned operations and persisted   version store (PVS) with logical revert to perform row level version-based undo.

By versioning all physical database modifications and only undoing logical operations—which are sparse and can be reversed almost immediately—ADR achieves quick database recovery. Any transactions that were ongoing at the time of a crash are marked as aborted, and concurrent user queries may ignore any versions produced by these transactions.

ADR Achieves Quick Recovery with help of the four key components. These are described below.

Persisted version store (PVS)

Instead of using the conventional tempdb version store, the database engine uses the persisted version store to store the row versions created in the database itself. The PVS makes readable secondaries more readily available and enables resource isolation.

Each instance of SQL Server 2019 has a single PVS thread (15.x) whereas One PVS cleaner thread is available for each database  of SQL Server 2022 (16.x).

Logical Revert

Row-level version-based undo is carried out by the asynchronous process known as logical revert, that also offers instantaneous transaction rollback and undo for all versioned operations.

Logical Revert is achieved by ADR by

• Maintaining a log of all failed transactions.
• Using PVS to roll back all user transactions
• Immediately after a transaction abort, releasing all locks.

SLOG

The log records for non-versioned operations are stored in the SLOG, a secondary in-memory log stream. SLOG accelerates redo and undo by processing only non-versioned operations.

• The SLOG is low volume and in memory
• The SLOG is serialized during the checkpoint process to persist data on disc
• The SLOG is periodically truncated as transactions commit.
• By preserving only the necessary log records, The SLOG enables aggressive transaction log truncation

Cleaner

The cleaner is the asynchronous process that wakes up periodically and cleans page versions that are not needed.

Advantages of Enhanced Database Recovery

ADR offers a number of crucial advantages that significantly raise database availability and performance, including:

• Faster Recovery Times: With ADR, SQL Server can more Quickly recover databases following a crash or a lengthy transaction. SQL Server can roll back only the changes made by the problematic transaction rather than rolling back the entire database by storing multiple versions of the same data in the transaction log.
• Improved Availability: ADR lessens how long-running transactions or system failures affect the accessibility of crucial applications. Applications can resume operation more quickly after a crash, reducing downtime, with faster recovery times.
• Reduced Database Downtime: ADR speeds up the process of recovering a database following a crash or a protracted transaction. In turn, this lessens the overall impact on business operations as critical applications can resume operation more quickly.
• Better Performance: ADR adds a new mechanism for log truncation that enables transaction logs to be pruned more frequently, resulting in a smaller transaction log overall and better database performance.

In conclusion, SQL Server's Accelerated Database Recovery is a strong feature that greatly improves database availability and performance. ADR streamlines and expedites database recovery by introducing new methods for handling transaction logs, leading to shorter recovery times, greater availability, less downtime, and improved performance.