Accelerated Database Recovery

Accelerated Database Recovery (ADR) is poised to be one of the biggest updates to SQL Server in the last few years.  ADR introduces in-line row versioning, which allows long running transactions (such as an offline clustered index rebuild, or a large insert/update/delete statement) to be rolled back immediately.  This will help with a few things that are typically situations for pulling ones hair out:

  • Clicking the cancel button on a query in SSMS – The query will stop running immediately instead of having to run a legacy “rollback” of the statement
  • Similarly, if you run a KILL {SPID} command to kill a runaway query, it will just stop and you won’t continue to have a blocking chain on your server for the next hour
  • AlwaysOn Availability Groups – If you have ever failed over a 2TB database for maintenance, only to realize that the recovery of the old primary database is going to take hours, this is a massive benefit.  The recovery of the new secondary replica (old primary) will be nearly immediate…meaning you can fail back to it as soon as you are done with maintenance instead of hitting refresh over and over on the SQL error logs waiting for that % to go up!

Lastly, and just as important, is Aggressive Log Truncation, which allows even the longest running queries to intermittently checkpoint and truncate the logs, even if they are still running…meaning you won’t blow out your database log file!

Scheduled to ship with SQL Server 2019, ADR is already in preview for Azure SQL workloads and is going to change the way we work!  Check it out below:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-accelerated-database-recovery

By |2018-12-09T19:46:42+00:00December 9th, 2018|Articles, Azure SQL|