March 18, 2010 at 8:38 am
I've been using TDE for a little over a year now, and one of the things that I read all the time and in multiple different articles is that TDE and backup compression are not compatible.
I would like to say that, the premise is flat out wrong.
I have many server running TDE, and databases ranging from MB to 100's of GB. And I use backup compression on all of the backups I run.
One of the databases that I have in my Dev Environment is an 18 GB database, nice middle of the road size. Without backup compression enabled the backups of this database are a little over 10 GB. With backup compression enabled the backups are around 3.2 GB.
I have found this to be the case across the board.
I have also used these backups to perform restores on other UAT environments, or back down to dev environments.
I came across several articles today saying that Backup Compression is not compatible. But saving around 7 GB of space per backup is a big deal, especially when storage is a premium. Or if you have plenty of storage space this could allow you to expand the number of backups you have on hand.
Either way this gets back to the old adage, take advice but test for yourself. I encourage you all to do the same, and if you get different results please let me know.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
March 18, 2010 at 9:16 am
Whoever is saying they're incompatible simply hasn't read the relevant data in BOL.
Encrypted data compresses significantly less than equivalent unencrypted data. If transparent data encryption is used to encrypt an entire database, compressing backups might not reduce their size by much, if at all.
They're compatible, but encrypted data often doesn't compress well.
The incompatibility is NTFS encryption/compression. Can't have both on the same file/directory/partition. Nothing to do with SQL Server, at least not directly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply