Enable TDE for a database in AlwaysOn Availability Group

In this blog, we will explore how we can enable TDE for a database that is already part of an AlwaysOn Availability Group.

Step 1:  We need to create  Master Key,  Certificate, and Database Encryption Key. The script below needs to be executed on the Primary replica. Modify the password and location as required.

 

USE MASTER
GO

-- Create a Master Key
CREATE MASTER KEY ENCRYPTION BY Password = 'Password@123';

--Backup Key
BACKUP MASTER KEY TO FILE = 'D:\TDE\MK_backup.key' ENCRYPTION BY PASSWORD = 'Password@456';
GO

-- create certificate
CREATE CERTIFICATE TDECertificate
	WITH SUBJECT = 'TDE_CERT';
GO

-- backup Cert
BACKUP CERTIFICATE TDECertificate TO FILE = 'D:\TDE\EncryptionCert.cert'
WITH PRIVATE KEY (
		FILE = 'D:\TDE\EncryptionCert.key'
		,ENCRYPTION BY PASSWORD = 'Password@123'
		);
GO

USE < DB Name FOR which we have TO enable TDE >
GO

-- Create a Database Encryption Key
CREATE DATABASE ENCRYPTION KEY
	WITH Algorithm = AES_128 ENCRYPTION BY SERVER Certificate TDECertificate;

Step 2:  Below script needs to be executed on Secondary Replicas, which creates the Master Key & the same CERTIFICATE which we created on Primary.

USE MASTER
GO

-- Create a Master Key
CREATE MASTER KEY ENCRYPTION BY Password = 'Password@123';

--Backup Key
BACKUP MASTER KEY TO FILE = 'D:\TDE\MK_backup.key' ENCRYPTION BY PASSWORD = 'Password@456';
GO

-- Copy the CERTIFICATE taken on Primary Replica(in step 1) to all secondary Replicas & paste it on drive : D:\TDE\
CREATE CERTIFICATE TDECertificate
FROM FILE = 'D:\TDE\EncryptionCert.cert'
WITH Private KEY (
		FILE = 'D:\TDE\EncryptionCert.key'
		,Decryption BY Password = 'Password@123'
		);

-- backup Cert
BACKUP CERTIFICATE TDECertificate TO FILE = 'D:\TDE\EncryptionCert_secondary.cert'
WITH PRIVATE KEY (
		FILE = 'D:\TDE\EncryptionCert_secondary.key'
		,ENCRYPTION BY PASSWORD = 'Password@123'
		);
GO

Step 3:  Connect to Primary Replica again & issue the below command after creating the certificates and keys on the secondary replica(s).

ALTER DATABASE < DB Name FOR which we have TO enable TDE >

SET ENCRYPTION ON

Step 4: Monitor the progress of TDE using the below script

SELECT DB_NAME(database_id) AS DatabaseName
	,encryption_state
	,encryption_state_desc = CASE encryption_state
		WHEN '0'
			THEN 'No database encryption key present, no encryption'
		WHEN '1'
			THEN 'Unencrypted'
		WHEN '2'
			THEN 'Encryption in progress'
		WHEN '3'
			THEN 'Encrypted'
		WHEN '4'
			THEN 'Key change in progress'
		WHEN '5'
			THEN 'Decryption in progress'
		WHEN '6'
			THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
		ELSE 'No Status'
		END
	,percent_complete
	,encryptor_thumbprint
	,encryptor_type
FROM sys.dm_database_encryption_keys

Step 5: Once the database is encrypted, we have to resume the Data Movement. Connect to the server instance that hosts the secondary replica whose database we want to resume by issuing the below command.

ALTER DATABASE database_name

SET HADR RESUME

Done!! I hope this helps 😊