TDE and Compression

I knew that enabling TDE would cause the information stored within to, effectively, be randomized, which means it would seriously impact the ability for backup compression to work well. It even says this in Books Online:

Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended.

I took that completely at face value and always figured it meant I would only get 5-10% compression or something instead of the higher rates available through backup compression (or the much, much higher rates available through 3rd party compression). Imagine my surprise when I tested this on a 20gb database. Not only did I get no compression, as in zero, but the size of the backup went up slightly.

So, when you go to enable TDE, think about what this means for your backup storage. If you’re counting on compression, you might be in trouble.

One thought on “TDE and Compression

  • Brandon Leach

    This makes sense since Backup compression looks for patterns. When you use TDE you are essentially wiping out the patterns in the data. Add on to that the overhead TDE would include in the backup and I can see how it would create a larger backup.

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.