Note: THERE ARE BUGS IN THIS FEATURE. Make sure you read all the way through the post to catch the updates from Microsoft as more bugs were found.
TDE is one of those things!
You either need it, and quickly learn how many things it plays the devil with, or you don’t need it, and there but for the grace of God go you. Off you go, with your compressed backups, your instant file initialization, your simple restore processes. Sod off, junior.
But Microsoft maybe listened or figured out something by accident. I don’t know which one yet, but they seem excited about it! And I am too! If you read this blog post that’s probably also being monitored closely by Chris Hansen, you’ll see why.
Backup compression now works with TDE
Cool! Great! Everyone encrypt your data and compress your backups. It’s fun. I promise.
Not satisfied with a few meek and meager data points, I set out to see if increasing Max Transfer Size also increased the degree of compression. Why? This paragraph.
It is important to know that while backing up a TDE-enable database, the compression will kick in ONLY if MAXTRANSFERSIZE is specified in the BACKUP command. Moreover, the value of MAXTRANSFERSIZE must be greater than 65536 (64 KB). The minimum value of the MAXTRANSFERSIZE parameter is 65536, and if you specify MAXTRANSFERSIZE = 65536 in the BACKUP command, then compression will not kick in. It must be “greater than” 65536. In fact, 65537 will do just good. It is recommended that you determine your optimum MAXTRANSFERSIZE through testing, based on your workload and storage subsystem. The default value of MAXTRANSFERSIZE for most devices is 1 MB, however, if you rely on the default, and skip specifying MAXTRANSFERSIZE explicitly in your BACKUP command, compression will be skipped.
It left things open ended for me. Unfortunately for me, it doesn’t help. Fortunately for you, you don’t have to wonder about it.
Check out the exxxtra large screen cap below, and we’ll talk about a few points.
First, the database without a Max Transfer Size at the bottom was a full backup I took with compression, before applying TDE. It took a little longer because I actually backed it up to disk. All of the looped backups I took after TDE was enabled, and Max Transfer Size was set, were backed up to NUL. This was going to take long enough to process without backing up to Hyper-V VM disks and blah blah blah.
The second backup up, just like the blog man said, no compression happens when you specify 65536 as the Max Transfer Size.
You can see pretty well that the difference between compressed backup sizes with and without TDE is negligible.
The most interesting part to me was the plateau in how long each backup took after a certain Max Transfer Size. Right around the 1MB mark, it hits the low 380s, and never strays very far from there afterwards. I could have tried other stuff to make this go faster, but my main interest was testing compression levels.
There you have it
Max Transfer Size doesn’t impact compression levels, but it can help duration. If you want to keep playing with switches, you can throw in Buffer Count, and try striping backups across multiple files to ‘parallelize’ output.
Thanks for reading!
Update (2017/06/16): Bugs!
One of our readers (who’s sharper than us!) noticed that the Microsoft blog post to introduce the new compatibility between TDE and backup compression had an update to it.
Apparently, some combinations of backup options will cause your backups to become corrupted.
Update April 6th, 2017
We have recently discovered some issues related to the use of TDE and backup compression in SQL Server 2016. While we fix them, here are some tips to help you avoid running into those known issues:
- Currently it is not advisable to use striped backups with TDE and backup compression
- If your database has virtual log files (VLFs) larger than 4GB then do not use backup compression with TDE for your log backups. If you don’t know what a VLF is, start here.
- Avoid using WITH INIT for now when working with TDE and backup compression. Instead, for now you can use WITH FORMAT.
SQL engineering is working on fixes for these issues in SQL Server 2016. We will update this blog post once again once we have further information to share.
So uh, be careful of all that if you’re on 2016 CU5, or 2016 SP1 CU2.
There’s a fix for this in 2016 CU5, and 2016 SP1 CU3.
Update (2017/09/21): More Bugs & Fixes
There are some scenarios where even the above list of recommendations doesn’t work. The SQL Server Tiger Team explains, and points out that you now need to be on at least 2016 CU7 or SP1 CU4.