TDE and Backup Compression…Can’t have your cake and eat it too!

I was testing out some new SQL Server 2008 features recently: Transparent Data Encryption (TDE) and Backup Compression. Both are new features in SQL Server 2008. TDE will allow you to encrypt an entire database on disk without needing any changes to your applications. Backup Compression will compress your backups and speed them up too. Both are Enterprise edition features…STOP PRESS…SQL Server 2008 R2 Standard edition allows Backup Compression too.

TDE will encrypt all your data pages on disk without enlarging the database files themselves. Microsoft refers to this as “data at rest”. As soon as the data page is read from disk to memory it is decrypted. Whenever a data page is updated, it is encrypted automatically when writing back to disk. Since the application code is exposed to unencrypted data, no changes are necessary. This could be handy if you are working with a package application but you need an extra level of physical security on your database. For instance, all backups will be automatically encrypted too so if a backup tape fell into the wrong hands it would not be such a big deal.

Backup Compression will compress and speed up your backups leaving more CPU cycles for your applications and saving space too. The algorithm cut down my backup to 25% of the original size in my tests. Of course, I wanted to show this off to my class this week. Using the WITH COMPRESSION option, I quickly switched to show the .BAK file size and it did not show any noticeable difference. In fact, it increased a little. Dang!

What I had done was to demonstrate TDE on my database first then later I was trying to show Backup Compression. The compression algorithm does not work well with encrypted data so the backup will complete just without any compression. So a valuable lesson learned: TDE is great but you have to give up the benefits of Backup Compression. At least the backup is encrypted.

It’s too easy to blame the technology but with me it usually comes back to the good old EBKAC error – “Error Between Keyboard And Chair”.

Cheers,

Brian

Related Courses

SQL Server 2008 for Administration (M6231, M6232)

Developing and Implementing a SQL Server 2008 Database (M6232)

In this article

Join the Conversation