TDE stands for Transparent Data Encryption and became available in SQL 2008 R2 Enterprise. TDE essentially protects your "data at rest" i.e. it protects your database from being stolen. As in backing up the database and restoring it in some other location where the contents can be analyzed via SQL.
Essentially when you apply TDE you are applying a certificate used to encrypt your data to the back-end database files (it only encrypts to the database files, when you access a TDE database via SQL there is no difference to the usual experience). You will only be able to restore the database to another database server if that server has the same TDE certificate applied. Otherwise you will be prevented from restoring the database - you will receive the "cannot find certificate with thumbprint" error:
The backed up database file will be similarly unintelligible.
As the name itself implies, TDE is transparent. Meaning this is configuration is applied on the database itself and any applications with back-end SQL databases running on top of it will be oblivious to the TDE. The only impact will be a slightly increased level of CPU utilization on the database server as it handles the data encryption for the SQL transactions.
In light of this, it is difficult to not recommend using TDE. It is simple to apply, it is completely transparent to applications, it protects your data... with the only downside being higher CPU utilization which for most databases won't make a difference (assuming the old adage "use it or lose it") and easily compensated in cases where the additional churn pushes your CPU over the edge.
Below are some scripts for setting up TDE on a SQL 2008 R2 Enterprise server:
/* If you wish to copy the certificate from an old server you will need to back it up */ --Backup the certificate from the 4.0 database server: USE master BACKUP CERTIFICATE TDECert TO FILE = 'C:\TDECRMCERT.BAK' WITH PRIVATE KEY ( FILE = 'C:\TDE.key', ENCRYPTION BY PASSWORD = 'EncryptionPassword' ) /* On a new server you will need to create a certificate using the backed up one from the old server. This procedure assumes you copied the backed up certificate to C:\ root on the new server */ -- Create a master key. NB: Record the password for future reference. USE master CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword' -- Backup the master key. NB: Store the backup file and record the password for future reference. USE master; BACKUP MASTER KEY TO FILE = 'C:\Master_Key.bak' ENCRYPTION BY PASSWORD = 'MasterKeyBackupFilePassword' -- Create a certificate by importing the certificate backed up in the first step. USE master CREATE CERTIFICATE TDECRM FROM FILE = 'C:\TDECRMCERT.BAK' WITH PRIVATE KEY (FILE = 'C:\TDE.key', DECRYPTION BY PASSWORD = 'EncryptionPassword') -- Backup the certificate. If you’re using the imported certificate this step is optional since the file -- you used to create the new certificate is already the backup file so you’ll just be recreating this file. -- NB: Store the backup file and record the password for future reference. BACKUP CERTIFICATE TDECRM TO FILE = 'C:\TDECRMCERT.BAK' WITH PRIVATE KEY ( FILE = 'C:\TDE.key', ENCRYPTION BY PASSWORD = 'EncryptionPassword' ) -- For each database you wish to restore to from a TDE enabled database you will need to apply the certificate -- and enable encryption. USE Vanilla_MSCRM GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 Encryption By Server Certificate TDECRM GO ALTER DATABASE Vanilla_MSCRM SET ENCRYPTION ON