Caution must be exercised when mixing Transparent Data Encryption (TDE) and Availability Groups to ensure databases don't end up in a suspect state on the secondary node.
Adding TDE to a database in an Availability Group
If a database is already part of an Availability Group, the certificate used to encrypt the database needs to be created on both nodes with the same thumbprint before the database encryption is turned on.
Create master key in master database on both nodes
USE MASTER
GO
CREATEMASTERKEYENCRYPTIONBYPASSWORD='Bollo_20140724';
Create certificate on primary node
USEmaster
GO
CREATECERTIFICATETDECertificate
WITHSUBJECT='SQL Server TDE Certificate';
Take a backup of certificate and copy to secondary node
BACKUPCERTIFICATETDECertificate
TOFILE='C:\TDECertificate.cer'
WITHPRIVATEKEY
(FILE='C:\TDECertificate.PVK',
ENCRYPTIONBYPASSWORD='Higuma_20140724')
Restore on secondary node and confirm the thumbprints are the same
CREATECERTIFICATETDECertificate
FROMFILE='C:\TDECertificate.cer'
WITHPRIVATEKEY (FILE='C:\TDECertificate.PVK',
DECRYPTIONBYPASSWORD='Higuma_20140724')
Create database encryption key and start encryption
USETest
GO
CREATEDATABASEENCRYPTIONKEY
WITHALGORITHM=AES_256
ENCRYPTIONBYSERVERCERTIFICATETDECertificate;
USE test
ALTER DATABASE test
SET ENCRYPTION ON
Adding a TDEed database to an Availability Group
If a TDE enabled database is yet to be added to an Availability Group,
the certificate used to protect the database encryption key needs to be created on the secondary node with the same thumbprint before the database is added to the Availability Group. The steps are very similar to previous point.
Take a backup of certificate from the instance with the TDE enabled database.
/*on Primary Instance*/
BACKUPCERTIFICATETDECertificate
TOFILE='C:\TDECertificate.cer'
WITHPRIVATEKEY
(FILE='C:\TDECertificate.PVK',
ENCRYPTIONBYPASSWORD='Higuma_20140724')
Copy to the secondary node and restore.
/*on Secondary Instance*/
CREATECERTIFICATETDECertificate
FROMFILE='C:\TDECertificate.cer'
WITHPRIVATEKEY (FILE='C:\TDECertificate.PVK',
DECRYPTIONBYPASSWORD='Higuma_20140724')
Using the database wizard to add the database to the availability group will give an error, stating the database contains an encryption key. As the error message says, use TSQL commands to add the database to the availability group instead.
/*On principal instance*/
USE Master
GO
ALTER AVAILABILITY GROUP <AVGroup> ADD DATABASE test;
/*On secondary instance*/
USE Master
GO
ALTER DATABASE test SET HADR AVAILABILITY GROUP = <AVGroup>;
The encrypted database will now be part of the Availability Group.
The INCORRECT way
Its important to make sure the certificate used to protect the database encryption key is restored onto the secondary node before turning on encryption for a database in an Availability Group. Failing to do will cause the database to turn suspect on the secondary node.
The following steps demonstrate the how to create a suspect database on the secondary node. The TSQL commands from previous steps can be used.
- Add non-TDE enabled database to Availability Group
- Create the master key on the Primary Instance
- Create the certificate on the Primary Instance
- Create the database encryption key in the database added to the Availability Group
- Enable TDE in the database on the Primary Instance
Querying sys.databases on the Primary Instance returns....
the same query on the Secondary Instance returns...
Trying the drop database will give the following error...
Its possible to remove the database from the Availability Group, which changes the database state to restoring but still not possible to drop the database. The same error is returned. Its also possible to restore the certificate from the Primary Instance but this doesn't allow the database to be dropped.
Unfortunately, the only way to remove the restoring\suspect database from the secondary instance is to stop the SQL service, (re)move the database and transaction log files and restart the SQL service.
