SQL Server 2019企业-高可用性与主密钥

Question

我有一个SQL Server 2019企业HA系统，它有一个加密的主密钥，每次我必须故障转移到辅助数据库(反之亦然)时，我必须运行此脚本，以便在故障转移后使辅助工作。

DROP SYMMETRIC KEY DataProtectionKey 
DROP CERTIFICATE DataProtection
DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My_encryption_key'
CREATE CERTIFICATE DataProtection WITH SUBJECT = 'Data Protection'
CREATE SYMMETRIC KEY DataProtectionKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE DataProtection

USE [MYDATABASE]
GO
ALTER MASTER KEY FORCE REGENERATE 
WITH ENCRYPTION BY PASSWORD = 'My_encryption_key'
GO

我也试图使用失败后，看看我是否可以最终插入主密钥和解决所有的问题这个脚本。

USE [MYDATABASE]
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'My_encryption_key';
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY;

不幸的是，每次在DB上失败时，我都必须运行两个脚本中的一个。

有什么办法可以让我一次性完成这个任务吗?

谢谢

Answer 1

您可以尝试在主服务器上创建证书，在备份服务器上创建证书，并在辅助服务器上还原证书。

这段代码来自我的repo，是为使用docker创建的AlwaysOn演示而创建的。

主要

-- PRIMARY
USE [master]
GO
-- Create masterkey
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd!';
GO
-- Create certificate
CREATE CERTIFICATE ao_certificate WITH SUBJECT = 'ao_certificate';
GO
BACKUP CERTIFICATE ao_certificate
TO FILE = '/var/opt/mssql/shared/ao_certificate.cert'
WITH PRIVATE KEY (
FILE = '/var/opt/mssql/shared/ao_certificate.key',
ENCRYPTION BY PASSWORD = 'P@ssw0rd!'
);
GO

二级

-- SECONDARY
USE [master]
GO
-- Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd!';
GO
-- create certificate with private key created on primary server and saved to shared location
CREATE CERTIFICATE ao_certificate
FROM FILE = '/var/opt/mssql/shared/ao_certificate.cert'
WITH PRIVATE KEY (
FILE = '/var/opt/mssql/shared/ao_certificate.key',
DECRYPTION BY PASSWORD = 'P@ssw0rd!'
)
GO

Answer 2

是的，修复相对简单。从辅助副本获取服务主密钥(SMK)的备份，并将其恢复到主副本。这将使用恢复的SMK重新加密已经用现有SMK加密过的任何内容。由于两个副本之间的SMK现在是相同的，因此故障转移将是无缝的。

从次要到主的方向的原因是重新加密执行写(虽然不是很多)到用户数据库，这只允许在主副本上。

Answer 3

我团队的一位同事找到了一个更好的解决方案。

1. 在主数据库上，我将使用创建数据库时使用的加密密码将DBS1备份为主密钥:

   BACKUP SERVICE MASTER KEY TO FILE = 'D:Backupdbs1_service_master_key.key' ENCRYPTION BY PASSWORD = 'Wh@t3v3r_P@$$w0rd';
   

2. 在HA上将备用DB提升为主DB(故障转移)，这样您就可以运行下面的脚本:

   USE [YOURDATABASE]
   GO
   open master key decryption by password = 'Wh@t3v3r_P@$$w0rd'
   Alter Master Key Add encryption by Service Master Key
   GO
   

3. 复制备份在主DBS1上的服务主密钥，并复制到DBS2上的某个位置。仍然在"二级"上。现在在HA组上为主的DBS，恢复证书:

   RESTORE SERVICE MASTER KEY FROM FILE = 'D:Backupdbs1_service_master_key.key' DECRYPTION BY PASSWORD = 'Wh@t3v3r_P@$$w0rd';