First published on MSDN on Sep 27, 2016
The Background
Before SQL Server 2016, businesses had to choose between securing their databases with Transparent Data Encryption (TDE) and database backup compression. While you technically can backup a database that has TDE enabled and choose to compress the backup, the backup doesn't really compress and it takes just as long to back up as choosing to not compress it.
So what's the big deal and why does this matter?
I'm sure that we do not need to talk about TDE and why it is important for some databases.
Backup compression on the other hand is important for two reasons.
SQL 2016 makes things better!
The SQL CAT Team from Microsoft posted an article at the end of June telling us that with SQL 2016, backup compression now works on TDE enabled databases! One thing to be careful of, is that you need to set the MAXTRANSFERSIZE parameter to a number larger than 65536 for the compression to work. Here is a link to the article by the SQL CAT team:
https://blogs.msdn.microsoft.com/sqlcat/2016/06/20/sqlsweet16-episode-1-backup-compression-for-tde-e...
I wanted to see how this new feature worked not just with a database that has TDE enabled, but also when I compress the largest tables with page and row level compression. To test this I used my laptop which has a SSD drive, SQL 2016 Enterprise Edition and a roughly 10 GB database. When I did the restores, I restored them over the original database each time to avoid having to re-allocate the disk space for the database. Here are my results:
The Takeaways
There are a few things that jumped out at me when doing this testing that I had honestly not thought of or tested before.
Hopefully this gives you something to think about as you are designing your DR and Backup Strategies!
The Links for more reading
Table Data Compression - https://msdn.microsoft.com/en-us/library/cc280449.aspx
Database Backup - https://msdn.microsoft.com/en-us/library/ms186865.aspx
Transparent Data Encryption (TDE) - https://msdn.microsoft.com/en-us/library/bb934049.aspx
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.