SQL Backups Grown Due to Encryption

Question

SQL Backups Grown Due to Encryption

arrjay

Hall of Fame

Points: 3455
More actions
November 10, 2014 at 4:36 am

#300930

Hi there - I recently enabled TDE encryption on my databases but didn't realise that this meant that backup compression would not work. Now I have backups which are too big to manage. Does anyone have any tips on reducing the backup size. I don't want to progress the Redgate route as this will be additional cost therefore I would prefer to explore SQL native features or windows compression.
Currently my backups are 500GB.
Thanks!
--------------------------------------------
Laughing in the face of contention...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Encrypted data doesn't compress well, if at all. This is because the repeating patterns which compression takes advantage of aren't present once the data is encrypted. I doubt Red Gate's tool will manage much compression either.

Unfortunately, it's encryption or compressed backups, not both.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 2

I'm checking on the technical details of the Red Gate product to see. The issue is that it depends on how the VDI interface sends out pages, and where in the stack this is. If it's just getting raw pages from the MDF/NDF, then these will be encrypted, and you won't get much help. If we get the pages from SQL Server, above the decryption in the read process, then we (Red Gate), write compressed data to the backup file, and then encrypt the compressed files.

SQL Server just grabs pages, which are encrypted. Compression doesn't work, as Gail noted.

arrjay Hall of Fame Points: 3455 More actions · Answer 3

Great, thanks for your help on this.

I spoke to our hosting company and they mentioned that Redgate would not be able to compress the file but it would be good to hear it from the horses mouth!

Thanks again guys!

--------------------------------------------

Laughing in the face of contention...

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 4

I got it back from the proj mgr that we read unencrypted pages, then compress them, then encrypt the result (if you choose to do so).