How to configure Always On for a TDE database

In this blog we will walk you through the steps to create availability group for an encrypted database. Though we have various articles, I would like to keep the entire step by step approach in one place.

We cannot create an availability group for an encrypted database from the availability group wizard. You will receive the below error

This wizard cannot add a database containing database encryption key to an availability group

We have to add the database using T-SQL statements.

Before you configure Always On for a TDE database make sure the following pre requisites are followed.

1. Always On endpoint port is opened in all node (5022 is default port, which can be changed).

2. Startup account of cluster service is added as SQL Server login and granted the ALTER ANY AVAILABILITY GROUP permission (for AlwaysOn Availability Groups) and the VIEW SERVER STATE permission (for SQL FCI).

{

In Windows Server 2008 Failover Clusters, the cluster service no longer runs in the context of a domain user account. Instead, the cluster service runs in the context of a local system (Nt authority\ system) account that has restricted rights to the cluster node. By default, Kerberos authentication is used. If the application does not support Kerberos authentication, NTLM authentication is used.

When installing the Database Engine as an Always On Availability Groups or SQL Failover Cluster Instance (SQL FCI), LOCAL SYSTEM is provisioned in the Database Engine. The LOCAL SYSTEM login is granted the ALTER ANY AVAILABILITY GROUP permission (for Always On Availability Groups) and the VIEW SERVER STATE permission (for SQL FCI).

}

Let me demonstrate few examples here:

Method 1 (Using T-SQL and GUI):

In this example, let us assume that you have a two node windows cluster server A and server B with two SQL standalone 2012 instances. Availability group feature is enabled for both the instances.

On server A which is our primary replica, perform the below steps

1.Enable TDE for the database test.

USEmaster;

GO

CREATEMASTERKEYENCRYPTIONBYPASSWORD='Test@123';

go

CREATECERTIFICATETestCertWITHSUBJECT='Cert@123'

go

USETest

GO

CREATEDATABASEENCRYPTIONKEY

WITHALGORITHM=AES_128

ENCRYPTIONBYSERVERCERTIFICATETestCert

GO

ALTERDATABASEtest

SETENCRYPTIONON

GO

2.Backup the certificate on the primary server

Usemaster

BACKUPCERTIFICATETestCertTOFILE='C:\test\TestCert.cer'

WITHPRIVATEKEY (FILE ='C:\test\TestCert.pvk',

ENCRYPTIONBYPASSWORD='Pas$w0rd');

GO

3.Take full and log backups for database test

4.Create an availability group named Test_AGfrom management studio ( create availability group )

5.Now add server B as secondary replica, while doing this select Skip initial data synchronization as we need to do it manually.

6.Now add the encrypted database Test to the availability group by running the below command

ALTERAVAILABILITYGROUPTest_AGADDDATABASETest

7.Verify the availability replicas and database on primary from the management studio

On server B which is our primary replica, perform the below steps

1.Create the master key and certificate from the backup taken from primary.

USEmaster;

GO

CREATEMASTERKEYENCRYPTIONBYPASSWORD='Test@123';

Go

CREATECERTIFICATETestCert

FROMFILE='C:\test\TestCert.cer'

WITHPRIVATEKEY

(

FILE='C:\test\TestCert.pvk',

DECRYPTIONBYPASSWORD='Pas$w0rd'

);

go

2.Restore the database full and transaction log backups with no recovery

useTest

OPENMASTERKEYDECRYPTIONBYPASSWORD=''Test@123';

GO

Restoredatabasetestfromdisk='c:\test\test.bak'

withnorecovery

go

Restorelogtestfromdisk='c:\test\test.trn'

withnorecovery

3.Add the database to the availability group by running the below command

useTest

OPENMASTERKEYDECRYPTIONBYPASSWORD=''Test@123';

Go

ALTERDATABASETestSETHADRAVAILABILITYGROUP=Test_AG;

4.You can verify the data availability group status and database status from management studio for the secondary replica.