TravisDBA (7/6/2010)

---

I am talking about compressing the backup, not compressing the database then encrypting it,, But anyway, I don't remember seeing that anywhere in SQL 2008 BOL? That would be nice if that little important detail was in there, but what else is new. :)

Michael Valentine Jones (7/6/2010)

---

SQL Server 2008 Books Online does specifically mention that, but don't let that get in the way of a good gripe.

TravisDBA (7/6/2010)

---

Thanks, but I already know about backing up a encrypted database with compression has no effect on the compression, and that is in BOL, hence my first post. However, it was pointed out in a previous post by Jay that compressing the database first then encrypting as an alternative. Where is that in BOL?, Anyway, I still don't like the idea of Mickeysoft giving us two new neat features we basically can't use together. :)

jay holovacs (7/7/2010)

---

My comment was not based on BOL, it was based the on the mathematics of compression and encryption. Compression works on mapping repetitive patterns in data, once encyrpted almost all such patterns are lost

Nadrek (7/7/2010)

---

Which is why all normal encryption software that's built for this first compresses (which, as a desirable side effect, reduces repetitive patterns in the data), and then encrypts! See PGP and GPG software, or any of the compression+encryption softare (7-Zip, PKWare Zip, WinZip, etc.) which use this ordering.

If your DB is encrypted but not compressed, then clearly one should have the option to spend the CPU time to decrypt, then compress, and then encrypt the compressed data, thus giving the effects of both. Since SQL Server compression doesn't take a whole lot of CPU anyway, this shouldn't be an unusual option to consider.