Internals of backup compression with TDE (SQL Server) 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?
That has made us wonder whether the backup process decrypts the data, compresses it, encrypts that result, then backs up that to disk.


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


  Starting with SQL Server 2016 (13.x), this enables an optimized compression algorithm for TDE encrypted databases that first decrypts a page, compresses it and then encrypts it again. 


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 2017 and your TSQL script uses MAXTRANSFERSIZE&gt; 65536 you will get benefit of backup compression for TDE enabled database. But I strongly suggest you to patch SQL Server 2017 to latest CU