Accelerated Database Recovery

The described recovery process ensures that every database is in its last known consistent state after SQL Server's startup. The recovery process could take a long time in some cases. A common situation would be when SQL Server is stopped (sometimes unexpectedly) when some long-running transaction is being executed. The recovery process takes almost the same time as executing the transaction. It leads to unacceptable database unavailability. SQL Server 2019 brings a new database-scoped feature that bypasses this issue. The feature is called Accelerated Database Recovery (ADR). ADR basically keeps track of changes in data using internal row versioning. When SQL Server stops working and is restarted, SQL Server does not recover all the transactions from the transaction log, but simply recovers the proper versions of the records from the in-database row version store.

To turn on ADR, we can use the following Data Definition Language (DDL) statement:

ALTER DATABASE AdventureWorks SET ACCELERATED_DATABASE_RECOVERY = ON

(PERSISTENT_VERSION_STORE_FILEGROUP = myPvsFG)

The preceding statement consists of two parts. The first part is just turning ON (or OFF, if needed) ADR. The second part of the statement, enclosed in brackets, is optional. Versions of records that have been changed during transactions are stored on a disk in a filegroup. We can set a filegroup dedicated to row versions (which is a good practice for performance). Row versions are stored in the filegroup called myPvsFG. When this part of the configuration is omitted, row versions are stored in the primary filegroup.

The ADR feature is useful for workloads with long-running transactions or when the transaction log of a certain database grows significantly.

It is important for DBAs to understand write-ahead logging when planning a backup strategy because the restore process finishes with the recovery process as well. When restoring the database, the administrator has to recognize if it's time to run the recovery process or not. Now, let's learn about the different backup options that are available in SQL Server by using a properly configured recovery model.