Backup Compression and TDE were both released as SQL Server 2008, but they were not work so well together, the backup compression ratio for TDE enabled database was nearly nothing. SQL Server 2016 has a hidden gem that easily be overlooked in the SQL Server BOL BACKUP SQL documentation, saying "Starting with SQL Server 2016 (13.x), setting MAXTRANSFERSIZE larger than 65536 (64 KB) enables an optimized compression algorithm for Transparent Data Encryption (TDE) encrypted databases that first decrypts a page, compresses it, and then encrypts it again. If MAXTRANSFERSIZE is not specified, or if MAXTRANSFERSIZE = 65536 (64 KB) is used, backup compression with TDE encrypted databases directly compresses the encrypted pages, and may not yield good compression ratios.". In this blog post, let's do experiment on backup a TDE enabled database, to compare the differences between the default (no compression), specified WITH COMPRESSION but no max transfer size, and WITH COMPRESSION plus MAXTRANSFERSIZE greater than 64KB.
Here I have a TDE encrypted database, data size 30GB, in a SQL Server 2016 instance.
Backup with default option (uncompressed)
Backup with COMPRESSION option but no max transfer size specified
Backup with COMPRESSION and MAXTRANSFERSIZE greater than 64kb
Good news:
Starting with SQL Server 2019 (15.x) CU5, setting MAXTRANSFERSIZE is no longer required to enable this optimized compression algorithm with TDE. If the backup command is specified WITH COMPRESSION or the backup compression default server configuration is set to 1, MAXTRANSFERSIZE will automatically be increased to 128K to enable the optimized algorithm.
