Last week, I wrote about the SQL Server Version Store. The week before, I looked at crash recovery. Let’s look next at a new feature in SQL Server 2019, where these two features intersect: Accelerated Database Recovery.
What is it?
Accelerated Database Recovery(ADR) is a new feature intended to speed up the recovery process, which could be very slow, particularly when there are long-running, large transactions. ADR is not just for recovery after a crash, but also helps in other scenarios where the transaction log needs to be recovered–including Availability Group secondary redo and Failover Cluster Instance failovers.
ADR is not enabled by default. You can check to see if it is enabled on any databases with this query:
SELECT [name], is_accelerated_database_recovery_on FROM sys.databases;
If you want to enable ADR on a database, it’s controlled by a database-scoped configuration:
ALTER DATABASE rhotacism SET ACCELERATED_DATABASE_RECOVERY = ON;
As of today, this setting isn’t exposed in any UI in SSMS or Azure Data Studio–but SQL Server 2019 isn’t officially released yet, so it’s certainly possible this will make its way into the UI at some point.
How does it work?
Aaron Bertrand (blog|twitter) recently provided a detailed look into ADR. He does a great job at explaining the internals, and I can’t do a better job. Go read his post.
I mentioned that ADR is the intersection between crash recovery & version store. Let’s look specifically at that aspect of ADR. One of the key changes that makes ADR possible is a persisted version store. This version store is similar to the one described in my last post, except instead of being in tempdb (and being…temporary), it’s persisted within the database itself. By keeping this persistent version store, SQL Server has ready access to the prior versions of the row, allowing it to more quickly “undo” to the prior row, rather than having to reconstruct it from the transaction log. And just like the tempdb version store, there is an asynchronous cleanup process to come through and make sure that unneeded versions are cleaned up to prevent database bloat.
By removing the ephemeral nature of the version store, the persisted version store is guaranteed to be available following a reboot/crash/failover, and also syncs across an AG to the secondary node (everything inside the database is synched across the AG). Recall that RCSI uses the version store to create a consistent committed copy of the data while it is being concurrently updated; ADR uses the persisted version store to create a consistent copy of the data to revert to when rolling back uncommitted changes.
I haven’t yet played much with ADR, but I’m particularly excited to look at how it affects redo on an Availability Group secondary during failover. AG Failovers are already pretty fast–but there is a momentary outage while SQL Server transitions primary from one node to the other, and reconciles transactions by completing redo on committed transactions and undo on uncommitted transactions. ADR should make that failover even faster by reducing the time needed for redo/undo. Simply by enabling ADR, you may be able to improve your uptime by making that recovery faster–just remember to test first.