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
CREATE
MASTER
KEY
ENCRYPTION
BY
PASSWORD
=
'Bollo_20140724';
Create certificate on primary node
USE
master
GO
CREATE
CERTIFICATE
TDECertificate
WITH
SUBJECT
=
'SQL
Server TDE Certificate';
Take a backup of certificate and copy to secondary node
BACKUP
CERTIFICATE
TDECertificate
TO
FILE
=
'C:\TDECertificate.cer'
WITH
PRIVATE
KEY
(FILE
=
'C:\TDECertificate.PVK',
ENCRYPTION
BY
PASSWORD
=
'Higuma_20140724')
Restore on secondary node and confirm the thumbprints are the same
CREATE
CERTIFICATE
TDECertificate
FROM
FILE
=
'C:\TDECertificate.cer'
WITH
PRIVATE
KEY
(FILE
=
'C:\TDECertificate.PVK',
DECRYPTION
BY
PASSWORD
=
'Higuma_20140724')
Create database encryption key and start encryption
USE
Test
GO
CREATE
DATABASE
ENCRYPTION
KEY
WITH
ALGORITHM
=
AES_256
ENCRYPTION
BY
SERVER
CERTIFICATE
TDECertificate;
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*/
BACKUP
CERTIFICATE
TDECertificate
TO
FILE
=
'C:\TDECertificate.cer'
WITH
PRIVATE
KEY
(FILE
=
'C:\TDECertificate.PVK',
ENCRYPTION
BY
PASSWORD
=
'Higuma_20140724')
Copy to the secondary node and restore.
/*on Secondary Instance*/
CREATE
CERTIFICATE
TDECertificate
FROM
FILE
=
'C:\TDECertificate.cer'
WITH
PRIVATE
KEY
(FILE
=
'C:\TDECertificate.PVK',
DECRYPTION
BY
PASSWORD
=
'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.
