I was talking with someone the other night about their database systems and they mentioned they had implemented TDE (Transparent Data Encryption) to comply with HIPAA regulations. This person had verified that they were backing up the Database Encryption Key, which you definitely need if you want to restore a backup from a TDE encrypted database. However they weren't sure if the certificate that protected the DEK, and the master keys on that instance were being backed up. Probably most scary to me, they hadn't tested any restores of the database.
Encryption is serious business, and if you are going to implement it in your databases, you had better be sure you understand how the various keys and certificates work. You better be sure you have protected your passwords, and that you can find them in the event of some issue.
Most importantly, though, is that you need to practice recovering your database to another instance. Preferably you'd learn how to recover on an instance that hasn't ever enabled encryption as well as one that has a different SMK or DMK.
Practicing restores isn't just about encryption and the potential for data loss because you don't have a key. Practicing restores is important for all of your systems to be sure you have the skills to successfully complete a restore. It helps ensure you know where the files, tapes, disks, or any other resources are located. Most importantly it ensures that your backup process is actually running smoothly.
Please don't assume your backup process works. Whether you're an accidental DBA stuck with their first SQL Server, or a ten year senior DBA that has performed hundreds of restores at previous jobs. You need to test your process and ensure that you can perform restores on the systems you are managing.
The Voice of the DBA Podcasts
We publish three versions of the podcast each day for you to enjoy.
The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
You can also follow Steve Jones on Twitter: