Transparent Data Encryption(TDE)
It protect the data and log files.
It encrypt the database at the page level.
It encrypt database before it write to disk.
It decrypt database when it read to memory.
It does not increase the size of the database.
It use Database Encryption Key(DEK) to encrypt Database.
DEK store in the database boot record for availability during recovery.
DEK can be protect by certificate or asymmetric key with extensible key management provider.
The above certificate or asymmetric key with extensible key management provider must be create at “master” system database.
When restore TDE database, the certificate or asymmetric key, which is used to protect DEK, must be available.
DEK use AES algorithm with 128,192,256 length.

View DEK information

SELECT * FROM SYS.dm_database_encryption_keys

View Database enable TDE or not

-- 0 is disable, 1 is enable
SELECT is_encrypted,* FROM SYS.databases

Create Master Key & CERTIFICATE at master

USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
CREATE CERTIFICATE TDE01 WITH SUBJECT='TDE EXAMPLE';

Create DEK

USE TDE_DB
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE01;

Change DEK’s AES length

ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_192;
GO

Enable TDE on databases

ALTER DATABASE TDE_DB
SET ENCRYPTION ON;

Disable TDE on databases

ALTER DATABASE TDE_DB
SET ENCRYPTION OFF;