Normally, when you use Transparent Data Encryption (TDE) to secure your database at rest, you can’t get backup compression. Encrypted data basically looks like random data, and random data doesn’t compress well.
SQL Server 2016 introduced the ability to compress your TDE databases. Yay!
Unfortunately, it has bugs that can leave you unable to restore the backup. Boo!
Even worse: compression can get turned on even if you weren’t aware. Booooo! When a database has multiple files created or you’re backing up to a URL, the compression is enabled automatically, and your restore might not work.
Microsoft now says you need to be on SQL Server 2016 RTM CU7 or SP1 CU4 or higher.
Database administration is hard.
When you install a new version of SQL Server, you get new features – and sometimes, you’re not told about them. For example, when 2016’s TDE compression came out, nobody told you, “If you back up across multiple files, your backups might suddenly be compressed.” You didn’t know that you had a new thing to test – after all, I don’t know a lot of DBAs who have the time to test that the new version of SQL Server successfully performs restores. They restore their production databases into the new version, test a few things, and declare victory – but testing restores FROM the new version’s backups isn’t usually on that list.
Then after you go live, hopefully you start testing your restores, implementing log shipping, and refreshing your dev boxes. That’s when you suddenly discover that your backups aren’t.
Change equals risk: every time you put new software into your environment, you’re gambling that it’s going to improve more than it breaks, and that you can fix the things it breaks. That doesn’t mean you shouldn’t ever change – it brings rewards, too – but just don’t go in naively thinking every piece of software is bug-free, and you need to keep up with all of the different Microsoft SQL Server team blogs. I wish that in 2017, there was a better way to get urgent SQL Server news from Microsoft – but until then, I’ll keep recapping to get the word out about urgent stuff like this. You’re welcome. You can buy me drinks at Summit.
3 Comments. Leave new
Lovely… Our URAC auditor said having the database files and backups on encrypted drives wasn’t enough and insisted we turn on TDE in our 2016 production environment after we migrate. I thought life was going to get easier…
Surely the very essence of the database is the ability to restore successfully. Come On Microsoft get your game together.
I have just made the case for my client to move to SQL 2016 and we will use TDE ,Yikes
Looks like people are still having problems on those versions and later:
https://matthewmcgiffen.com/