Over the years Microsoft developed several processes that work together to ensure the durability of data. Data durability, in this case, means ensuring that in case of a system failure, all completed transactions are retained and all incomplete transactions are rolled back. This ensures that committed data is not lost during system failure. These processes have been in use for quite a long time and play a critical role in SQL Server.
In October of 2018, Microsoft announced Accelerated Database Recovery (ADR) and it was one of the most interesting topics of that year’s PASS Summit. ADR is a redesign of the SQL engine’s recovery process. It represents a big change from the long-established model and represents a big boost to recovery time.
ADR Benefits
Due to the changes in logging, only certain operations require log space. This leads to efficient log usage and aggressive truncation. This means reduced log growth and better manageability in addition to fast recovery. Systems that have very long running transactions, high log growth and long recovery will benefit greatly from ADR.
Current Recovery Process – A Primer
Before looking into the updated recovery process, its worth taking a quick look at how the current three-step recovery process works.
When SQL Server starts up, the following steps are performed before a database is available:
Step 1 – Analysis
SQL Server reads the database transaction log from the latest checkpoint to the end of the log. This establishes potential dirty pages (pages that had been modified in memory but not persisted to disk).
Step 2 – Redo
SQL Server then begins replaying the log from the oldest uncommitted transaction to the end of the of the log, redoing all transactions up to the point of the crash. This brings the database to the point of the crash.
Step 3 – Undo
The final step of recovery, SQL Server traverses the log backwards, from the end to the oldest uncommitted transaction, undoing any transactions that were still active during the crash.
While this process has served SQL Server well for years, it is not without its drawbacks. Because transactions are replayed, the recovery time is relative to the largest transaction. If the database is subject to large, long running transactions, recovery time can be equally as long.
Recovery re-engineered – The ADR Process
At its core, ADR still uses the three-step recovery process. The new mechanisms ADR introduces substantially improve crash recovery and startup, as well as transaction rollback and transaction log management.
The new mechanisms and process are described below:
Persistent Version Store (PVS)
In ADR, SQL Server stores row versions. In isolation levels that leverage versioning, these are stored in tempdb. In ADR, these versions are stored in the database itself. Although this can increase database size, this is an enhancement that allows versioning in readable secondaries.
Logical Revert
This is the mechanism for handling the undo section of recovery from the PVS.
Slog
Slog is an in-memory secondary log stream. Slog only contains non-versioned operations such as lock acquisitions, metadata cache invalidation, etc. The number of transactions stored in slog is very small compared to the normal transaction log. It is persisted to disk during checkpoint process. The slog is key to ADR.
Cleaner
The cleaner process is responsible for removing unneeded row versions.
ADR in Action
So what does this mean in practice? In short, the new process is very similar but a bit busier than traditional recovery. Here is the new ADR Process:
Step 1 – Analysis
This is identical to standard recovery, except the slog is rebuilt during the process.
Step 2 – Redo
Redo from the oldest uncommitted transaction only has to redo the slog. Because these are considerably fewer in number, this process is very fast. Traditional redo then resumes from the latest checkpoint.
Step 3 – Undo
The undo process is also greatly improved. Because slog is in memory, undoing those are very fast. Because rows are stored in the PVS, Logical Revert can quickly undo any traditional transactions.
ADR in Action
Microsoft had a great application example during Pass Summit. Check out the video here:
Lastly, if your organization is looking to learn more about SQL database recovery options, please reach out to us. We’d love to help you out.
Share on
