SQL Server高可用之镜像配置（使用见证服务器） - 重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

（七）其它前置条件

-- 若要建立镜像会话，伙伴双方和见证服务器（如果有）必须在相同版本的 SQL Server 上运行。
-- 确保两个伙伴（即主体服务器和镜像服务器）必须运行相同版本的 SQL Server。见证服务器（如果有）在任意支持数据库镜像的 SQL Server 版本上运行。
-- 数据库必须使用完整恢复模式。简单恢复模式和大容量日志恢复模式不支持数据库镜像。因此，镜像数据库的大容量操作始终被完整地记入日志。
-- 验证镜像服务器是否能为镜像数据库提供足够的磁盘空间。
-- 在镜像服务器上创建镜像数据库时，请确保指定相同数据库名称 WITH NORECOVERY 来还原主体数据库备份。另外，还必须通过 WITH NORECOVERY 应用在该备份执行后创建的所有日志备份。
-- 如果数据库镜像已经停止，则必须将对主体数据库执行的所有后续日志备份应用到镜像数据库中，然后才可以重新启动镜像。
-- 只能镜像用户数据库，不能镜像 master、 msdb、tempdb 或 model 数据库。
-- 镜像的数据库在数据库镜像会话过程中不能重命名。

一.准备工作

主服务器：192.168.133.202 实例名：SZTECH
辅助服务器：192.168.133.178 实例名：STANDBY
见证服务器：192.168.133.198 实例名：WITNESS
服务器版本：Windows Server 2019 非域环境
数据库版本：SQL Server 2019

二.创建数据库主密钥（主/备/见证机均执行）

USE master;
GO

-- 创建密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'oracle_4U';
GO

-- 查询密钥
SELECT * FROM sys.key_encryptions;
GO

4655369d9eb2530056.png (50.38 KB, 下载次数: 5)

下载附件

6 天前上传

三.创建证书

主机（创建名为master的证书）

USE master;
GO

CREATE CERTIFICATE master_cert
WITH SUBJECT = 'master certificate', EXPIRY_DATE = '2099-01-01';
GO

select * from sys.certificates --查看证书

604469d9eb2eaee95.png (165.36 KB, 下载次数: 5)

下载附件

6 天前上传

备机（创建名为slave的证书）
USE master;
GO

CREATE CERTIFICATE slave_cert
WITH SUBJECT = 'slave certificate', EXPIRY_DATE = '2099-01-01';
GO

select * from sys.certificates --查看证书

4802169d9eb32d8cda.png (165.74 KB, 下载次数: 5)

下载附件

6 天前上传

见证机（创建名为witness的证书）

USE master;
GO

CREATE CERTIFICATE witness_cert
WITH SUBJECT = 'witness certificate', EXPIRY_DATE = '2099-01-01';
GO

select * from sys.certificates --查看证书

5192469d9eb3668450.png (168.9 KB, 下载次数: 5)

下载附件

6 天前上传

四.创建镜像端点

主机执行
USE master;
GO

-- 如果存在之前创建的旧端点，先删除
SELECT * FROM sys.endpoints;
DROP ENDPOINT [端点名称];

CREATE ENDPOINT Ticket_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE master_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
);
GO

8612169d9eb46651d1.png (28.76 KB, 下载次数: 5)

下载附件

6 天前上传

备机执行
USE master;
GO

-- 如果存在之前创建的旧端点，先删除
-- SELECT * FROM sys.endpoints;
-- DROP ENDPOINT [端点名称];

CREATE ENDPOINT Ticket_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE slave_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
);
GO

8032169d9eb4a6c72b.png (26 KB, 下载次数: 5)

下载附件

6 天前上传

见证机执行
USE master;
GO

-- 如果存在之前创建的旧端点，先删除
-- SELECT * FROM sys.endpoints;
-- DROP ENDPOINT [端点名称];

CREATE ENDPOINT Ticket_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE witness_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
);
GO

6156569d9eb514a489.png (22.68 KB, 下载次数: 4)

下载附件

6 天前上传

五.备份证书

注意：先需要在各自机器上创建证书目录，再执行下面的命令

主机备份
USE master;
GO

BACKUP CERTIFICATE master_cert TO FILE = 'E:\cert\master_cert.cer';
GO

1478969d9eb598f741.png (15.99 KB, 下载次数: 4)

下载附件

6 天前上传

8632469d9eb5c47451.png (12.96 KB, 下载次数: 4)

下载附件

6 天前上传

备机备份
USE master;
GO

BACKUP CERTIFICATE slave_cert TO FILE = 'E:\cert\slave_cert.cer';
GO

2788469d9eb60730cb.png (15.38 KB, 下载次数: 4)

下载附件

6 天前上传

8282169d9eb6377016.png (12.9 KB, 下载次数: 4)

下载附件

6 天前上传

见证机备份
USE master;
GO

BACKUP CERTIFICATE witness_cert TO FILE = 'E:\cert\witness_cert.cer';
GO

9348269d9eb66d5413.png (16.55 KB, 下载次数: 5)

下载附件

6 天前上传

4709569d9eb68ea7ff.png (12.08 KB, 下载次数: 4)

下载附件

6 天前上传

六.交换证书文件

确保master_cert、slave_cert和witness_cert三个文件同时存在于主机、备机和见证机的 E:\cert 目录下：

5362469d9eb6e2fb84.png (23.9 KB, 下载次数: 4)

下载附件

6 天前上传

七.创建登录名（交叉创建）

主机上创建备机、见证机登录名
USE master;
GO

CREATE LOGIN slave_login WITH PASSWORD = 'oracle_4U';
GO

CREATE LOGIN witness_login WITH PASSWORD = 'oracle_4U';
GO

8387169d9eb74e897d.png (19.91 KB, 下载次数: 4)

下载附件

6 天前上传

备机上创建主机、见证机登录名
USE master;
GO

CREATE LOGIN master_login WITH PASSWORD = 'oracle_4U';
GO

CREATE LOGIN witness_login WITH PASSWORD = 'oracle_4U';
GO

700769d9eb787de73.png (18.95 KB, 下载次数: 4)

下载附件

6 天前上传

见证机上创建主机、备机登录名
USE master;
GO

CREATE LOGIN master_login WITH PASSWORD = 'oracle_4U';
GO

CREATE LOGIN slave_login WITH PASSWORD = 'oracle_4U';
GO

4451769d9eb7b60749.png (17.26 KB, 下载次数: 4)

下载附件

6 天前上传

八.创建用户（交叉创建）

主机上创建备机、见证机用户
USE master;
GO

CREATE USER slave FOR LOGIN slave_login;
GO

CREATE USER witness FOR LOGIN witness_login;
GO

4329469d9eb80c8cde.png (17.03 KB, 下载次数: 4)

下载附件

6 天前上传

备机上创建主机、见证机用户
USE master;
GO

CREATE USER master FOR LOGIN master_login;
GO

CREATE USER witness FOR LOGIN witness_login;
GO

8466769d9eb8471eb7.png (17.45 KB, 下载次数: 5)

下载附件

6 天前上传

见证机上创建主机、备机用户
USE master;
GO

CREATE USER master FOR LOGIN master_login;
GO

CREATE USER slave FOR LOGIN slave_login;
GO

4798769d9eb8a42846.png (18.62 KB, 下载次数: 5)

下载附件

6 天前上传

九.证书与用户关联（交叉关联）

主机关联备机、见证机证书
USE master;
GO

CREATE CERTIFICATE slave_cert
AUTHORIZATION slave
FROM FILE = 'E:\cert\slave_cert.cer';
GO

CREATE CERTIFICATE witness_cert
AUTHORIZATION witness
FROM FILE = 'E:\cert\witness_cert.cer';
GO

1011969d9eb8fdfaf2.png (23.05 KB, 下载次数: 4)

下载附件

6 天前上传

备机关联主机、见证机证书
USE master;
GO

CREATE CERTIFICATE master_cert
AUTHORIZATION master
FROM FILE = 'E:\cert\master_cert.cer';
GO

CREATE CERTIFICATE witness_cert
AUTHORIZATION witness
FROM FILE = 'E:\cert\witness_cert.cer';
GO

3987669d9eb9406f60.png (22.92 KB, 下载次数: 4)

下载附件

6 天前上传

见证机关联主机、备机证书
USE master;
GO

CREATE CERTIFICATE master_cert
AUTHORIZATION master
FROM FILE = 'E:\cert\master_cert.cer';
GO

CREATE CERTIFICATE slave_cert
AUTHORIZATION slave
FROM FILE = 'E:\cert\slave_cert.cer';
GO

6360969d9eb97d0d5f.png (28.74 KB, 下载次数: 4)

下载附件

6 天前上传

十.授予连接权限（交叉授权）

主机授予备机、见证机登录名连接端点权限
USE master;
GO

GRANT CONNECT ON ENDPOINT::Ticket_Mirroring TO [slave_login];
GO

GRANT CONNECT ON ENDPOINT::Ticket_Mirroring TO [witness_login];
GO

6963569d9eb9ecd119.png (22.76 KB, 下载次数: 4)

下载附件

6 天前上传

备机授予主机、见证机登录名连接端点权限
USE master;
GO

GRANT CONNECT ON ENDPOINT::Ticket_Mirroring TO [master_login];
GO

GRANT CONNECT ON ENDPOINT::Ticket_Mirroring TO [witness_login];
GO

2127069d9eba2160fd.png (20.91 KB, 下载次数: 4)

下载附件

6 天前上传

见证机授予主机、备机登录名连接端点权限
USE master;
GO

GRANT CONNECT ON ENDPOINT::Ticket_Mirroring TO [master_login];
GO

GRANT CONNECT ON ENDPOINT::Ticket_Mirroring TO [slave_login];
GO

612169d9eba528a08.png (22.98 KB, 下载次数: 4)

下载附件

6 天前上传

十一.备份主库并还原到备库

从主机上备份需要热备的数据库的数据库和事务日志，数据库一定要完整，然后把数据库和事务日志还原到备机

4443469d9eba886f5f.png (132.3 KB, 下载次数: 5)

下载附件

6 天前上传

1412169d9ebac700b2.png (134.52 KB, 下载次数: 4)

下载附件

6 天前上传

8864269d9ebb061562.png (20.17 KB, 下载次数: 4)

下载附件

6 天前上传

将备份文件传输到备库中并还原，一定要使用NORECOVERY模式，还原后备机数据库显示正在还原为正常现象

9381369d9ebb417043.png (141.97 KB, 下载次数: 4)

下载附件

6 天前上传