I have the biggest problem trying to get a handle on data encryption. So, when it came time to test a log shipping solution I'd implemented for one of our websites the initial testing failed because I hadn't done a couple of basic, but very important steps.
First, and this is really important, back up the service master key and database master key for each database which uses SQL Server encryption. It's not hard - here are the steps:
use master
GO
BACKUP SERVICE MASTER KEY FROM FILE = 'C:\MyDirectory\ServerSMK.key' ENCRYPTION BY PASSWORD = 'UD58ss6r'
GO
use MyDatabase
GO
BACKUP MASTER KEY FROM FILE = 'C:\MyDirectory\MyDatabaseMK.key'
ENCRYPTION BY PASSWORD = 'UD58ss6r'
GO
Copy these files to somewhere safe, where you know you'll be able to find them should you find you need to recover the data in these databases.
Now, should you find that you need to recover the database, you can restore the database from backup, then issue the following commands to restore full access to the encrypted data:
use master
GO
RESTORE SERVICE MASTER KEY FROM FILE = 'C:\MyDirectory\ServerSMK.key' DECRYPTION BY PASSWORD = 'UD58ss6r'
GO
use MyDatabase
GO
RESTORE MASTER KEY FROM FILE = 'C:\MyDirectory\MyDatabaseMK.key'
DECRYPTION BY PASSWORD = 'UD58ss6r'
ENCRYPTION BY PASSWORD = 'ccH4QvQCp8Ry6nYSsVxZ5oU'
FORCE
GO
As I mentioned, I learned this while testing a site where we've implemented log shipping to another city's data center in the event of a disaster in our Cleveland data center, and after restoring the service master key and database master key, the application on the alternate site worked as it does here in Cleveland. Of course, disaster recovery without testing doesn't amount to much, so it was a good exercise for many reasons.
I'm still not real clear on all the encryption technology, but I'm much more comfortable with it than I was a week ago.
Allen