Remember when you were eight years old, how exciting it was to order some of those amazing products that were advertised in the back of comic books? Sea Monkeys, your very own nuclear sub,  X-Ray glasses? Good stuff…

Remember how utterly disappointed you were when the crap that you ordered arrived, and the nuclear sub turned out to be three inches long, made of plastic, and fueled by baking soda? Those little black specks floating in water that looked nothing like monkeys? Yeah, good stuff alright…

Remember when SQL Server 2008 was still yet-to-be-released, and promised us things like built-in encryption, built-in compression of both data and backups? Finally, we could stop using third-party tools to fill these needs. No more Litespeed, no more Encryptionizer (no offense to either intended), it’s all available, right there in the box. Yeah, GOOD STUFF!

Take a walk with me down memory lane…

We’ve just upgraded, and naturally we want to start pushing buttons and turning knobs. What should we do first? Oh, how about a backup? Yeah, let’s try out the new backup compression!

-- First, we'll do a normal, uncompressed backup
BACKUP DATABASE AdventureWorks2012
TO DISK = 'C:\SQLBackups\AdventureWorksUncompressed.BAK'
GO
-- Next we'll do a compressed backup
BACKUP DATABASE AdventureWorks2012
TO DISK = 'C:\SQLBackups\AdventureWorksCompressed.BAK'
WITH COMPRESSION
GO
-- How does that compare to the uncompressed backup?
SELECT physical_device_name, backup_size, compressed_backup_size
FROM msdb.dbo.backupset
INNER JOIN msdb.dbo.backupmediafamily
 ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE backupset.database_name = 'AdventureWorks2012'
 AND backupmediafamily.physical_device_name 
  IN ('C:\SQLBackups\AdventureWorksUncompressed.BAK',
      'C:\SQLBackups\AdventureWorksCompressed.BAK')
GO

From which we see this:

Holy downsizing, Batman, it worked! The compressed backup is 75% smaller than the uncompressed one. That’s a huge space savings! I’m sold – backup compression for everyone, it’s the new standard for our SQL Server backups.

Now let’s turn on encryption, because we want to keep our database safe. It’s a simple process, which I’ve written about before. Following the instructions in that post, we encrypt the AdventureWorks2012 database. Super-simple.

Remember how that felt? Wham! Bam! Kapow! Just like that, we’ve eliminated the need for two different third-party products from our environment. The boss is gonna be pleased! You confidently fire off an email asking him to stop by your desk so that you can show him your awesome new toys.

Your Nuclear Sub Has Arrived

Within minutes your boss walks over – “What’s up?” he says. You reply “You’ve gotta see this. Backups are compressing down to 25% of the original database size, AND I can encrypt the databases, without any extra software.”

You repeat the same backup compression test that you did previously:

BACKUP DATABASE AdventureWorks2012
TO DISK = 'C:\SQLBackups\EncryptedAdventureWorksUncompressed.BAK'
GO
-- Next we'll do a compressed backup
BACKUP DATABASE AdventureWorks2012
TO DISK = 'C:\SQLBackups\EncryptedAdventureWorksCompressed.BAK'
WITH COMPRESSION
GO
-- How does that compare to the uncompressed backup?
SELECT physical_device_name, backup_size, compressed_backup_size
FROM msdb.dbo.backupset
INNER JOIN msdb.dbo.backupmediafamily
 ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE backupset.database_name = 'AdventureWorks2012'
 AND backupmediafamily.physical_device_name 
  IN ('C:\SQLBackups\EncryptedAdventureWorksUncompressed.BAK',
      'C:\SQLBackups\EncryptedAdventureWorksCompressed.BAK')

Which gives us:

Uh-oh, wait a sec. What happened? Where’s our 75% compression?

Much like those comic book ads offering us X-Ray vision from a pair of cheap glasses, the truth is in the fine print. In this case, the fine print is found on MSDN in this article: http://msdn.microsoft.com/en-us/library/bb964719(v=sql.100).ASPX

Encrypted data compresses significantly less than equivalent unencrypted data. If transparent data encryption is used to encrypt an entire database, compressing backups might not reduce their size by much, if at all.

Coming from a world where backup products like Quest’s LiteSpeed or RedGate’s SQL Backup, teamed up with database encryption tools like Encryptionizer, offered us both protection AND space savings, I found (and still do) this to be a HUGE disappointment. I know, I know, encrypted data doesn’t contain the repeating patterns that make for good compression, but if third-party tools FROM DIFFERENT VENDORS can work together to provide both of these features, why can’t the company who owns the database platform and controls the inner workings make it happen?

With databases that long ago crossed the terabyte threshold, I’m really missing my backup compression. So much so that I considered going back to third-party backup tools, until I discovered that EVEN THEY can’t compress backups from a database that is protected by SQL Server’s built-in encryption. We’re just stuck with large backup files.

How Can You Be The Hero?

First, be aware of this limitation and make sure your boss is aware of it. When planning for your upgrade and expected disk space needs, you don’t want to discover that you didn’t allocate enough disk for your database backups.

Second, consider using table and/or index compression within the database itself. This type of compression occurs before encryption, and actually works very well. I personally reduced an encrypted 850GB database down to just under 500GB using table/index compression.

Does this annoy you as much as it does me? What other features of SQL Server, any version, do you think failed to live up to the hype? Sound off in the comments…

The post Transparent Data Encryption’s Dirty Little Secret appeared first on RealSQLGuy.