Nadrek (7/7/2010)

---

jay holovacs (7/7/2010)

---

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. 🙂

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

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.