SQL2008 TDE and SQLBackup Compression

AdamMetcalfeAdamMetcalfe Posts: 11
edited July 21, 2011 1:40AM in SQL Backup Previous Versions
A really simple question, but i think i already know the answer.

How well does SQLBackups compression work with a TDE enabled database. Some real world examples would be great.

My current understanding is:

You can backup and compress a db then encrypt the backup
You can encrypt a db but not effectively compress a database.

thanks!
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    How well does SQLBackups compression work with a TDE enabled database.
    Not at all well.
    Some real world examples would be great.
    Your own databases would be a great example. If you don't want to install SQL Backup, just take a native backup of your database that has TDE enabled, and compress it using Winzip or something similar. The compression ratio would be somewhere near SQL Backup's own results. Or if you're using SQL 2008 Ent./SQL 2008 R2, try creating a compressed backup of a TDE-enabled database.
    You can backup and compress a db then encrypt the backup
    True.
    You can encrypt a db but not effectively compress a database.
    True.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • AdamMetcalfeAdamMetcalfe Posts: 11
    Petey

    Unfortunately I thought i had it right, thanks for confirming.

    I will be trialling next week with my own DB, but i need to write a business case for extra disk required when going with TDE now. What are the other advantages of SQL Backup when encryption is used? Im not a heavy user of SQL Backup (mainly used for log shipping). But if we cannot compress. I think we need to look at dropping the product. I am not saying the product is bad, but maybe our use for it diminishes without this advantage?

    I was secretely hoping someone was going to say yes it will compress, Nothing like having your encrypted cake and compressing it too. :)
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    See the link in my sig on what SQL Backup offers beyond just compression. If your database is TDE-enabled, you would back up the database using COMPRESSION = 0 so that the encrypted backup data just passes through to disk directly, without SQL Backup attempting to compress it.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • AdamMetcalfeAdamMetcalfe Posts: 11
    yes i did check your link out, the network resiliance is something that is pretty important in our environment, this is probably the main big ticket item for keeping it at the moment would be. we would still use it for log shipping i think as well.
    · Share on Twitter
Sign In or Register to comment.