Does anyone have any (inside?) knowledge of how backup compression works with TDE on SQL Server (version 2016+).
In general I would expect compression of encrypted data to have minimal benefit, however we notice that with TDE, when we do a full back up with compression we still see a significant reduction in the backup file size. That has made us wonder whether the backup process decrypts the data, compresses it, encrypts that result, then backs up that to disk. Obviously that is going to increase the CPU consumption for the backup process due to the need to decrypt and re-encrypt data.
Details
select @@version
Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 17763: ) (Hypervisor)'
TSQL Scipt
BACKUP DATABASE TestTDE TO DISK = 'c:\Test\TestTDE_Compressed.bak' WITH COMPRESSION, MAXTRANSFERSIZE = 65537
Or is the space saving achieved in a different way?
Best Answer
This is exactly how space saving is achieved. When backup process run page is decrypted and loaded into memory, then compressed and then encrypted and saved onto disk. The process is mentioned in Backup TSQL Doc
I am assuming here that since you are getting good mileage with backup compression on TDE database you must be using SQL Server 2016 patched to latest SP and CU. I strongly suggest you to read Backup Compression for TDE Enabled Database
Since you are using
SQL Server 2017and your TSQL script usesMAXTRANSFERSIZE> 65536you will get benefit of backup compression for TDE enabled database. But I strongly suggest you to patch SQL Server 2017 to latest CU