Environment

Enabling TDE on JBSERVER1

--Create database JB_DB

create database JB_DB
go

-- Create Master Key for TDE

USE master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='QKyrk@%wIj$p97';
GO

-- Create Certificate

USE master;
GO
CREATE CERTIFICATE JB_TDE_Certificate
WITH SUBJECT='Certificate for TDE';
GO

-- Create Encryption key

USE JB_DB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE JB_TDE_Certificate;

-- Enable TDE for database JB_DB

ALTER DATABASE JB_DB SET ENCRYPTION ON;
GO

-> Enable TDE for another database.

--Create database JBS_DB

create database JBS_DB
go

-- Create Encryption key

USE JBS_DB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE JB_TDE_Certificate;

-- Enable TDE for database JBS_DB

ALTER DATABASE JBS_DB SET ENCRYPTION ON;
GO

-> Execute below query and make sure the databases are encrypted.

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 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

-> This is what I see,

Backup the Certificate and Private Key

USE master;
GO
BACKUP CERTIFICATE JB_TDE_Certificate
TO FILE = 'C:\temp\JB_TDE_Certificate.cer'
WITH PRIVATE KEY (file='C:\temp\JB_TDE_Certificate.pvk',
ENCRYPTION BY PASSWORD='QKyrk@%wIj$p97');

Create certificate on JBSERVER2

-> Copy certificate C:\temp\JB_TDE_Certificate.cer and private key C:\temp\JB_TDE_Certificate.pvk from JBSERVER1 to JBSERVER2.

-> Execute below on JBSERVER2.

USE master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='QKyrk@%wIj$p97';
GO

CREATE CERTIFICATE JB_TD_Certificate
FROM FILE='C:\temp\JB_TDE_Certificate.cer'
WITH PRIVATE KEY (
FILE = 'C:\temp\JB_TDE_Certificate.pvk',
DECRYPTION BY PASSWORD='QKyrk@%wIj$p97')

-> Backup a database from JBSERVER1 and Restore it in JBSERVER2 and make sure restore works.

Adding Database JB_DB and JBS_DB to an Existing Alwayson Availability group JB_AG

-> Perform a Full database backup of JB_DB and JBS_DB on JBSERVER1.

-> Perform a Transaction Log backup of JB_DB and JBS_DB on JBSERVER1.

-> Try adding databases JB_DB and JBS_DB to Availability group JB_AG using GUI.

-> Adding a TDE enabled database using GUI is not possible. We need to use TSQL.

-> Create a database called Test and make sure it is not enabled with TDE. Create availability group JB_AG using database Test. Once the Availability group JB_AG is created on JBSERVER1 and JBSERVER2, Remove Test database from Availability group and delete the database Test from JBSERVER1 and JBSERVER2. This step is to create JB_AG Availability group before performing below steps.

-> Execute below query on JBSERVER1.

USE master
GO
ALTER AVAILABILITY GROUP [JB_AG] ADD DATABASE [JB_DB]

USE master
GO
ALTER AVAILABILITY GROUP [JB_AG] ADD DATABASE [JBS_DB]

-> Restore Full database backup and Transaction Log backup with NO RECOVERY on JBSERVER2.

-> Execute below query on JBSERVER2 to join the databases JB_DB and JBS_DB to Always On availability group JB_AG

USE master
GO
ALTER DATABASE [JB_DB] SET HADR AVAILABILITY GROUP = [JB_AG];

USE master
GO
ALTER DATABASE [JBS_DB] SET HADR AVAILABILITY GROUP = [JB_AG];

-> Check if the databases are added to Availability group JB_AG.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.