Backup Compression, TDE, and MaxTransferSize

This blog describes enhancement in SQL 2019 for TDE enabled databases in which MaxTransferSize is no longer required.

Backup Compression and Transparent Data (TDE) have been two valuable features for customers using . In version of prior to SQL for TDE enabled databases – specifying WITH COMPRESSION had no effect on backup size.

Starting with SQL , backup compression was enabled for TDE-enabled databases provided MAXTRANSFERSIZE > 64KB is specified along with compression. This in turn causes backup compression to kick in and improve overall backup performance by reducing the size and time it takes to complete the backup. For more details, you can read a blog post from our SQLCAT team on this improvement related to TDE and backup compression.

Starting with 2019 CU5, setting MAXTRANSFERSIZE larger than 64KB is no longer required to enable the optimized compression algorithm for TDE enabled databases. In other words, if a backup command is specified WITH COMPRESSION or the backup compression default server configuration is set to 1, MAXTRANSFERSIZE will automatically be increased to 128 KB to enable optimum compression of TDE enabled database backup.

If an existing backup command is already making use of MAXTRANSFERSIZE > 64K along with compression, the provided value of MAXTRANSFERSIZE will be honored and backup compression for the TDE enabled database will continue to work.

For more info refer to:

Improvement: MAXTRANSFERSIZE no longer required to enable backup compression on TDE databases…


This article was originally published by Microsoft's Azure Security Blog. You can find the original article here.