TDE And Backup Compression

Erik Darling notes that databases using Transparent Data Encryption now support backup compression:

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.

Check it out, including the table Erik put together.  I’m glad that backup compression is now supported, although I’m kind of curious how they can do that while retaining encrypted backups—are they decrypting data, writing to backup (and compressing), and then encrypting the backup?  That’d be worth checking out with a hex editor.

Related Posts

Auditing Database Backups

Jovan Popovic shows how you can audit who is taking backups on an Azure SQL Managed Instance: One mechanism to ensure that nobody can take the COPY_ONLY backup of your database is to use Transparent Data Encryption that automatically encrypts all backups. In that case you would need to use Customer-managed (BYOK) TDE where you will keep […]

Read More

When xp_logininfo Fails

Gianluca Sartori helps Future Gianluca (and present us in the meantime) troubleshoot issues with xp_logininfo: The user does not existThis is very easy to check: does the user exist in Windows? Did you misspell the name? You can check this from a cmd window, issuing this command: net user SomeUser /domain If you spelled the […]

Read More


July 2016
« Jun Aug »