TDE in SQL Server

Implementing TDE in SQL server

-- Create Master Key
USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='STRONGPASSWORD';
GO

-- Create Certificate protected by master key
CREATE CERTIFICATE TDE_Cert
WITH 
SUBJECT='Database_Encryption';
GO

-- Create Database Encryption Key
USE <DB>
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO

-- Enable Encryption
ALTER DATABASE <DB>
SET ENCRYPTION ON;
GO

-- Backup Certificate
BACKUP CERTIFICATE TDE_Cert
TO FILE = 'C:\backup\TDE_Cert'
WITH PRIVATE KEY (file='C:\backup\TDE_CertKey.pvk',
ENCRYPTION BY PASSWORD='STRONGPASSWORD') 

You must remember where you backed up the certificate and the encryption/decryption password.

Restoring a Certificate

-- Create Master key on the secondary server
USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='STRONGPASSWORD';
GO

-- restore the key with encryption password
USE MASTER
GO
CREATE CERTIFICATE TDECert
FROM FILE = 'C:\backup\TDE_Cert'
WITH PRIVATE KEY (FILE = 'C:\backup\TDECert_Key.pvk',
DECRYPTION BY PASSWORD = 'STRONGPASSWORD' );