Troubleshooting Backup Compression for Encrypted Databases

Andy Levy
sqlserver
Page content

For years, I thought that native backups of databases using Transparent Data Encryption (TDE) couldn’t be compressed. Between TDE being limited to Enterprise Edition until SQL Server 2019 and my own lack of experience with TDE in prior positions, I hadn’t really experimented with this myself. Some people have even gone so far as to skip compression in their backup jobs for TDE-enabled databases because there’s no need to burn those CPU cycles if you won’t get any compression, right?

But a curious thing happened after I upgraded a portion of my environment to SQL Server 2019 in late 2020. I observed that scheduled backups were compressing for some of my TDE-enabled databases, most notably the newer instances. And when I took ad hoc backups in any environment, they were compressed. So why wasn’t it working everywhere?

  • Was it because Default Backup Compression was switched off? Nope, that’s part of my standard setup/best practices checklist.
  • Was it because compression was explicitly disabled in my Agent Jobs performing the backups? Nope, I didn’t set it one way or the other (so it takes the instance default).
  • Was it a difference between Standard and Developer editions? Not that I saw in the documentation.

It turns out that in SQL Server 2016, Microsoft did start compressing backups for TDE-enabled databases, but only if the MAXTRANSFERSIZE for your backup is a value greater than 64 KB.

Default, But Not Default

Stick with me here. The default MAXTRANSFERSIZE is 1MB and last I checked, that’s greater than 64KB. So you might assume that compression would work by default given the above. That’s not the case. You have to explicitly set MAXTRANSFERSIZE to a value greater than 64KB, even if that value is less than 1MB, to get compression.

Edited on 2021-06-21 thanks to a note from Kevin Feasel: Per the documentation, MAXTRANSFERSIZE is not 1MB when backing up a TDE-protected database to disk, it’s 64K.

In SQL Server 2019 CU5 this requirement was dropped. SQL Server is now smart enough to take care it for you automatically if your MAXTRANSFERSIZE is not specified.

OK, great. I’m running SQL Server 2019 CU5 (or newer), I’m not specifying a MAXTRANSFERSIZE, so why am I not getting compression on my TDE-enabled databases when I run my backup jobs? I use Ola Hallengren’s Maintenance Solution for my backups, so I ran DatabaseBackup with EXECUTE = 'N' to see the BACKUP DATABASE statement was executed. Aha! NO_COMPRESSION was explicitly being set in the statement. But why?

I went over to Github and started poring over the source code to figure out where and why it was making that decision. Something didn’t add up. Finding the relevant section of code, I saw the checks for MAXTRANSFERSIZE, I saw the checks for the SQL Server version, I found all the logic that decided whether it should use COMPRESSION or NO_COMPRESSION. And this works fine on most of my instances, what’s going on here?

Revelation & Resolution

Then it hit me. The instance that wasn’t compressing backups was my oldest one. I installed Ola’s scripts on some of these instances a year ago, well before this update to SQL Server 2019 was released, and carried the same versions forward when I upgraded the instances.

Resolving the situation was easy enough. I installed the latest versions of Ola’s stored procedures on my outdated instances, and everything worked great!

The Takeaway

As DBAs, staying current with updates to our operating systems and SQL Server is just part of the job. But that’s not enough. Make sure you have an inventory of and update schedule for your all of your tools and components.