First published on TECHNET on Oct 01, 2013
Overview of TDE with some details on major administrative issues.
Many people who've played around with TDE seem to have had trouble with restoring a TDE database on an alternate server, and the confusion seems to stem primarily from the deep encryption heirarchy for TDE. It's not too hard, however, once you realize that you can ignore most of the stack. The lynchpin is the certificate that is just above the TDE database in the heirarchy. If you use the certificate backup option to export the certificate's private key and protect it by password, then you can restore the certificate to any instance of SQL Server of the same version or later. Once the certificate has been restored, you should also be able to restore the TDE database that was protected by that certificate.
I've attached a .zip with a detailed document and a T-SQL script to help you experiment with TDE. Here are the topics in the document:
-
What is Transparent Database Encryption (TDE)?
-
Limitations of TDE
-
Costs of TDE
-
Alternatives to TDE
-
When to use TDE
-
How to enable TDE
-
How to backup a TDE database
-
How to restore a TDE database to the same server
-
How to restore a TDE database to an alternate server
-
TDE/Encryption-Related System Tables
-
TDE's Encryption Hierarchy
Before you invest a lot of time with TDE, consider my standard lecture #8: Since there are many good alternatives to using TDE and since there are costs imposed by using TDE, it shouldn't be used unless there are specific reasons for which the alternatives are inadequate and the costs are justifiable. In practice, this means that TDE should rarely be implemented.
See Also:
http://blogs.technet.com/b/fort_sql/archive/2012/02/23/does-the-dod-stig-require-transparent-da...
TDE overview and example script.zip
