Introduction
Transparent Data Encryption is an SQL DB encryption technique. In TDE, primary and secondary data files are encrypted using an encryption key with the help of Database Master Key and TDE Certificate. This is the simplest form of encryption and it has been provided by default in SQL Server 2016.
Always On Availability Group is an HADR (High Availability Disaster Recovery) technique which is introduced in SQL Server 2012. It provides an alternative to Failover Clustering (High Availability) and Mirroring (Disaster Recovery). In simple words, when the database is moved in to the Availability group, two or more physical copies of a database reside on two or more different Servers, which enables us to sync data automatically between the primary and secondary database.
In this article, I will explain the steps involved in implementation of TDE on database that is stored in the Availability Group.
Assumptions
Reader is familiar with the basics of Always On environment and has basic knowledge of different TDE terminologies.
Implementation Steps
Let us assume that the database TestTDEInAO which we are targeting is in Availability Group.
Remove Database from Availability Group
- Connect to Primary node from SSMS.
- Open Object Explorer.
- Disable _SQL_BackupAll and _SQL_BackupTranAll jobs from SQL Server agent on both, primary and secondary nodes if enabled.
- Expand the Always On High Availability node and the Availability Group's node.
- Select the availability group, and expand the Availability Databases node.
- Right click on TestTDEInAO. In menu, select Remove Database from Availability Group command.
- In the Remove Databases from Availability Group dialog box, make sure only TestTDEInAO database is listed.
- Click OK.
Encrypting Primary copy of the Database
- Generating, Encrypting, and Backing up the Master key
- Creating and Backing up certificate
- Create a certificate that's protected by the master key. Execute the following command in query window.
- USE master;
- OPEN MASTER KEY DECRYPTION BY PASSWORD ='<Password>';
Where PASSWORD =’<Password>’ used for encryption of the master key.
- CREATE CERTIFICATE <CertificateName>
- WITH SUBJECT='<SubjectName>',
- EXPIRY_DATE ='01-01-3000'
Ex- CREATE CERTIFICATE TDECertificate
WITH SUBJECT = 'TDE Certificate for TestTDEInAO‘
- Create a Database Encryption Key and Encrypting Database
Follow below steps to create and secure Database Encryption Key with the help of certificate.
- Open New Query window and use the target database.
- Create Database Encryption key Encrypted by server certificate as shown in below query.
- CREATE DATABASE ENCRYPTION KEY
- WITH ALGORITHM = AES_128
- ENCRYPTION BY SERVER CERTIFICATE <CertificateName>
- Encrypt the database by executing following command in query window.
- ALTER DATABASE <TestTDEInAO>
- SET ENCRYPTION ON;
- Check the status of the encryption by running the below query.
- USE master
- SELECT db_name(database_id) as 'database name' , encryption_state, percent_complete
- FROM sys.dm_database_encryption_keys;
- GO
Where encryption state,
2 =Encryption in progress
3=Encrypted
Once the database encryption state turn to Encrypted, we can say that our database is encrypted.
Backup and Restoration on Secondary Node
In this step, we will be backing up Master key, Certificate, and Database along with transactional logs as well and we will be restoring these elements on secondary node/s.
- Restoring Master key and Certificate
- Restore the backed-up certificate by executing below commands.
- USE Master;
- OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password'
-
- CREATE CERTIFICATE <CertificateName>
- FROM FILE = 'Location\CertName.cer' WITH PRIVATE KEY ( FILE = 'Location\CertKeyName.key', DECRYPTION BY PASSWORD = 'encryption_password');
- GO
- Where PASSWORD = 'Password’ is the password used for encrypting the master key in the previous step.
- and PASSWORD = 'encryption_password' is the password that is used to decrypt the private key and is same that was used while encrypting during back-up of the certificate.
- Alter the Master key to add encryption by service master key.
- USE master
- OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password'
- ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
- GO
-
- Where PASSWORD = 'Password' is the password used to encrypt the master key.
- Restoring Encrypted Database from Primary to Secondary node
- Take a full backup and transactional backup of TestTDEInAO from Primary node. Recommended is to take backup in multi file mode.
- On Secondary node same backup should be restored in NO RECOVERY mode
Bring Database in Availability Group
Now, we have encrypted database on primary as well as secondary servers. Now, we will bring these servers back in Availability Group by using TSQL.
- Add Database on Primary node to Availability Group
- Refer below script to add the existing database in AG
- ALTER AVAILABILITY GROUP MyAvailabilityGroup ADD DATABASE TestTDEInAO;
- GO
- Join Secondary node Database to Availability Group
- Refer below script to join the secondary server database to the AG
- ALTER DATABASE TestTDEInAO SET HADR AVAILABILITY GROUP = MyAvailabilityGroup;
- GO
Once the database syncing is completed, you will have achieved your goal.!!!!