First published on MSDN on Sep 20, 2017
Backup Compression and Transparent Data Encryption (TDE) have been two immensely valuable and popular features for customers in SQL Server. In SQL Server 2016, backup compression was enabled for TDE-enabled databases, where if you backup a TDE-enabled database with compression and MAXTRANSFERSIZE > 64K, backup compression will kick in, reducing backup size and improving overall backup performance and time. For more details, you can read
blog post
from our SQLCAT team on this improvement.
In past few months, we discovered some edge scenarios related to backup compression for TDE-enabled databases causing backups or restores to fail, hence our recommendations have been:
-
Avoid using striped backups with TDE-enabled databases and backup compression.
-
If a TDE-enabled database has virtual log files (VLFs) larger than 4GB, then do not use backup compression your log backups. If you don't know what a VLF is, start
here
.
-
Avoid using WITH INIT for now when working with TDE-enabled databases and backup compression. Instead, use WITH FORMAT.
-
Avoid using backup checksum with TDE-enabled databases 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. There are certain scenarios listed below where the SQL Server engine chooses to use MAXTRANSFERSIZE > 64K dynamically to optimize for performance:
-
When the database has multiple data files created, it uses MAXTRANSFERSIZE > 64K.
-
When performing backup to URL, the default MAXTRANSFERSIZE = 1MB.
Even when these conditions are met, you must explicitly set the MAXTRANSFERSIZE > 64K in the backup command in order to get the new backup compression algorithm.
Starting with SQL 2016 RTM CU7, SQL 2016 SP1 CU4 and above, we have made improvements and updates to the SQL Server engine which would avoid the edge cases discussed earlier. If you plan to leverage native backup compression for TDE databases or are already using it,
we strongly recommend applying the latest CUs on SQL 2016
to ensure you are not hitting any of the known issues we have discovered earlier.
If you are already using backup compression for TDE databases in your environment on SQL 2016 builds below RTM CU7/SP1 CU4, we strongly recommend validating your backups by restoring them and apply latest CUs proactively to ensure your RPO/RTO requirements are met. If the restore of the backup from SQL Server build below RTM CU7/SP1 CU4 is failing, applying the latest CUs won't allow restore of older backups. Only backups created with the latest CU builds will avoid the above issues, and can be restored without any errors.
It is important to know that VDI support for backup compression on TDE-enabled databases is not added yet, and we plan to add it soon in upcoming servicing releases of SQL Server.
Parikshit Savjani
Senior PM, SQL Server Tiger Team
Twitter
|
LinkedIn
Follow us on Twitter:
@mssqltiger
| Team Blog:
Aka.ms/sqlserverteam
