In an AlwaysOn scenario, enabling TDE on one or more secondary replicas is slightly complex versus a standalone SQL Server instance. The current setup in your environment may vary and this article talks about enabling TDE on the databases in an AlwaysOn scenario in those situations.
Transparent Data Encryption is about securing the data at rest on the SQL Server. If the data files are stolen and are attempted to be restored, the data thieves will not be able to perform a successful restore. If a database is being used in database mirroring, log shipping or AlwaysOn, both databases will be encrypted. The log transactions will be encrypted when sent between them.
Below are two cases, you may have in your current environment and is dealing with those cases are described in this document.
CASE 1: Database(s) is/are NOT encrypted and is/are part of Availability Group
If you have an AlwaysOn setup and the database in the Availability Group is not encrypted, then enabling the TDE is slightly simpler than the Case 2.
When you turn on the process of database encryption on the Primary replica, each of the Secondary replicas will also initiate the process of encryption. You don’t have to explicitly turn on encryption for the Secondary replica. If the database size is large, you may have to wait for few hours before the database completes the encryption process on the Secondary replicas.
Follow the below steps if you are dealing with Case 1.
Step 1: On Primary Replica - Creation of the Database MASTER KEY (DMK)
The database master key (DMK) is a symmetric key which safeguards the private keys of the certificates existing on the databases. The master key is encrypted using the AES_256 algorithms along with a password.
USE MASTER GO -- Create a Master Key CREATE MASTER KEY ENCRYPTION BY Password = '<password>';
It is a good practice to perform an immediate backup of the Master Key once the database master key is created and is recommended to perform the back up on a secured location.
-- Backup the Master Key BACKUP MASTER KEY TO FILE = '\\<PRIMARYSERVERNAME>\E$\MSSQL\TDE\<PRIMARYSERVERNAME>_BACKUPKEY' ENCRYPTION BY Password = '<password>';
Step 2: On Primary Replica - Creation of the CERTIFICATE
Certificates are used in Transparent Data Encryption to secure the Database Encryption Key. If you refer step 3, you will find that Database Encryption Key uses the certificate for encryption. Remember to keep a note of certificate that is used to secure the Database Encryption Key.
-- Create Certificate Protected by Master Key CREATE Certificate <PRIMARYSERVERNAME>_CERT WITH Subject = 'TDE_CERT';
The backup files of TDE enabled databases are also encrypted by using the database encryption key and in turn, the certificates secure the DEK. Consequently, the backup of the certificate is required when you are restoring the TDE enabled database. You will observe that this command will create two files; a) Certificate b) Private Key
-- Backup the Certificate BACKUP Certificate <PRIMARYSERVERNAME>_CERT TO FILE = '\\<PRIMARYSERVERNAME>\E$\MSSQL\TDE\<PRIMARYSERVERNAME>_BACKUPCERT' WITH Private KEY (FILE = '\\<PRIMARYSERVERNAME>\E$\MSSQL\TDE\<PRIMARYSERVERNAME>_PRIVKEY', ENCRYPTION BY Password = '<password>');
Step 3: On Primary Replica - Creation of Database Encryption Key (DEK)
Before you enable the TDE, database encryption key needs to be created. DEK is used to encrypt the actual database content including the log. It is a symmetric key and you can create using available AES algorithms. In this document AES_128 is being used.
USE TDE_Database GO -- Create a Database Encryption Key CREATE DATABASE ENCRYPTION KEY WITH Algorithm = AES_128 ENCRYPTION BY Server Certificate <PRIMARYSERVERNAME>_CERT;
If there are multiple databases you can do so in the same step 3, just change the name of the database. Note that database encryption key needs to be only created on Primary, as the AlwaysOn synchronization process will take care of the secondary replicas.
Step 4: On Secondary Replica - Creation of the DMK
In Case 1, as the database is also available on the secondary replica, the DMK also needs to be created, unless there is one existing already. Verify if the DMK already exists using the below query.
USE MASTER; GO SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##'
If the DMK exists, you need not create one on Secondary replica. If it does not exist, use the below to create a DMK.
USE MASTER GO -- Create a Master Key, I use the same password as of primary for simplifying it, however, you do not need to have the same password on each instance. CREATE MASTER KEY ENCRYPTION BY Password = '<password>';
As the secondary replica, may become the primary replica in the event of a failover, it is a good practice to perform an immediate backup of the Master Key on the secondary replica, too. It is recommended to perform the backup to a secured location..
-- Backup the Master Key on secondary in order we have this when it turns into Primary and we need not worry when recovery event occurs. BACKUP MASTER KEY TO FILE = '\\<SECONDARYSERVERNAME>\E$\MSSQL\TDE\<SECONDARYSERVERNAME>_BACKUPKEY' ENCRYPTION BY Password = '<password>';
Step 5: On Secondary Replica - Creation of the Certificate from the Primary Certificate Backup
If the certificate does not exist, you will need to create one on the secondary replica. You can verify from the sys.certificates table.
-- Create Certificate Protected by Master Key
CREATE CERTIFICATE <SECONDARYSERVERNAME>_CERT
FROM FILE = '\\<PRIMARYSERVERNAME>\E$\MSSQL\TDE\<PRIMARYSERVERNAME>_BACKUPCERT'
WITH Private KEY (
FILE = '\\<PRIMARYSERVERNAME>\E$\MSSQL\TDE\<PRIMARYSERVERNAME>_PRIVKEY',
Decryption BY Password = '<password>');Next, take a back up the certificate on secondary replica using the below command.
-- Backup the Certificate BACKUP Certificate <SECONDARYSERVERNAME>_CERT TO FILE = '\\<SECONDARYSERVERNAME>\E$\MSSQL\TDE\<SECONDARYSERVERNAME>_BACKUPCERT' WITH Private KEY (FILE = '\\<SECONDARYSERVERNAME>\E$\MSSQL\TDE\<SECONDARYSERVERNAME>_PRIVKEY', ENCRYPTION BY Password = '<password>');
Step 6: On Primary Replica – Enabling TDE Encryption
The last step is to enable the TDE in the required database. Use the below command to enable TDE.
ALTER DATABASE TDE_Database SET ENCRYPTION ON
If there are multiple databases you can do so in the same step 6, just change the name of the database. Once you execute the above query, the SQL Server will begin encrypting the database. You will now need to monitor its completion.
Step 7: On the Primary Replica and Secondary Replicas – Monitoring Encryption
Run the below queries on both Primary and Secondary replicas.
USE MASTER; GO SELECT db.name,db.is_encrypted,dm.encryption_state,dm.percent_complete, dm.key_algorithm,dm.key_length FROM sys.databases db LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id; GO
Column Information
The data returned is explained here.
Is_encrypted
A return value of "1" for the is_encrypted column of the sys.databases catalog view indicates that the database has been encrypted successfully through TDE.
Encryption_State
A return value of "2" in this column indicates that the encryption process is in progress. A return value of "3" indicates that the encryption process is complete.
Percent_Complete
The percent_complete indicates the percent progress of the encryption process.
Note: If the database is large in size and you are running this query on Secondary replica, you will notice the percent_complete will be displayed “0” and encryption_state to be “2” until the entire AlwaysOn sync is complete. It typically takes few hours on the secondary to change the encryption_state to “3”. You will need to be patient.
CASE 2: Database is encrypted on Primary Replica and is NOT part of the Availability Group or NOT on Secondary Replicas.
The Add Database Wizard and New Availability Group Wizard for AlwaysOn Availability Groups, by default, do not show the databases that are already encrypted or even if the database contains a Database Encryption Key (DEK).
Case 2 needs to be handled differently, but it is indeed possible. There are various steps and requires more effort than Case 1. You have a choice to reverse the encryption on that database and then follow the Case 1 OR instead follow the steps as described below.
Step 1: On Secondary Replica – Verifying existence of the DMK
Before you create a Database Master Key, you may want to verify if the DMK already exists. You can use the below query to check it.
USE master; GO SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##'
If DMK exists, you are good, and you need not create one on Secondary replica. If it does not exist, use the below to create a DMK.
USE MASTER; GO -- Create a Master Key, I use the same password as of primary for simplifying it, however, you do not need to have the same password on each instance. CREATE MASTER KEY ENCRYPTION BY Password = '<password>';
Step 2: On Primary Replica – Backup TDE certificate
You need to know which certificate needs to be backed up. Below is the query to identity that.
USE master; GO SELECT db_name(database_id) DatabaseName, cer.name as CertificateName FROM sys.dm_database_encryption_keys dek INNER JOIN sys.certificates cer ON dek.encryptor_thumbprint = cer.thumbprint WHERE db_name(database_id) ='<TDE_DATABASE>'
Once you identify the certificate, next step is to back up the certificate using the below command. You will observe that this command will create two files; a) Certificate b) Private Key.
-- Backup the Certificate BACKUP Certificate <PRIMARYSERVERNAME>_CERT TO FILE = '\\<PRIMARYSERVERNAME>\E$\MSSQL\TDE\<PRIMARYSERVERNAME>_BACKUPCERT' WITH Private KEY (FILE = '\\<PRIMARYSERVERNAME>\E$\MSSQL\TDE\<PRIMARYSERVERNAME>_PRIVKEY',ENCRYPTION BY Password = '<password>');
Step 3: On Secondary Replica - Create a CERTIFICATE from the Primary Certificate Backup with Private Key
A TDE certificates needs to be created on the secondary replica using the backed up certificate from Primary replica location.
-- Create Certificate Protected by Master
Key
CREATE CERTIFICATE
<SECONDARYSERVERNAME>_CERT
FROM FILE = '\\<PRIMARYSERVERNAME>\E$\MSSQL\TDE\<PRIMARYSERVERNAME>_BACKUPCERT'
WITH Private KEY (
FILE = '\\<PRIMARYSERVERNAME>\E$\MSSQL\TDE\<PRIMARYSERVERNAME>_PRIVKEY',
Decryption BY Password = '<password>');Step 4: On Primary Replica – Add Database to the Availability Group
When you add a database, it becomes part of the availability group as a primary database only. It’s an indication to the SQL Server that, this database will be sooner or later be available on secondary replica.
ALTER AVAILABILITY GROUP <AGNAME> ADD DATABASE <TDE_DATABASE>
Step 5: On Primary Replica - Create a FULL Database Backup
We need a full backup to initialize the secondary database.
BACKUP DATABASE <TDE_Database> TO DISK = N'<DBFullBackupPath>' WITH NOFORMAT, INIT, NAME = N'<BackUpSetName>', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Step 6: On Secondary Replica - Restore the FULL Database Backup
You may be required to copy the backup on the secondary replica.
RESTORE DATABASE <TDE_DATABASE> FROM DISK = N'<DBFullBackupPath>' WITH NORECOVERY, NOUNLOAD, STATS = 5
You will notice on the Secondary that the TDE_Database will be in the RESTORING state, as we are required to use NORECOVERY during this process.
Step 8: On Primary Replica - Create a Transaction Log Backup
We will use a transaction log backup to ensure we can sync up the secondary database.
BACKUP LOG <TDE_DATABASE> TO DISK = N'<DBTranLogBackUpPath>' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5
Step 9: On the Secondary Replica – Restore the Transaction Log Backup
You may be required to copy the transaction log backup on the secondary replica.
RESTORE LOG <TDE_DATABASE> FROM DISK = N'<DBTranLogBackUpPath>' WITH NORECOVERY, NOUNLOAD, STATS = 5
You will notice on secondary that the TDE_Database will still be in RESTORING state, as we are required to use NORECOVERY during this process.
Step 10: On Secondary Replica – Join to the Availability Group
You have to join the databases to the Availability Group. This step is different from adding the database into Availability Group.
USE master; go ALTER DATABASE TDE_Database SET HADR AVAILABILITY GROUP = <AGNAME>;
Once you join the database into Availability Group, the data synchronization is initiated, by which any changes on Primary will be synchronized to Secondary by means of sending transaction log records to secondary. The database status will be ONLINE on Secondary with either Synchronized or Synchronizing depending on the Availability Mode configured on the Availability Group.
You can now use the monitoring encryption query to verify the encryption completion on the secondary replica. This completes your TDE on the databases on secondary replica.
Notes
A few items to be aware of.
- It is not necessarily required to have the exact same DMK on each replica.
- It is not necessarily required to back up the DMK from Primary and restore it on secondary replica.
- The DMK is used to encrypt the CERTIFICATES and not used for encrypting the databases.
- It is not necessarily required to have the same password on each replica.
- The CREATE MASTER KEY command can be used on each replica with separate passwords.
- Even if you use same passwords, the keys will be different as it is independent to the replica.
- If you don’t have the password for the Private Key for a previously backed up certificate, it is okay. However, you need to take a fresh backup with a new password and then secure it.
