Disaster Recovery - Backing up Keys and Certificates

It is important to back up SQL Server Keys and Certificates separately as not doing so can result in data loss.

Symmetric Key

The way that K2 uses the Symmetric key is based on K2's use of Certificates. Certificates are built into SQL Server and K2 is leveraging off the SQL Server platform. Hierarchically, it can be depicted in the following way:

Operating System Level: Windows DPAPI
SQL Server Level: Service Master Key (SMK)
SQL Server Level: Database Master Key (DMK)
- K2: Certificate
- - K2: Symmetric Keys

The encryption is applied in a top down manner, so the Operating System level secures the Service Master Key (SMK), etc.

As discussed in Database Disaster Recovery Options, there are four SQL Disaster Recovery Options which are supported by K2. In all options, the domain should be changed as the Operating System level uses the Service Account or SPN to encrypt the Service Master Key

Backup and Restore

As long as the SQL instance is still functional, the Service Master Key and Database Master Key will still be functional. Recreate the Certificate and Symmetric Keys and the data will be accessible.

Log Shipping

Visit the following link http://technet.microsoft.com/en-us/library/ms366281(SQL.90).aspx for information on how to create identical symmetric keys on two servers

Both servers should have the same Service Master Key

Database Mirroring

The same applies as in Log Shipping

Database Clustering

The same applies as in Log Shipping, although a Microsoft Cluster Server will not need the identical symmetric keys created as, due to its nature, it is aware of the other nodes and will likely use the same key by design.

Visit the following links for information on backup of Certificates and Keys:

Backup the Master Key: http://msdn.microsoft.com/en-us/ms174387(SQL.90).aspx
Backup the Certificate: http://msdn.microsoft.com/en-us/ms178578(SQL.90).aspx
How to backup the Service Master Key: http://technet.microsoft.com/en-us/library/aa337561.aspx
How to restore the Service Master Key: http://technet.microsoft.com/en-us/library/aa337510.aspx
SQL 2005 Symmetric Encryption: http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/