SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

TDE and Compression

I knew that enabling TDE would cause the information stored within to, effectively, be randomized, which means it would seriously impact the ability for backup compression to work well. It even says this in Books Online:

Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended.

I took that completely at face value and always figured it meant I would only get 5-10% compression or something instead of the higher rates available through backup compression (or the much, much higher rates available through 3rd party compression). Imagine my surprise when I tested this on a 20gb database. Not only did I get no compression, as in zero, but the size of the backup went up slightly.

So, when you go to enable TDE, think about what this means for your backup storage. If you’re counting on compression, you might be in trouble.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...