8

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?

| improve this question | |
  • Here's a write up on this. Seems like for 2016 onward it would work.It doesn't explain the latter part of your question, though I would guess that it is not decrypted, compressed, and encrypted again. When you restore the backup, this would have to happen again instead of simply restoring the encrypted data. Here is one on the performance – scsimon Oct 23 '19 at 13:32
10

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> 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

| improve this answer | |
  • 1
    Thanks, that answer and the ref to the docs in the first link was exactly what I was looking for. – Matthew McGiffen Oct 23 '19 at 13:50
  • BTW, the versions etc were just a test DB locally – Matthew McGiffen Oct 23 '19 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.