SQL Server 透明数据加密TDE for AlwaysOn

 上篇留下了一个问题，如何将已启用TDE的数据库加入AG？

实际上TDE for AlwaysOn分两种情况：

• 对已在AG中的数据库启用TDE
• 已启用TDE的数据库加入AG（更复杂）

注意如果在数据库镜像、日志传送、AlwaysOn中使用TDE，则主从数据库都将被加密，不必显式启用辅助数据库加密，事务日志在它们之间发送时将被加密。

一、 对已在AG中的数据库设置TDE

注意以下步骤如果已经有的则不需要再建，否则会报错

1. 主节点建DMK 

无域alwayson已创建，此步可忽略

USE MASTER
GO
-- Create a Master Key
CREATE MASTER KEY ENCRYPTION BY Password = '<password>';

-- Backup the Master Key
BACKUP MASTER KEY
TO FILE = '\\<PRIMARYSERVERNAME>\E$\MSSQL\TDE\<PRIMARYSERVERNAME>_BACKUPKEY'
ENCRYPTION BY Password = '<password>';

2. 主节点建证书

无域alwayson已创建，此步可忽略

-- Create Certificate Protected by Master Key
CREATE Certificate TDECert WITH Subject = 'TDE_CERT';

-- Backup the Certificate
BACKUP Certificate TDECert
  TO FILE = 'C:\TDE\TDECert_backup'
WITH Private KEY (FILE = 'C:\TDE\TDECert_key',
ENCRYPTION BY Password = 'xxx');

3. 主节点建DEK

USE testag
GO
-- Create a Database Encryption Key
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO

4. 从节点创建及备份DMK

无域alwayson已创建，此步可忽略

-- 检查DMK是否已存在
USE MASTER;
GO
SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##'

-- 若不存在则创建
CREATE MASTER KEY ENCRYPTION BY Password = '<password>';

-- 备份DMK
BACKUP MASTER KEY
TO FILE = '\\<SECONDARYSERVERNAME>\E$\MSSQL\TDE\<SECONDARYSERVERNAME>_BACKUPKEY'
ENCRYPTION BY Password = '<password>';

5. 从节点创建及备份证书

无域alwayson已创建，此步可忽略

-- Create Certificate Protected by Master Key
CREATE CERTIFICATE TDECert   
FROM FILE = 'C:\TDE\TDECert_backup'    
WITH PRIVATE KEY (FILE = 'C:\TDE\TDECert_key',   
DECRYPTION BY PASSWORD = 'xxx');
GO

-- Backup the Certificate
BACKUP Certificate TDECert
  TO FILE = 'C:\TDE\TDECert_backup'
WITH Private KEY (FILE = 'C:\TDE\TDECert_key',
ENCRYPTION BY Password = 'xxx');

6. 主节点启用TDE

ALTER DATABASE testag SET ENCRYPTION ON;

7. 检查加密情况

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;

主节点

从节点

二、 已启用TDE的数据库加入AG

更复杂，无法使用向导工具将TDE加密库加入AG，只能手动添加。

1. 主节点建DMK

无域alwayson已创建，此步可忽略

-- 主节点是否存在DMK
USE master;
GO
SELECT * FROM  sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##'
-- 若不存在则创建
CREATE MASTER KEY ENCRYPTION BY Password = '<password>';

2. 主节点备份证书

无域alwayson已创建，此步可忽略

-- 证书是否存在
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>'

-- 备份证书
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>');

3. 从节点利用主节点的证书备份创建新证书

需要先将主库证书拷贝至从库再创建，无域alwayson已创建，此步可忽略

-- Create Certificate Protected by Master
CREATE CERTIFICATE TDECert   
FROM FILE = 'C:\TDE\TDECert_backup'    
WITH PRIVATE KEY (FILE = 'C:\TDE\TDECert_key',   
DECRYPTION BY PASSWORD = 'xxx');
GO

4. 主节点将DB加入AG

ALTER AVAILABILITY GROUP <AGNAME> ADD DATABASE <TDE_DATABASE>;

5. 主节点做DB全备+日志备份

全备

日志备份

备份文件传至备库

6.  从节点，还原全备及日志备份

还原全备，注意选项选restore with norecovery

db状态变为正在还原

还原事务日志，注意选项同样选restore with norecovery

此时DB状态还是正在还原

7.  从节点将DB加入AG

USE master;
go
ALTER DATABASE PlanError SET HADR AVAILABILITY GROUP = TESTAG;

测试也可看到数据已同步

参考

https://www.sqlservercentral.com/articles/enabling-tde-on-databases-in-an-alwayson-scenario

http://mysql.taobao.org/monthly/2016/05/06/