3

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.backup_size and 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 INIT instead of NOINIT produces 4:1 compression.

TDE was only recently enabled, and we saw 1:1 compression for the weekly backups as well until we specified the MAXTRANSFERSIZE value. 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?

Update (2022-04-04)

In experimenting with the development server, I found that the appended backups are properly compressed with SQL Server 2019 CU9 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+.

Tanner Jotblad
  • 131
  • 4

1 Answers1

1

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!

GAURAV RATHOD
  • 186
  • 1
  • 13