2

I've been reading about this for a long time, and it seems it's not safe to compress a backup when the database has TDE enabled.

is anyone experiencing errors during restore with compressed backups for tde databases?

I've never experienced these kind of errors but i'm really worried about it.

It seems the problem is with SQL SERVER 2016, and as I'm using 2012, I can't see any problems.

Also, even using MAXTRANSFERSIZE = 131072 the backup is not compressed. what could It be? I'm using NO CHECKSUM and WITH CHECKSUM both with the same size.

2
  • 1
    Never had problems with it failing to restore but that is because I wouldn't normally do it as the compression of a database with TDE is so poor. (improved in later versions ?) Feb 17, 2021 at 12:20
  • Could you post EXACTLY which errors you're encountering so that we can help you troubleshoot them?
    – Nzall
    Feb 18, 2021 at 10:30

2 Answers 2

6

I've been reading about this for a long time, and it seems it's not safe to compress a backup when the database has TDE enabled.

You asked about safety so replying. Using backup compression with TDE is safe and I have been using it quite a lot. What doesn't works well, for version SQL Server 2014 and below is the backup compression mileage (the amount of compression) you would get for versions below SQL Server 2016 is not great, let me put that the backups are slightly compressed as compared to backup compression on non TDE enabled database. From the Docs which Andrew Sayer shared (Backup compression with TDE

Starting from SQL Server 2016

Starting with SQL Server 2016 (13.x), setting MAXTRANSFERSIZE larger than 65536 (64 KB) enables an optimized compression algorithm for Transparent Data Encryption (TDE) encrypted databases that first decrypts a page, compresses it, and then encrypts it again. If MAXTRANSFERSIZE is not specified, or if MAXTRANSFERSIZE = 65536 (64 KB) is used, backup compression with TDE encrypted databases directly compresses the encrypted pages, and may not yield good compression ratios.

Starting from SQL Server 2019

Starting with SQL Server 2019 (15.x) CU5, setting MAXTRANSFERSIZE is no longer required to enable this optimized compression algorithm with TDE. If the backup command is specified WITH COMPRESSION or the backup compression default server configuration is set to 1, MAXTRANSFERSIZE will automatically be increased to 128K to enable the optimized algorithm

is anyone experiencing errors during restore with compressed backups for tde databases?

Show me the error, you must be restoring TDE enabled database without certificates. My best guess

2
  • Oh no I'm not trying to restore. I just see some posts here about corrupt databases that used TDE + checksum + compression. I was worried about that. but thants for the answer.
    – Racer SQL
    Feb 18, 2021 at 11:33
  • Can you please guide me to the posts, let me check and see what caused the corruption. It could easily be bad hardware or may be a bug.
    – Shanky
    Feb 18, 2021 at 12:12
3

You need to be on at least SQL Server 2016 for backups of TDE databases to be compressed properly, and even then you need to use the non-default setting of maxtransfersize. This restriction was fixed in CU 5 of SQL Server 2019.

See the docs https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server?view=sql-server-ver15#backup-compression-with-tde

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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