How do we add a database that has TDE Encryption enabled to a SQL Server Always On Availability Group?

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How we enable Unified Auditing in Oracle Database - 7 October 2024
• What is PostgreSQL and how do we do a full installation - September 2, 2024
• How do we configure GoldenGate replication to read from Oracle Data Guard Standby - 2 August 2024

In previous article we have seen that TDE (Transparent Data Encryption) is the encryption of a database at a physical level. That is, it encrypts data files, log files and backups.

When a database in SQL Server has TDE (Transparent Data Encryption) enabled, we will see that if we go through a wizard to add it to an Always On Availability Group, it will not allow us:

In this article we will see how we can add a TDE-enabled database to an Always On Availability Group.

The example

In our example we have created a database with the name demo_tde.

We have created it master key encryption and certificate so we can enable TDE SQL Server Instance:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD  = 'Passw0rd'
GO

CREATE CERTIFICATE tdeCert WITH SUBJECT = 'TDE Certificate'
GO

We have taken backup the certificate with creation private key:

BACKUP CERTIFICATE tdeCert TO FILE = 'C:\backup\tde.cer' WITH PRIVATE KEY ( FILE = 'c:\backup\tde.pvk' ,  ENCRYPTION BY PASSWORD =  'Passw0rd' ); 

Then we created one encryption key which uses this certificate and we activated the encryption at the base:

use demo_tde
go
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert;

ALTER DATABASE demo_tde
SET ENCRYPTION ON;

If we run the following query we will see that the base demo_tde is in status Encrypted:

SELECT
[db].name,
[db].is_encrypted,
[k].encryption_state,
[k].percent_complete,
CASE [k].encryption_state
WHEN 0 THEN 'Not Encrypted'
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 '
ELSE 'Not Encrypted'
END AS 'Desc'
FROM
sys.dm_database_encryption_keys [k]
RIGHT JOIN
sys.databases [db] ON [k].database_id = [db].database_id;

The footsteps

On the primary node

As a first step, it is good to have disabled it Transaction Log backup as if the database is very large and the process is slow, we will need all the Transaction Log backups performed during this time.

Then we take a full backup of the database:

BACKUP DATABASE [demo_tde] 
TO DISK = N'C:\backup\demo_tde_full_co.bak' WITH STATS = 10;
GO

On the secondary node

First we transfer from the primary node to the secondary, the backup of the certificate (C:\backup\tde.cer), the private key (C:\backup\tde.pvk) and the database backup (C:\backup\demo_tde_full_co.bak).

We create it master key encryption and we do restore the certificate with the backup file, the private key and its password:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD  = 'Passw0rd'
GO

create certificate tdeCert from file ='C:\backup\tde.cer' WITH PRIVATE KEY ( FILE = 'c:\backup\tde.pvk' ,  DECRYPTION BY PASSWORD = 'Passw0rd'); 
GO

We restore the database with NORECOVERY:

RESTORE DATABASE [demo_tde] 
FROM DISK = N'C:\backup\demo_tde_full_co.bak' WITH NORECOVERY, STATS = 10;
GO

Once completed, we will see that it is in status Restoring...:

On the primary node

Now we go back to the primary node and add the database to the Availability Group with T-SQL:

*If we already had another replica of the database on another node, we skip this step

USE master 
GO
ALTER AVAILABILITY GROUP [DP-AG] ADD DATABASE [demo_tde]

On the secondary node

Then we go back to the secondary node and declare that the database belongs to this Availability Group:

USE master
GO 
ALTER DATABASE [demo_tde] SET HADR AVAILABILITY GROUP =  [DP-AG];

In case the following message has appeared, ignore it as it means that the database has already been added to the Availability Group:

Msg 41145, Level 16, State 1, Line 19
Cannot join database 'demo_tde' to availability group 'DP-AG'.  The database has already joined the availability group.  This is an informational message.  No user action is required.

Now if we refresh we will see that it is in status Synchronized which is also what we want for Synchronous commit. If we had chosen Asynchronous commit it should be in status Synchronizing:

With the following query we can see information about the health status of the database in the Availability Group:

SELECT ag.name AS [AG_Name], ar.replica_server_name AS [ReplicaServerName], ar.availability_mode_desc AS [AvailabilityMode], adc.[database_name], 
       hars.role_desc as [Role],  drs.synchronization_state_desc as [SynchronizationState], drs.is_commit_participant as [IsCommit], 
       drs.synchronization_health_desc as [SyncHealth],drs.database_state_desc as [DatabaseState],
       drs.last_commit_time as [Last Commit Update Time]
FROM sys.dm_hadr_database_replica_states AS drs WITH (NOLOCK)
INNER JOIN sys.availability_databases_cluster AS adc WITH (NOLOCK)
ON drs.group_id = adc.group_id 
AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag WITH (NOLOCK)
ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar WITH (NOLOCK)
ON drs.group_id = ar.group_id 
AND drs.replica_id = ar.replica_id
INNER JOIN sys.dm_hadr_availability_replica_states hars 
ON drs.replica_id = hars.replica_id 
where database_name='demo_tde'
ORDER BY ag.name, ar.replica_server_name, adc.[database_name] OPTION (RECOMPILE);