How It Works: SQL Server 2008 Backup Compression, Database Compression and Total Data Encryption

I have seen a fair series of questions around the compression and encryption features of SQL Server 2008.   The main question is can I or should I used Total Data Encryption (TDE) and backup compression?

All these features work with each other but this is not always the recommended configuration. Without going into complete details, already available in books online, the following table outlines the basics.

Database Compression	Allows data in the database to be compressed on the database pages.    For example the integer value of 1 can be stored without requiring 4 bytes of storage.   It also locates similar patterns and only stores that pattern once.
Total Data Encryption (TDE)	Allows all data in the database to be encrypted.
Backup Compression	Uses a compression algorithm to compress the data stored on backup media.

Using backup compression with database compression or TDE enabled generally uses more CPU and produces limited compression benefits.  You can issue a database backup with compression when these features are enabled but you should not expect a large compression ratio.   The data is already compressed(database compression) or unique (TDE) preventing significant compression gains.

Backup without compression is generally a better choice when database compression or TDE is involved.

Bob Dorr
SQL Server Senior Escalation Engineer