Accelerated Database Recovery in SQL Server 2019: Choose your own filegroup for the version store

An exciting new feature in SQL Server 2019 is Accelerated Database Recovery (ADR). Resulting from a combination of magic beans and smart software developers (I might be wrong about the beans), there is a good chance that you will find yourself using it. Not only can ADR dramatically improve recovery time with database restores, but you will also see a noticeable improvement in rollbacks for long-running transactions as well.

ADR makes use of a per-database version store, instead of putting everything in the transaction log and TempDB. In most cases, the payoff more than makes up for valuable transaction log and TempDB storage. In my testing, enabling ADR requires around 10% additional storage in your database file.

The reason for this post, however, is to mention that SQL Server 2019 CTP 3.2 introduced the ability to choose which filegroup you want to use for the version store, which will help with performance. Leveraging filegroups gives us the opportunity to put the version store on a similar tier as your TempDB and transaction log.

ALTER DATABASE SET ACCELERATED_DATABASE_RECOVERY = { ON | OFF } [ ( VERSION_STORE_FILEGROUP = <filegroup_name> ) ]

Note that if a filegroup is not specified, it will use the default filegroup.

Share your thoughts in the comments below.

Photo by Mathew Schwartz on Unsplash.

One thought on “Accelerated Database Recovery in SQL Server 2019: Choose your own filegroup for the version store”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: