none
Data Compression & TDE RRS feed

  • Question

  • Hi ,

    lately i enabled TDE on my databases and noticed as was expecting the backup comprission automaticaly became unuseful , and i must invest more in storage to statisfy my retnetion period for backup copies..

    my question is , is it applicable to think in data Compression as an alternative to back to old size of backup before enable encryption?

    i have SQL 2008 ent x64

    Wednesday, September 12, 2012 9:09 AM

Answers

  • My suggestion would be to acquire more storage for backups.  Encrypted data does not compress - in fact, the ability to compress encrypted data is one of the quickest checks for something that might be real encryption: if it compresses, it's likely not a high quality encryption.

    If you want data that is both compressed and encrypted, then it must be compressed first - which is exactly what happens with row and page level compression.


    -cd Mark the best replies as answers!

    Sunday, September 16, 2012 6:03 PM

All replies

  • Hi,

    That is expected. With TDE enabled, the backup compression may not really help. See SQLCAT's analysis http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx

     Databases using the Transparent Database Encryption (TDE) feature of SQL Server 2008 may not see as high backup compression ratios (in most cases, it will be close to 1) due to the fact that encrypted data does not lend itself well to compression.


    This posting is provided “AS IS” with no warranties, and confers no rights.

    If this reply answers your question, please mark it as Answered for others to find it easily.
    If this reply help you resolving the problem, please vote the post as Helpful.

    • Proposed as answer by Ramesh Babu Vavilla Wednesday, September 12, 2012 10:08 AM
    • Unproposed as answer by SQL Kitchen Wednesday, September 12, 2012 11:14 AM
    Wednesday, September 12, 2012 9:23 AM
  • the same goes with data comprission (Row & Page) ?

    Thanks

    Wednesday, September 12, 2012 9:50 AM
  • the same goes with data comprission (Row & Page) ?

    Thanks

    Yes Sir.

    This posting is provided “AS IS” with no warranties, and confers no rights.

    If this reply answers your question, please mark it as Answered for others to find it easily.
    If this reply help you resolving the problem, please vote the post as Helpful.

    Wednesday, September 12, 2012 9:51 AM
  • but as i saw here http://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

    the effectiveness and efficiency of data compression is not impacted by TDE?? what do you think??

    Wednesday, September 12, 2012 10:01 AM
  • but as i saw here http://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

    the effectiveness and efficiency of data compression is not impacted by TDE?? what do you think??

    Is it talking about backups? I mean does it say the backups would still be compresses as if it were an un-encrypted data?


    This posting is provided “AS IS” with no warranties, and confers no rights.

    If this reply answers your question, please mark it as Answered for others to find it easily.
    If this reply help you resolving the problem, please vote the post as Helpful.

    • Proposed as answer by Ramesh Babu Vavilla Wednesday, September 12, 2012 10:08 AM
    • Unproposed as answer by SQL Kitchen Wednesday, September 12, 2012 11:08 AM
    Wednesday, September 12, 2012 10:04 AM
  • it seems i wasn't clear enough :) sorry

    i meant what if i used data comprission eithr row or page insted of backup comprison to reduce the size of database backup ..

    Wednesday, September 12, 2012 10:22 AM
  • it seems i wasn't clear enough :) sorry

    i meant what if i used data comprission eithr row or page insted of backup comprison to reduce the size of database backup ..

    You mean No TDE. Just the data compression and then backup ? Oh yes, then data compression would help you reduce the overall database size and now that the database size is reduced backup would certainly be of lesser size. But there are considerations of using data compression (row or page). See http://msdn.microsoft.com/en-us/library/cc280449.aspx

     

    This posting is provided “AS IS” with no warranties, and confers no rights.

    If this reply answers your question, please mark it as Answered for others to find it easily.
    If this reply help you resolving the problem, please vote the post as Helpful.

    Wednesday, September 12, 2012 10:52 AM
  • no with TDE enable ..

    simply if i used data comprission after enabled TDE .. is that would help in reduce the size of database backup..


    • Edited by SQL Kitchen Wednesday, September 12, 2012 11:15 AM
    Wednesday, September 12, 2012 11:06 AM
  • Page or row compression will not have an impact on backup size, regardless of whether TDE is enabled or not.

    TDE renders backup compression worthless (usually), but page and row level compression do work normally when TDE is enabled. Data is not encrypted while it is in-memory, and if page or row level compression is on and TDE is on, SQL Server first compresses the data that will be stored in a single 8K data page, then it encrypts that page and flushes it to disk.

    Thursday, September 13, 2012 4:34 PM
  • Thanks for your reply

    actually i am looking for an alternative of backup comprission after TDE made it usless , becouse we have daily full backup 1T of data

    pleasse if yo have any suggestion your most welecomed

    Sunday, September 16, 2012 11:55 AM
  • My suggestion would be to acquire more storage for backups.  Encrypted data does not compress - in fact, the ability to compress encrypted data is one of the quickest checks for something that might be real encryption: if it compresses, it's likely not a high quality encryption.

    If you want data that is both compressed and encrypted, then it must be compressed first - which is exactly what happens with row and page level compression.


    -cd Mark the best replies as answers!

    Sunday, September 16, 2012 6:03 PM