SQL2008 TDE and SQLBackup Compression

• 

  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.

  petey July 20, 2011 22:18

  - 0 +
• 
  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.

  AdamMetcalfe July 21, 2011 00:07

  - 0 +
• 
  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.

  petey July 21, 2011 01:37

  - 0 +
• 
  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.

  AdamMetcalfe July 21, 2011 01:40

  - 0 +

Add comment

Please sign in to leave a comment.