I am testing TDE in a lab environment for a proof of concept for roll out. I have 3 servers, a primary, a disaster recovery using AlwaysOn Availability Groups, and a report server subscriber using transaction replication from the primary. I set up the primary first using the scripts below:
-- Create a Master Key
CREATE MASTER KEY ENCRYPTION BY Password = 'Password1';
-- Backup the Master Key
BACKUP MASTER KEY
TO FILE = 'Server_MasterKey'
ENCRYPTION BY Password = 'Password2';
-- Create Certificate Protected by Master Key
CREATE Certificate SQLCertTDEMaster
WITH Subject = 'Certificate to protect TDE key';
-- Backup the Certificate
BACKUP Certificate SQLCertTDEMaster
TO FILE = 'SQLCertTDEMaster_cer'
WITH Private KEY (
FILE = 'SQLCertTDEMaster_key',
ENCRYPTION BY Password = 'Password3'
Then I encrypted the Primary DB with the following scripts:
-- Create a Database Encryption Key
CREATE DATABASE ENCRYPTION KEY
WITH Algorithm = AES_128
ENCRYPTION BY Server Certificate SQLCertTDEMaster;
-- Enable the Database for Encryption by TDE
ALTER DATABASE TestDB
SET ENCRYPTION ON;
When I run the following select statement (select * from sys.dm_database_encryption_keys
). It lists the database in an encryption_state = 2, with 0 percent_complete. It has been that way for 24 hours and the database is only 2GB.