SQL Server AlwaysOn环境配置数据库TDE

Enabling TDE on Databases in an AlwaysOn Scenario

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