SQL 2016 Backup Compression with TDE Enabled Databases

  • Article

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.

  1. It saves disk space.  This seems obvious, but still, it's important when you are architecting your DR strategy and sizing up how much space you will need for your backups.
  2. It saves time.  This is frequently over looked.  The faster you can back up a database, the faster you can restore a database.  This directly impacts your ability to meet Recovery Time Objective (RTO).

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-enabled-databases/?utm\_content=buffer64043\&utm\_medium=social\&utm\_source=facebook.com\&utm\_campaign=buffer

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:

TDEBackupSize TDEBackupTime TDERestoreTime

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.

  • just using table compression will also save you space in the backup files and will save you time on the restores
  • combining page and backup compressions will squeeze out some extra disk space savings and will backup and restore faster
  • in my testing (and I ran the backup and restore multiple times), TDE enabled is faster to backup and restore

Hopefully this gives you something to think about as you are designing your DR and Backup Strategies!

The Links for more reading

TableData 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