Backups

SQL Server 2025 – New Backup Magic

6 Comments

SQL Server 2025 Public Preview is not even a week old, but I’m impressed with another new capability that was released – a new backup compression algorithm: ZSTD. This one came as a surprise, despite being part of Private Preview, as it was only released with Public Preview.

TL;DR – ZSTD Can Be Fast!

Today, I’m only going to do a quick blog post to share some initial test results.

This is against a new database that I’m running on a VMware Workstation VM, on my laptop, on a Samsung 990 Pro. I’m backing up my new RecipesDemoDB, which is 25.13 GB in size.

DISK = NUL (x4 files)Speed
COMPRESSION3057442 pages in 49.966 seconds (478.050 MB/sec)
STD – Level = Low3057442 pages in 44.711 seconds (534.236 MB/sec)
STD – Level = Medium3057442 pages in 33.428 seconds (714.558 MB/sec)
STD – Level = High3057442 pages in 73.147 seconds (326.551 MB/sec)
DISK = 4 Backup FilesSpeedTotal Backup Size% size of original
COMPRESSION3057434 pages in 80.761 seconds (295.764 MB/sec)16.52 GB65.74%
STD – Level = Low3057434 pages in 39.920 seconds (598.351 MB/sec)17.37 GB69.13%
STD – Level = Medium3057434 pages in 56.676 seconds (421.451 MB/sec)15.93 GB63.38%
STD – Level = High3057434 pages in 94.440 seconds (252.924 MB/sec)15.86 GB63.10%

Observations and Thoughts

First, note that there’s now three sub-options which is the compression level. Low is the default. It’s interesting to see that in this initial (SINGLE) test, that STD-Low was fastest but the final output was slightly larger than MSXpress (the legacy compression algorithm).

And a note about data composition… ~3-4GB consists of mostly text data (source) and the remaining ~20-21GB consists of vector embeddings + corresponding text chunks. Because of the nature of vector embeddings, that’ll impact the compressibility. I’ll be releasing this new database very soon with additional supporting documentation.

I’ll be writing much more on this topic later, but wanted to share these initial findings. I find them extremely compelling and am looking forward to testing this with larger databases on higher end hardware.

Thanks for reading!

SQL Server Backups & Mirror To

1 Comment

I love that, while I’ve been working with SQL Server for far too many years, that I’m still constantly learning new things, even about old aspects of SQL Server.

MirrorDirectory / MIRROR TO… Wut?

About a month ago, was having a conversation with a Pure Storage customer’s SQL Server backups. They were using Ola Hallengren’s scripts but mentioned something about writing the backups to multiple locations at once. I presumed they were executing the backup operation as normal, then using another script of some sort to then copy the backup files elsewhere. That’s when I learned that Ola has a parameter called MirrorDirectory, that exposes the MIRROR TO option in a native BACKUP command.

This conversation raised the question of, what is really happening behind the scenes when this parameter is utilized? More specifically, are write I/Os done in parallel, like if you striped your backups across multiple backup files, or are they serial? Or maybe a combination there of, parallel in a backup set, but serial to each different MIRROR TO destination?

Simple Test

Finally got some spare time so I put together a simple test to dig deeper. First, here’s the backup command that I created:

-- Turn on Trace Flags
DBCC TRACEON(3604, 3004, 3014, 3213, -1);
GO

BACKUP DATABASE AutoDealershipDemo
TO
     DISK='I:\AYunBackups\AutoDealershipDemo-1a.bak',
     DISK='I:\AYunBackups\AutoDealershipDemo-1b.bak',
     DISK='I:\AYunBackups\AutoDealershipDemo-1c.bak'
MIRROR TO
     DISK='H:\Backup\AutoDealershipDemo-2a.bak',
     DISK='H:\Backup\AutoDealershipDemo-2b.bak',
     DISK='H:\Backup\AutoDealershipDemo-2c.bak'
MIRROR TO
     DISK='D:\Backup\AutoDealershipDemo-3a.bak',
     DISK='D:\Backup\AutoDealershipDemo-3b.bak',
     DISK='D:\Backup\AutoDealershipDemo-3c.bak'
MIRROR TO
     DISK='V:\Backup\AutoDealershipDemo-4a.bak',
     DISK='V:\Backup\AutoDealershipDemo-4b.bak',
     DISK='V:\Backup\AutoDealershipDemo-4c.bak'
WITH INIT, CHECKSUM, FORMAT, STATS = 3
GO

If you’re wondering about the Trace Flags, I wrote about them here.

Then to monitor I/O, I opted for the simple, brute-force method of using Resource Monitor on the SQL Server itself. So I simply executed the above backup statement, switched windows to the SQL Server, and here’s what I saw:

Simple explanation – all writes to all backup sets and backup files, were occurring simultaneously. Nothing kicked off serially. Neat!

Conclusion

So there you have it folks. MIRROR TO can enable you to write backup files to multiple locations simultaneously. But be cautious that it will take more CPU and you’ll increase the volume of write I/O, meaning you could bottleneck on your storage interconnect. Could be useful, but could become painful as your databases grow.

Thanks for reading!