SQL2008 TDE and SQLBackup Compression

AdamMetcalfe

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!

petey

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.

AdamMetcalfe

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.

petey

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.

AdamMetcalfe

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.