SQL Server非域（跨域）环境下镜像（Mirror）的搭建步骤及注意事项

2022-09-19 14:25:54

在实际的生产环境下，我们经常需要跨域进行数据备份，而创建Mirror是其中一个方案。但跨域创建Mirror要相对复杂的多，需要借助证书进行搭建。

下面我们将具体的步骤总结如下：

第一部分创建证书

Step 1：创建数据库主密钥

主密钥的用处在这里是用于加密证书，当然主密钥不仅仅只有这个作用。对数据库主密钥的密码及存储保护要小心，这是实力级别的对象，影响面非常广。可以使用下面语句来创建：

USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';

通过系统表查看，确认

select top 100 is_master_key_encrypted_by_server,* from sys.databases

使用相同方式在镜像服务器创建数据库主密钥。

Step 2：创建证书，并用主密钥加密

创建证书时，默认在创建日期开始一年后过期，所以针对证书的创建，要注意其过期时间。下面是在“主体服务器”上创建HOST_P_cert证书的创建

USE master

CREATE CERTIFICATE Host_A_Cert

WITH Subject = 'Host_P Certificate',

Expiry_Date = '2050-1-1'; --过期日期

使用相同的方法在镜像服务器上实现对HOST_S_cert证书的创建

Step 3：创建端点

可以使用下面的代码在主体服务器中创建端点，并且指定使用5022,端口，端口在镜像配置过程中不强制使用特定端口（被占用或者特定端口如1433除外）。

--使用Host_A_Cert证书创建端点

IF NOT EXISTS ( SELECT 1

FROM sys.database_mirroring_endpoints )

BEGIN

CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022,

LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =

CERTIFICATE Host_A_Cert, ENCRYPTION = REQUIRED Algorithm AES, ROLE =

ALL );

END

在镜像服务器对证书名稍作修改，创建镜像服务器的端点。

Step 4：备份证书

备份证书的目的是发送到别的服务器并导入证书，以便别的服务器能通过证书访问这台服务器（主体服务器）。

BACKUP CERTIFICATE Host_A_Cert

TO FILE = 'D:\ShareFoldersMirror\Host_A_Cert.cer';

同理，在镜像服务器上重复一次，注意证书名和路径。备份之后可以在目标文件夹上看到有一个cer文件：

备份证书文件互相Copy至对方文件中。

Step 5：创建登录账号

针对每个服务器单独创建一个服务器登录账号，这里只需要创建一个登录给镜像服务器即可：

CREATE LOGIN Host_B_Login WITH PASSWORD = 'Pa$$w0rd';

同理，在镜像服务器上创建Host_A_Login给主体服务器。

Step 6：创建用户，并映射到Step 5中创建的登录账号中

在主体服务器上运行：

CREATE USER Host_B_User For Login Host_B_Login;

同理在镜像服务器也创建。

Step 7：使用证书授权用户

创建一个新的证书，并使用从伙伴服务器中复制过来的证书导入，然后映射step 6中的账号到这个新证书上。

CREATE CERTIFICATE Host_B_Cert

AUTHORIZATION Host_B_User

FROM FILE = 'D:\ShareFoldersMirror\Host_B_Cert.cer';

注意镜像服务器上也同样。

Step 8：把Step 5中的登录账号授权访问端口

GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Host_B_Login];

镜像服务器也一样。

到此为止，镜像所需的证书已经配置完毕。

第二部分备份还原数据库

如果是想按照界面操作的话，可以采用如下方式

step1 创建数据库，选择【还原文件和文件组】

step 2 手动输入目标数据库名称选择【源设备】，选择对应的文件

注意:本次还原是为Mirror做准备，所以，点击 【选项】 按钮。

所以需要选择【不对数据库执行任何操作，不回滚未提交的事务….】

还原成功。

第三部分启动镜像

依次分别在镜像Server和主Server上运行以下命令就可以了【最好在Master DB上执行以下命令】

在镜像Server上线运行

ALTER DATABASE [Test_Mirror]

SET PARTNER = 'TCP://172.XXX.XXX.93:5022';

在主Server上运行

ALTER DATABASE [Test_Mirror]

SET PARTNER = 'TCP://172.XXX.XX.106:5022';

配置成功，此时显示如下：

主体服务器上显示

镜像服务器上DB显示

第四部分补充部分

问题1 查询判断数据库是否已添加主密钥

---sys.databases的is_master_key_encrypted_by_server得到是否有加密

select top 100 is_master_key_encrypted_by_server,* from sys.databases

----如果没有就看不到数据【需定义到数据库】

----解释说明：##MS_ServiceMasterKey##----是说的整个服务，而##MS_DatabaseMasterKey## 是说的Master数据库，需留意。

----我们使用证书搭建镜像是需要在master数据库上创建数据库主密钥（如果主密钥不存在）。

SELECT * FROM sys.symmetric_keys

以下截图查询的数据显示Master数据库尚未创建主密钥。

以下截图的数据显示Master数据库已有主密钥

问题2 由日志传送更改为镜像。

希望直接更改，即不再需要备份和还原。

Step 1 【注意：此时先手动执行一下此DB的Log 备份的Job，然后停掉此Job，接下来再执行Copy Log 文件的Job（如果有此Job的话），再停掉此Job，最后执行Restore 此Log 文件的Job，接着停掉此Job】

Step 2 选择指定DB，取消【将此数据库启用为日志传送配置中的主数据库…】,就是把勾去掉。

Step 3 开始建立伙伴关系

先在备份Server的DB上去做

ALTER DATABASE [数据库名称]

SET PARTNER = 'TCP://172.XXX.XXX.6:10001';

然后再在主DB上运行

ALTER DATABASE [数据库名称]

SET PARTNER = 'TCP://172.XXX.XXX.4:10002';

问题3 在建立伙伴关系时，需注意设置伙伴的顺序

我们需要先在Mirror服务器上执行，然后再在主服务器中执行，则不报错。

否则，提示错误：

问题4 删除主密钥相关问题

删除主密钥需先删除由它加密的证书；而删除证书需要先删除由它映射的用户。

问题5 搭建前的检查项

（1）网络是否能联通，并且端口可用；（2）SQL Server数据库的恢复模式是否为完整；（3）SQL Server上是否有常规的备份作业，特别是日志备份是否已经暂停。

码农公寓

第一部分 创建证书