TDE Backups Won't Compress with NOINIT
We run weekly backups of a TDE-enabled database using a command in the following format:
BACKUP DATABASE [DBName] TO DISK = N'C:\Temp\DBName.bak' WITH FORMAT, INIT, MEDIANAME = N'DBName Backup', NAME = N'DBName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, MAXTRANSFERSIZE = 1048576, STATS = 10
We then run daily backups the rest of the week using a command in this format:
BACKUP DATABASE [DBName] TO DISK = N'C:\Temp\DBName.bak' WITH NOFORMAT, NOINIT, NAME = N'DBName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, MAXTRANSFERSIZE = 1048576, STATS = 10
The weekly backups are compressed at a ratio of about 4:1 (e.g., 33,109,313,536 vs. 8,093,794,304 bytes according to
msdb.backupset.compressed_backup_size). The daily backups are compressed basically 1:1 (e.g., 33,100,596,224 vs. 33,048,332,345 bytes). Running the daily backup command with
NOINITproduces 4:1 compression.
TDE was only recently enabled, and we saw 1:1 compression for the weekly backups as well until https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server#backup-compression-with-tde . We're seeing the same behavior on a production 2017 server (14.0.3370.1) and a development 2019 server (15.0.2080.9). Any ideas as to why compression is working for overwrites but not for appends?
In experimenting with the development server, I found that the appended backups are properly compressed with https://support.microsoft.com/en-us/topic/kb5000642-cumulative-update-9-for-sql-server-2019-97ad5c3e-e002-4b6d-b566-698bf70ca44a and higher. There is nothing in the release notes that specifically references this issue, so I'm not sure if I should assume it's a bug. Since we would like to stay on GDR versions, I would still be interested in finding a workaround without having to update to CU9+.
One of compromises you make by enabling TDE is with size of compressed backups. If you use TDE, you won't get much out of backup compression feature because all pages will be having unique data now due to encryption algorithm and as it is hard to compress unique data - you are getting 1:1 compression ratio after you enabled TDE, which is expected too.
The reason why you still see 4:1 compression with INIT (append) is probably because old backup file was of size x MB, and since DB was recently encrypted - it does not have that much amount of new data (let's assume you have got y MB of data after TDE), so you get backup file size should be x (with 4:1 ratio) + y (with 1:1 ratio).
However, if you use NOINIT - SQL Server will overwrite all data on backup media file - so your previously compressed data (with 4:1 ratio) gets overwritten too. And you get 1:1.
Hope this helps!