If you plan to leverage native backup compression for TDE databases or are already using it, 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. When performing backup to URL, the default MAXTRANSFERSIZE = 1MB.Įven 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. When the database has multiple data files created, it uses MAXTRANSFERSIZE > 64K. There are certain scenarios listed below where the SQL Server engine chooses to use MAXTRANSFERSIZE > 64K dynamically to optimize for performance: 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. Instead, use WITH FORMAT.Īvoid using backup checksum with TDE-enabled databases and backup compression. If you don't know what a VLF is, startĪvoid using WITH INIT for now when working 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. 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:Īvoid using striped backups with TDE-enabled databases and backup compression. For more details, you can readįrom our SQLCAT team on this improvement. 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. Backup Compression and Transparent Data Encryption (TDE) have been two immensely valuable and popular features for customers in SQL Server.
0 kommentar(er)
