In my recent post about “Options for smaller backups”, I intentionally omitted backup compression, which I’ll cover in this post. We’ll drill down a bit into the specifics of using TDE and backup compression together.

The history of TDE and backup compression is that until SQL 2016, they were great features that didn’t play well together – if TDE was in play, backup compression didn’t work well, or at all.

However, with the release of SQL 2016, Microsoft aimed to have these two awesome features get along better (the blog post announcing this feature interoperability is here). Then there was this “you need to patch” post, due to edge cases that might cause your backup to not be restored. So if you haven’t patched in a while, now would be a good time to do so, because Microsoft says those issues have been resolved (although that seems to be disputed here).

That “you need to patch” blog post was recently updated to make it (hopefully) crystal clear about the conditions under which database backups use a value for MAXTRANSFERSIZE that is other than the default, thereby optimizing the backup process. To be clear, the following conditions are specific to backups that do not use TDE. Without TDE, the engine will internally change the default MAXTRANSFERSIZE, if:

• your database (not your backup) has >1 file
• you are backing up to URL

BUT – if TDE is enabled for the database you’re backing up – and you don’t supply a value for MAXTRANSFERSIZE, the engine uses a MAXTRANSFERSIZE of 65536 (64K), and the new algorithm for getting good compression with TDE will not be used.

You must supply a value for MAXTRANSFERSIZE of at least 65537 (one byte > 64K) to enable the new compression algorithm when using TDE.

Yeah, it’s sort of hackish, and Microsoft is aware of that, but that’s the way it is for now.

I’ll update this post if/when more information becomes available about the co-existence of these features.