My Favorite New Feature in SQL Server 2014

Happy New Year readers, I was slack in blogging over the holidays, but I’m back and ready to get going with 2014. Last night, our user group featured a speaker gave and overview of all of the new features in SQL Server 2014 (which I think will arrive in the first half of this year) and there are some really things:

• In-Memory OLTP (Formerly known as Hekaton)
• New Cardinality Estimator
• Security Enhancements (new roles for better separation of duties)
• Updateable and clustered columnstore indexes (I’ve blogged and presented on these)
• Enhancements to AlwaysOn Availability Groups
• Better integration with Windows Azure services

All of these are really nice features—in particular I really like the option of being able to use Windows Azure for offsite backup storage and even a disaster recovery (DR) site in a number of different SQL Server DR scenarios. However, one thing stands out, and it is even in the standard edition of SQL Server 2014—Backup Encryption.

Due to their nature database backups tend to travel a lot of places, and pass through a lot of hands. In some shops, you may be backing up to tape, and then shipping that tape off to a 3^rd party (like Iron Mountain). Even though, young Timmy who unloads the tape library seems trustworthy—do you know what he’s doing with those tapes? Or if he made a copy of them—and sold a copy of our customer database to the Russian mafia? You might want to check to see how Timmy paid for his new M5.

SQL Server somewhat fixed this Transparent Data Encryption (TDE) being included in SQL 2008—if we turn on TDE, by default our backups are encrypted, along with the performance hit that entails, and the fact that it requires enterprise edition. TDE is a nice feature—I’ve used it where I had high security requirements, but you may still have sensitive data, that either can’t support the performance hit of encryption or isn’t of the level of sensitivity where it needs to be encrypted. Or maybe your company won’t spring for Enterprise Edition.

In SQL 2014, things get a lot simple in terms of encrypting backups. First let’s take a look at a backup file (unencrypted). So we create a database, add a table, and take a backup.

If Timmy’s Russian mafia friends had a copy of this backup they have a couple of options—they can restore it to another instance of SQL Server, or if they are looking for a specific of piece of data, they could fire up the old text or hex editor (Hey look—it’s John Smith’s SSN).

So let’s see how we do this encryption thing. First we need to create a cert (NOTE: PROTECT THIS CERT WITH YOUR LIFE AND JOB—YOU CAN’T RESTORE WITHOUT IT)

Once our certificate is created we can either backup using the WITH ENCRYPTION option, or through backup options in the GUI.

Let’s try that hex editor again:

Since the file is encrypted, everything is a mash of gibberish. More importantly, no one can take our backup file and restore it to another database server. This is a very good thing for us. However, when Timmy starts sending the Russian mob backups they can’t do anything with, he may not be long for this world. So start looking for a new backup technician when you turn this feature on.