smartlat.blogg.se - Sql server vdi and compression tde

Starting SQL 2016 RTM CU7, SQL 2016 SP1 CU4 and above, improvements and updates to the SQL Server engine avoid these edge cases.

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.

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.

That means compression for TDE databases can kick in automatically even when maxtransfersize is not explicitly set.

However, there are c scenarios (listed below) where the SQL server engine chooses to use MAXTRANSFERSIZE > 64K dynamically to optimize for performance. 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.

Avoid using backup checksum with TDE and backup compression.Avoid using WITH INIT when working 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 striped backups with TDE and backup compression.Recently, Microsoft found some edge scenarios related to backup compression for TDE databases caused backups or restores to fail. 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. In SQL Server 2016, backup compression was enabled for TDE databases. Backup Compression and Transparent Data Encryption (TDE) have been immensely valuable and popular features in SQL Server.