Backup Compression and Transparent Data Encryption (TDE) have been immensely valuable and popular features in SQL Server.
In SQL Server 2016, backup compression was enabled for TDE databases. When you backup a TDE enabled database with compression and MAXTRANSFERSIZE > 64K, backup compression will kick in to reduce backup size and to improve overall backup performance and time.
Recently, Microsoft found some edge scenarios related to backup compression for TDE databases caused backups or restores to fail.
Hence our recommendations have been
• Avoid using striped backups with TDE and backup compression.
• If your database has virtual log files (VLFs) larger than 4GB, then do not use backup compression with TDE for your log backups.
• Avoid using WITH INIT when working with TDE and backup compression. Instead, use WITH FORMAT.
• Avoid using backup checksum with TDE and backup compression
Note: The default native backup uses MAXTRANSFERSIZE = 64K when the database has a single database file, so compression doesn’t kick in automatically for TDE enabled databases and the above issues aren’t encountered.
However, there are c scenarios (listed below) where the SQL server engine chooses to use MAXTRANSFERSIZE > 64K dynamically to optimize for performance. That means compression for TDE databases can kick in automatically even when maxtransfersize is not explicitly set.
• When the database has multiple data files created, it uses MAXTRANSFERSIZE > 64K for which the backup compression kicks in automatically if compression is specified and database has TDE enabled.
• When performing backup to url, the default MAXTRANSFERSIZE = 1MB for which the backup compression kicks in automatically if compression is specified and database has TDE enabled.
Starting SQL 2016 RTM CU7, SQL 2016 SP1 CU4 and above, improvements and updates to the SQL Server engine avoid these edge cases . If you plan to leverage native backup compression for TDE databases or are already using it, we strongly recommend you apply the latest CUs on SQL 2016 to ensure you are not hitting any of the known issues .
If you are already using backup compression for TDE databases in your environment on SQL 2016 builds below RTMCU7/SP1CU4, then we strongly recommend you validate your backups by a test restore and also apply the latest CUs proactively to ensure your RPO/RTO requirements are met.
If the restore of the backup from SQL Server build below RTMCU7/SP1CU4 is failing, then applying the latest CUs won’t allow restore of older backups.
Only backups created from the latest CU build will avoid the above issues and can be restored without any errors.
VDI support for backup compression on TDE enabled databases is not added yet and Microsoft plans to add it soon in an upcoming service releases of SQL Server.
