Transparent data encryption (TDE)

Introduction: Encrypting at the Database Level :

Transparent data encryption (TDE) is a new encryption feature introduced in Microsoft® SQL Server™ 2008. to use encryption in Microsoft SQL Server 2005, the column data type must be changed to varbinary;

Q & A :

What happen if you need to restore or detach/attach an encrypted database on a different server or instance?
The certificate that was used to encrypt the database encryption key must be available. Otherwise, the operation will fail.

TDE :
Transparent data encryption is the new database-level encryption feature introduced in SQL Server 2008.
How to Enable TDE :
To enable TDE, you must have the normal permissions associated with creating a database master key and certificates in the master database. You must also have CONTROL permissions on the user database.
To enable TDE :

1- Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘some password’;
2- Create Certificate
CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’;
3- Create Database Encryption
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert
4- Enable TDE in Database
ALTER DATABASE myDatabase SET ENCRYPTION ON

Finally, let’s find the state of the database encryption.
SELECT db_name(database_id), encryption_state
FROM sys.dm_database_encryption_keys