What is Accelerated Database Recovery
SQL Server 2019 introduced Accelerated Database Recovery (ADR) as a new feature. ADR reduces the time it takes to recover a database from a crash or other failure. It accomplishes this by introducing a new recovery mechanism that greatly reduces the amount of time it takes to roll back transactions that were in progress at the time of the failure.
How Recovery worked traditionally
In traditional SQL Server recovery, a rollback operation can take a long time, especially if there were long-running transactions that need to be rolled back. This is because SQL Server must scan the entire transaction log to find the relevant transactions and then undo the changes made by those transactions.
How Accelerated Database Recovery functions
ADR addresses this issue by maintaining a separate persistent version store in the database that tracks changes made by transactions. When a failure occurs, SQL Server can use this version store to quickly identify and undo the changes made by in-progress transactions, without having to scan the entire transaction log.
By reducing the time it takes to recover a database, ADR can help improve overall database availability and reduce downtime in the event of a failure.
Accelerated Database Recovery (ADR) does not require any changes to the application code to function properly. The implementation of ADR is entirely within the database engine. It does not require any modification to the database schema or application code.
However, it is worth noting that the use of ADR may require changes to the database configuration settings, such as enabling the feature and configuring the version store size. It’s also important to note that not all recovery scenarios can benefit from ADR, so it’s important to understand the limitations and requirements of this feature before implementing it in production environments.
In summary, ADR does not require any changes to the application code, but it may require changes to the database configuration settings and requires careful consideration of the recovery scenarios that can benefit from this feature.
Accelerated Database Recovery (ADR) can benefit in the following recovery scenarios:
- Crash Recovery: When a SQL Server instance crashes, ADR can help to speed up the database recovery process by using the persistent version store to roll back any in-progress transactions. This helps to reduce the time it takes to bring the database back online. So it reduces the downtime and improving database availability.
- Availability Group Failover: ADR can also benefit in the context of SQL Server Availability Groups. It can help to reduce the downtime during a failover scenario. When a primary replica fails and secondary replica takes over. ADR can help to speed up the database recovery process on the secondary replica. ADR does it using the persistent version store to roll back any in-progress transactions.
- Database Restore: ADR can also benefit in the case of a database restore from backup scenario. ADR speeds up the recovery process. It utilizes the persistent version store to apply any changes made since the backup was taken. It eliminates the need to redo all transactions since the backup was taken. This can help to reduce the time it takes to restore the database and bring it back online.
ADR may not be able to help or may not provide any benefits in below scenarios:
- Non-recovery scenarios: It does not provide any benefits in non-recovery scenarios, such as routine database maintenance tasks, backups, or index rebuilds.
- Log Shipping: Log shipping relies on standard database recovery mechanisms to apply transaction logs. It does not help in case of log shipping..
- Database Corruption: ADR may not be able to assist if hardware or software failures cause database corruption. In some cases, database corruption may require a complete database restore or other specialized recovery techniques.
- Insufficient resources: If the system running SQL Server does not have sufficient resources, such as CPU or memory. ADR may not be able to accelerate the recovery process. In fact, ADR may consume additional system resources during the recovery process. This can impact the performance of other processes running on the same system.
Happy Learning !