I’ve deployed several SQL Server Always On Availability Groups in the past with high availability being the primary requirement. In some environments, there is a requirement to protect sensitive data for security and compliance reasons. Back in 2013, I had a customer who wanted to implement Always On Availability Groups for their databases. They were moving their on-premise databases to a new hosting provider and wanted to make sure that the databases are protected at-rest. Their existing solution uses the Windows Encrypting File System feature for encrypting the disk volumes that contain the database files and TrueCrypt for the backups. But with the plan to move to a hosting provider, they need something that can do both because they don’t have the flexibility of keeping their existing solution. Besides, they already have the license for SQL Server 2012 Enterprise Edition, we might as well make the most out of it.

I also wrote an article that outlines what needs to be done to prepare their databases for the upgrade (since they were still on SQL Server 2008) and migration. And while the article has been very helpful for new deployments, not so much for existing Always On Availability Group implementations. Business requirements change frequently and you might need to implement Transparent Data Encryption (TDE) on your databases that are already in an Always On Availability Group configuration. If you follow the steps outlined in the article, you might end up causing issues on your Always On Availability Group databases depending on the amount of transactions generated. I’ve outlined the steps that you need to take if you would like to enable TDE on an existing database participating in an Always On Availability Group. I’m “stealing” the code examples in the article for reference.

1. 1. Create a database master key on the primary replica. The Database Master Key creates a certificate in the master database. To create a database master key, 

    
   USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mYC0mpl3XP@$$w0rd'
GO


 

2. 2. Create a certificate protected by the database master key. Once we’ve created a database master key, we need to create a certificate protected by the database master key. This certificate is what we’ll use to protect the database encryption key (DEK) used to enable TDE. To create a certificate protected by the service master key, 

    

   CREATE CERTIFICATE TDECert
WITH SUBJECT = 'My TDE Certificate for all user database in the Availability Group'

 

3. 3. Create a database encryption key and use the certificate to protect it. After the certificate has been created, we can now create a database encryption key that we can use to enable TDE. You will need to do this on all of the databases that are already on the Always On Availability Group. To create  a database encryption key and protect it using the certificate, 

    

   USE [DemoSalesDB]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECert
GO

 

4. 4. Backup the certificate to a file. This is where the process is a bit different from the article. If you enable TDE prior to restoring the certificate on the secondary replicas, the transaction log records won’t get applied to the secondary databases because they “don’t speak the same language yet.” You want to make sure that the databases in an Always On Availability Group configuration “speak the same language” in order for the secondary replicas to properly receive the transaction log records from the primary replica and apply them to the log file. To backup the certificate to a file, 

    

   USE MASTER
GO
BACKUP CERTIFICATE TDECert
TO FILE = 'C:\TDECert_File.cer'
WITH PRIVATE KEY (FILE = 'C:\TDECert_Key.pvk' ,
ENCRYPTION BY PASSWORD = 'mYC0mpl3XP@$$w0rd' )
GO

 

5. 5. Copy the certificate to the secondary replicas. Once the certificate has been generated to a file, copy it to all of the secondary replicas. We will use this certificate to protect

 

6. 6. Create a database master key on the secondary replicas. Similar to what we did on the primary replica, we need to create a database master key on all of the secondary replicas. Repeat this step and all the steps below on all of your secondary replicas.To create a database master key,

    

   USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mYC0mpl3XP@$$w0rd'
GO


 

7. 7. Create a certificate on all of the secondary replicas using the certificate file from the primary replica. Once we’ve created a database master key on the secondary replicas, we need to create a certificate based on the one that we generated and copied from the primary replica. To create the certificate on all of the secondary replicas, 

    

   USE MASTER
GO
CREATE CERTIFICATE TDECert
FROM FILE = 'C:\TDECert_File.cer'
WITH PRIVATE KEY (FILE = 'C:\TDECert_Key.pvk',
DECRYPTION BY PASSWORD = 'mYC0mpl3XP@$$w0rd' );

 

8. 8. Enable TDE on all of the databases in the Always On Availability Group. Now that we have given all of the Always On Availability Group replicas a “common language to use to talk to each other,” we can go ahead and enable TDE on all of the databases in the Always On Availability Group. And since enabling this feature generates transaction log records, all of the databases in the secondary replicas will also have TDE turned on. Note that you still have to do this on a per-database level. To enable TDE on the database in an Always On Availability Group, 

    

   ALTER DATABASE [DemoSalesDB]
SET ENCRYPTION ON

 

Note that the steps are a bit different from the one described in the article because we don’t want to cause any disruption on the existing Always On Availability Group configuration. Our goal is still maintaining high availability while meeting security and compliance requirements.

Additional Resources

• Configuring Transparent Data Encryption with SQL Server 2012 Always On Availability Groups
• Implementing Transparent Data Encryption in SQL Server 2008 (still applicable in SQL Server 2012 and higher)
• SQL Server Transparent Data Encryption (TDE)

Schedule a Call