This article is contributed. See the original author and article here.
This blog describes enhancement in SQL 2019 for TDE enabled databases in which MaxTransferSize is no longer required.
Backup Compression and Transparent Data Encryption (TDE) have been two valuable features for customers using SQL Server. In version of SQL Server prior to SQL Server 2016 for TDE enabled databases – specifying WITH COMPRESSION had no effect on backup size.
Starting with SQL Server 2016, 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 SQL Server 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 encrypted databases
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.