In this 15th article of SQL Server Always On Availability Groups series, we will cover Transparent Data
Encryption (TDE) for AG databases.
In the previous articles of SQL Server Always On series, we explored the following topics so far.
- Build virtual servers using Oracle VirtualBox
- We configured domain controller, active directory and domain name service (DNS)
- Deployed SQL Server 2019 Availability groups
- Domain-independent AG in Windows Server 2016
- We configured distributed availability groups for the availability group in independent clusters
Suppose, In specific requirements, you might want to protect sensitive data for compliance and security purposes.
SQL Server provides Transparent Data Encryption(TDE) for encrypting the physical files. In this
article, we will explore TDE for a SQL Server Always On Availability Group.
Overview of Transparent Data Encryption (TDE)
The objective of implementing TDE is to secure the data and log files for a SQL database. Suppose you have sensitive
customer data in your database. Someone might copy your database files and easily access critical information. This
encryption process does not require any changes from the application end. It encrypts the files at the page level
before writing the page in the disk. It decrypts the files once a user tries to access a page. Once we implement
TDE, we cannot restore or attach a database without an appropriate certificate and key.
The high-level steps for a TDE are as below.
TDE is not straightforward for an availability group database. In the next section, we explore the steps in a
two-node SQL Server Always On Availability Group.
Prepare two-node availability group replicas in synchronous mode. In this article, I have the following servers.
- Current Primary replica: SQLAG1INST1
- Secondary replica: SQLAG2INST2
- Synchronization status: Synchronized
- Failover mode: Automatic
- AG database: DBARepository
Steps to enable TDE for SQL Server Always On Availability Groups
Let’s explore the steps for TDE for the availability group database. We do not have transparent data encryption
enabled on any of the replicas as of now. The database [DBARepository] is part of the availability group, and we
require to enable TDE on it.
The below figure highlights the steps required to enable the TDE on an availability group database.
Step 1: Database Master Key (DMK) on the primary replica
We require a database master key(DMK) on the primary replica to secure certificates and keys. We require a DML on
the SQL instance. All user databases share the same DMK for encryptions.
First, you can check if your instance already has a database master key. We already created a DML for the
distributed availability group in the earlier article.
- You should use complex passwords for creating a master key
If the database master key is already available for your SQL instance, but you do not know the password of it,
your SQL service account with the SA permission can decrypt the key
- You can create the DML on all AG instances with different passwords
Step 2: Create the Certificate for the AG database on the primary replica
In this step, we create a certificate to encrypt the Database Encryption Key. The master key created in step 1
protects the certificate.
We can query the sys.certificates system object to verify the certificate exists for our SQL
Step 3: Create a database encryption key and use the certificate to protect it
In this step, we create a database encryption key (DEK) to enable the TDE. In our case, the DBARepository database
is already part of the availability group, and we want to enable TDE on it.
The database master key(DEK) is the actual key for the encryption and decryption of the database. The server
certificate protects it.
Execute the script under the AG database context and specify the certificate name in the parameter
ENCRYPTION BY Server Certificate argument. In the output, you get a warning message. It asks you to
back up the certificate and the private key. Don’t worry about it. We take the backup in the next step.
Step 4: Backup the certificate and private key on the primary replica
In this step, we need to take the certificate and its private key backup. This backup should be encrypted by a
complex password as well.
It creates two files in the specified directory, as shown below.
The first file [TDECert] is the certificate and the second file [TDECert_private] is the private key file, and it is
protected by the password specified while taking the backup.
Step 5: Create a database master key on the secondary replica
This step is similar to step 1. You should create a database master key on all secondary replicas if it does not
exist. In my environment, this encryption key already exists on the secondary replica node SQLAG2INST2 as shown
Step 6: Create a certificate on the secondary replicas from the primary replica certificate
You must copy the certificate from the primary replica to all secondary replicas. In this step, we create a
certificate on the secondary replica from the primary replica certificate.
You must specify the password that we used earlier to encrypt the backup. If you specify a different password, it
won’t decrypt the certificate.
Step 7: Enable TDE for the SQL Server Always On Availability Group database
We are ready to enable the TDE for the database participating in an availability group. Execute the following t-SQL
on the primary replica.
Monitor the transparent data encryption in SQL Server Always On Availability Group
We use the dynamic management view sys.dm_database_encryption_keys to monitor the TDE status.
It gives the following column outputs:
- Is_encrypted: The value “1” denotes TDE is enabled for the corresponding database
- Encryption_state: Initially, once you enable the TDE, it shows the encryption_state 2 along
with its percentage completion in the percent_complete column. It might take a few hours to
enable TDE on a vast database. You should monitor the status of it after enabling it. Once encryption completes,
it changes the encryption_state value to 3 and percent_complete value to zero
- Key_algorithm and key_length: It shows the algorithm and key length for encrypting the databases
As per the values of the encryption_state column, we can modify the query with the CASE statement in SQL. You can browse the article How to
monitor and manage Transparent Data Encryption (TDE) in SQL Server for the monitoring query
We see that the TDE certificate encrypts our database DBARepository.
You can also monitor the TDE status in the SQL Server error logs. It has two useful entries.
- Beginning database encryption scan for the database DBARepository
- The database encryption scan for the database ‘DBARepository’ is complete
Note: If your database in the primary replica is encrypted, but this database is joined in the secondary replica,
- Current primary replica: SQLAG1INST1
- New Primary replica: SQLAG2INST2
- Failover mode: Manual failover with no data loss ( database in the Synchronized state)
- AG database: DBARepository
you cannot use the availability group wizard. You must use the t-SQL for this purpose. You can refer to this
article, How to add a TDE encrypted user database to an Always On Availability Group for this purpose
SQL Server Always On Availability Group failover testing after TDE implementation
You must do failover testing after you implement any new functionality in the SQL Server Always On Availability
Group. It helps you to troubleshoot any foreseen issues during implementations only. It is easy to fix the things
compared to knowing the issue at the time of any failover.
Launch failover availability group wizard and select the new primary replica.
In my case, we have the following configurations.
AG Failover is successful now, as shown below.
Launch the AG dashboard and verify its status.
Verify TDE encryption as well after the failover using the monitoring scripts provided earlier.
In this article, we explored transparent data encryption (TDE) for the database in SQL Server Always On Availability
Groups. You should implement TDE encryption to protect sensitive data files and prevent unauthorized access.