sql sever2016使用证书配置具有自动故障转移功能的数据库镜像，镜像服务器与见证服务器在同一台机器上

最新推荐文章于 2024-01-10 15:07:37 发布

大艺术家彼得潘

最新推荐文章于 2024-01-10 15:07:37 发布

阅读量248

点赞数

分类专栏： sql server 文章标签： sql 运维 sqlserver

本文链接：https://blog.csdn.net/weixin_54669165/article/details/134526469

版权

sql server 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

这段时间搞了数据库镜像，遇到了大大小小的问题。刚开始的时候是用域来搞的，本人对域不是很了解，然后就没弄出来，难搞得很。后面发现使用证书也可以搞，试了一下成功了，总结如下。

有关数据库镜像的相关概念可以查看微软官网介绍：数据库镜像 (SQL Server) - SQL Server Database Mirroring | Microsoft Learn

一、环境准备

1.服务器设计

主体服务器和镜像服务器实例需运行到相同版本的SQL SERVER上，见证服务器和镜像服务器是同一数据库的两个不同实例，现三台服务器设计如下：

主体服务器：

操作系统：Windows 10 专业版

SQL SERVER版本：Microsoft SQL Server Enterprise 2016

IP地址：192.168.3.210

SQL SERVER实例名：PRINCIPALSERVER

镜像服务器：

操作系统：Windows 10 专业版

SQL SERVER版本：Microsoft SQL Server Enterprise 2016

IP地址：192.168.3.211

SQL SERVER实例名：MIRRORSERVER

见证服务器：

操作系统：Windows 10 专业版

SQL SERVER版本：Microsoft SQL Server Enterprise 2016

IP地址：192.168.3.211

SQL SERVER实例名：WITNESSSERVER

2.网络检查

在配置数据库镜像前需检查服务器网络的连接情况，确保服务器的防火墙关闭，并且相互之间可以ping通。

3.查看及更改数据库的恢复模式

“恢复模式”是一种数据库属性，它控制如何记录事务，事务日志是否需要（以及允许）进行备份，以及可以使用哪些类型的还原操作。有三种恢复模式：简单恢复模式、完整恢复模式和大容量日志恢复模式。

简单恢复模式和大容量日志恢复模式不支持数据库镜像。数据库必须使用完整恢复模式。

查看数据库恢复模式：

--查看数据库xrdcmv5的恢复模式

SELECT name, recovery_model_desc

FROM sys.databases

WHERE name = 'xrdcmv5';

运行结果如下：

name	recovery_model_desc
xrdcmv5	FULL

由上述运行结果可知，数据库xrdcmv5的恢复模式为完整恢复模式（FULL）。如果恢复模式为简单恢复模式（SIMPLE）或大容量日志恢复模式（BULK_LOGGED），则需要更改恢复模式。

更改数据库恢复模式：

--更改数据库xrdcmv5的恢复模式

USE [master];

ALTER DATABASE [xrdcmv5]

SET RECOVERY FULL;

二、配置数据库镜像

数据库镜像有两种运行状态：同步数据库镜像（高安全性模式）和异步数据库镜像（高性能模式），其中高安全性模式包括：具有自动故障转移的高安全性模式和不带自动故障转移的高安全性模式。

本文使用证书配置具有自动故障转移的高安全性模式。

服务器需配置出站连接的进程和入站连接的进程，在可以建立入站连接之前，必须在每个服务器实例上配置出站连接。

出站分为以下基本步骤：

1.在 master 数据库中，创建数据库主密钥。

2.在 master 数据库中，为服务器实例创建加密证书。

3.使用服务器实例的证书为该服务器实例创建端点。

4.将证书备份到文件，并将其安全地复制到其他系统。

入站分为以下几个步骤：

1.为其他系统创建登录名。

2.创建一个使用该登录名的用户。

3.获取其他服务器实例的镜像端点的证书。

4.将该证书与在步骤 2 中创建的用户相关联。

5.授予对该镜像端点的登录名的 CONNECT 权限。

1.配置出站连接

1)创建主密钥

数据库主密钥 (DMK)是一种用于保护数据库中存在的证书私钥和非对称密钥的对称密钥。只有Windows登录名、SQL Server登录名和应用程序角色才能拥有对称密钥。

--创建主密钥，主服务器、镜像服务器和见证服务器都执行

Use master

Create master key encryption by password='$a123456'

2)创建证书

证书是在两个服务器之间共享的软件“密钥”，使用证书后，可以通过严格的身份验证实现安全通信。

主数据库上执行：

--创建主服务器证书

use master

create certificate mirror01_cert

with subject='mirror01 certificate',expiry_date='2099-1-1'

镜像数据库上执行：

--创建镜像服务器证书

use master

create certificate mirror02_cert

with subject='mirror02 certificate',expiry_date='2099-1-1'

见证数据库上执行：

--创建见证服务器证书

use master

create certificate mirror03_cert

with subject='mirror03 certificate',expiry_date='2099-1-1'

3)创建端点

数据库镜像端点使用传输控制协议 (TCP) 在参与数据库镜像会话或承载可用性副本的服务器实例之间发送和接收消息。如果某一给定计算机上的多个服务器实例要求数据库镜像端点，则为每个端点都指定不同的端口号。

主数据库上执行：

--创建主服务器端点

use master

create endpoint Endpoint_Mirroring

state=started

as tcp ( listener_port = 5022,listener_ip = all )

for database_mirroring ( authentication = certificate mirror01_cert, encryption = required algorithm aes, role = all )

在镜像数据库上执行：

--创建镜像服务器端点

use master

create endpoint Endpoint_Mirroring

state=started

as tcp ( listener_port = 5022,listener_ip = all )for database_mirroring ( authentication = certificate mirror02_cert, encryption = required algorithm aes, role = all )

在见证数据库上执行：

--创建见证服务器端点，见证服务器端口号不同于镜像服务器

use master

create endpoint Endpoint_Mirroring

state=started

as tcp ( listener_port = 5023,listener_ip = all )

for database_mirroring ( authentication = certificate mirror01_cert, encryption = required algorithm aes, role = all )

4)备份及拷贝证书

每台服务器都需要备份，并互相拷贝，保证每个服务器上都有3个证书。

主体服务器上执行：

--备份主数据库证书

use master

backup certificate mirror01_cert to file = 'D:\sqlServer\cert01\mirror01_cert.cer'

在镜像服务器上执行：

--备份镜像数据库证书

use master

backup certificate mirror02_cert to file = 'D:\sqlServer\cert02\mirror02_cert.cer'

在见证服务器上执行：

--备份见证数据库证书

use master

backup certificate mirror03_cert to file = 'D:\sqlServer\cert03\mirror03_cert.cer'

2.配置入站连接

1)创建登录名

需与证书关联，在主数据库创建镜像数据库和见证数据库的登录名，在镜像数据库创建主体数据库和见证数据库的登录名，在见证数据库中创建主体数据库和镜像数据库的登录名。

主体服务器上执行：

--创建镜像数据库的登录名

use master

create login mirror02_login with password='abc@123456'

--创建见证数据库的登录名

use master

create login mirror03_login with password='abc@123456'

在镜像服务器上执行：

--创建主数据库的登录名

use master

create login mirror01_login with password='abc@123456'

--创建见证数据库的登录名

use master

create login mirror03_login with password='abc@123456'

在见证服务器上执行：

--创建主数据库的登录名

use master

create login mirror01_login with password='abc@123456'

--创建镜像数据库的登录名

use master

create login mirror02_login with password='abc@123456'

2)创建使用登录名的用户

主体服务器上执行：

--创建镜像数据库的用户

use master

create user mirror02_user for login mirror02_login

--创建见证数据库的用户

use master

create user mirror03_user for login mirror03_login

镜像服务器上执行：

--创建主数据库的用户

use master

create user mirror01_user for login mirror01_login

--创建见证数据库的用户

use master

create user mirror03_user for login mirror03_login

见证服务器上执行：

--创建主数据库的用户

use master

create user mirror01_user for login mirror01_login

--创建镜像数据库的用户

use master

create user mirror02_user for login mirror02_login

3)证书与用户关联

主数据库上执行：

--证书与镜像数据库用户关联

use master

create certificate mirror02_cert

authorization mirror02_user

from file='D:\sqlServer\cert01\mirror02_cert.cer'

--证书与见证数据库用户关联

use master

create certificate mirror03_cert

authorization mirror03_user

from file='D:\sqlServer\cert01\mirror03_cert.cer'

镜像数据库上执行：

--证书与主数据库用户关联

use master

create certificate mirror01_cert

authorization mirror01_user

from file='D:\sqlServer\cert02\mirror01_cert.cer'

--证书与见证数据库用户关联

use master

create certificate mirror03_cert

authorization mirror03_user

from file='D:\sqlServer\cert02\mirror03_cert.cer'

在见证服务器上执行：

--证书与主数据库用户关联

use master

create certificate mirror01_cert

authorization mirror01_user

from file='D:\sqlServer\cert03\mirror01_cert.cer'

--证书与镜像数据库用户关联

use master

create certificate mirror02_cert

authorization mirror02_user

from file='D:\sqlServer\cert03\mirror02_cert.cer'

4)授予对远程数据库端点登录名的CONNECT权限

主体服务器上执行：

--授予镜像登录名CONNECT权限

use master

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror02_login];

--授予见证登录名CONNECT权限

use master

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror03_login];

镜像服务器上执行：

--授予主体登录名CONNECT权限

use master

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror01_login];

--授予见证登录名CONNECT权限

use master

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror03_login];

见证服务器上执行：

--授予主体登录名CONNECT权限

use master

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror01_login];

--授予镜像登录名CONNECT权限

use master

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror02_login];

3.数据库备份与还原

对需要做镜像的数据库做完整备份和事务日志备份，然后在镜像库上做还原，使用NORECOVERY模式。

主库上执行：

--备份数据库xrdcmv5

BACKUP DATABASE xrdcmv5

TO DISK = 'D:\sqlServer\Backup\Bk-Data\xrdcmv5.bak'

WITH FORMAT

--备份日志：

BACKUP LOG xrdcmv5

TO DISK = 'D:\sqlServer\Backup\Bk-Log\xrdcmv5-log.bak'

镜像数据库上执行：

需要先将数据库和日志手动复制备份到镜像服务器

--还原数据库文件

RESTORE DATABASE xrdcmv5

FROM DISK = 'D:\sqlServer\Backup\Bk-Data\xrdcmv5.bak'

WITH NORECOVERY,

MOVE 'xrdcm' TO 'D:\sqlServer\Backup\Bk-Data\xrdcmv5.mdf',

MOVE 'xrdcm_log' TO 'D:\sqlServer\Backup\Bk-Data\xrdcmv5_log.ldf';

--还原日志文件

RESTORE LOG xrdcmv5

FROM DISK ='D:\sqlServer\Backup\Bk-Log\xrdcmv5-log.bak'

WITH FILE=1, NORECOVERY

4.确认伙伴

镜像数据库上执行：

--确认主数据库伙伴

use master

ALTER DATABASE xrdcmv5

SET PARTNER = 'TCP://192.168.3.210:5022';

主库上执行：

--确认镜像伙伴

use master

ALTER DATABASE xrdcmv5

SET PARTNER = 'TCP://192.168.3.211:5022';

--确认见证服务器

use master

ALTER DATABASE xrdcmv5

SET WITNESS = 'TCP://192.168.3.211:5023';

三、测试镜像

完成数据库镜像的配置后，现模拟现场环境测试是否配置成功。在主数据库中“右键数据库”>“任务”>“数据库镜像监控器”启动数据库镜像监控器，以验证数据是否流动以及流动的情况。

1.自动故障转移

自动故障转移需要以下条件：

1.数据库已同步。

2.发生故障时所有三个服务器实例均处于连接状态，并且见证服务器和镜像服务器保持连接状态。

--查看镜像数据库状态，mirroring_state_desc的值需为SYNCHRONIZED（同步）

SELECT database_id,mirroring_state_desc,mirroring_role_desc,

mirroring_safety_level_desc

FROM sys.database_mirroring

WHERE database_id = DB_ID('xrdcmv5');

mirroring_state_desc表示镜像状态，(1)镜像状态为SYNCHRONIZING，说明镜像数据库的内容滞后于主体数据库的内容。主体服务器正在将日志记录发送到镜像服务器（正在将更改应用于镜像数据库以使其前滚）。(2)镜像状态为DISCONNECTED,说明伙伴已失去与其他伙伴的通信。(3)镜像状态为SYNCHRONIZED，说明镜像服务器与主体服务器几乎保持同步，主体服务器向镜像服务器发送更改，镜像服务器持续将更改应用于镜像数据库。此时事务安全为FULL,则同时支持自动故障转移和手动故障转移，并且在故障转移后不会丢失数据。

mirroring_safety_level_desc表示事务安全状态，数据库镜像运行模式为高安全性模式的事务安全状态为FULL。

在实际生产环境中，主体服务器丢失情况下，事务安全设置、数据库的状态以及见证服务器的状态对镜像会话行为的综合影响如下表所示：

事务安全	镜像状态	见证服务器状态	主体丢失时的行为
FULL	SYNCHRONIZED	CONNECTED	发生自动故障转移
FULL	SYNCHRONIZED	DISCONNECTED	镜像服务器将停止；无法进行故障转移，并且数据库不可用
OFF	SUSPENDED/DISCONNECTED	NULL（无见证服务器）	可以对镜像服务器进行强制服务（可能造成数据丢失）
FULL	SYNCHRONIZING/SUSPENDED	NULL（无见证服务器）	可以对镜像服务器进行强制服务（可能造成数据丢失）

现模拟主体服务器丢失情况下测试自动障碍转移，如果主体数据库宕机后，镜像数据库自动转换角色为主体数据库，则证明具有自动故障转移功能的数据库镜像配置成功。

本文通过停止主服务器的sql server服务来模拟主体数据库宕机，在主服务器的“开始”>“SQL Server 配置管理器”>“sql server服务”中找到运行的数据库实例右键停止。

此时在镜像服务器中查看数据库镜像运行状态，可以看到镜像数据库已经转换成主体数据库角色。

2.脱离镜像

脱离镜像后，伙伴间的关系将永远中断，每台伙伴服务器实例使用的数据库为数据库的单独副本。

--脱离镜像

alter database xrdcmv5 set partner off

由于镜像数据库是使用 RESTORE WITH NORECOVERY 创建的，因此镜像数据库的状态为 RESTORING。此时，可以删除以前的镜像数据库或使用WITH RECOVERY还原以前的镜像数据库。恢复该数据库时，由于恢复将启动新的恢复分支，因此将与以前的主体数据库不同。

--修改数据库的状态为Recovery

restore database xrdcmv5 with recovery

四、暂停和恢复数据库镜像

执行暂停操作将保留在挂起镜像时的会话状态，执行暂停操作将保留在挂起镜像时的会话状态，当出现瓶颈时，暂停可能有利于提高主体服务器的性能。

会话暂停后，主体数据库仍然可用。暂停操作将镜像会话的状态设置为 SUSPENDED，并且镜像数据库不再与主体数据库保持一致，由于在数据库镜像会话处于暂停时无法截断事务日志，因此，如果数据库镜像会话暂停的时间太长，事务日志将填满，导致数据库不可用。

在主体服务器或镜像服务器上执行：

--暂停数据库镜像

ALTER DATABASE TT SET PARTNER SUSPEND;

--恢复数据库镜像

ALTER DATABASE TT SET PARTNER RESUME;

五、删除镜像

1.删除端点

--查看所有端点

use master

select * from sys.endpoints

--删除端点

use master

drop endpoint Endpoint_Mirroring

2.删除证书

--查看所有证书：

use master

Select * from sys.certificates

--删除主体服务器证书：

use master

drop certificate mirror01_cert

--删除镜像服务器证书：

use master

drop certificate mirror02_cert

3.删除密钥

--查看所有主密钥：

use master

select * from sys.key_encryptions

--删除主密钥：

use master

drop master key

六、问题排查

1.镜像无法连接

启动数据库镜像监控器时，镜像状态显示检查数据库时出错，如下图所示:

在服务器上找到管理服务器实例连接。

将服务器实例连接方式由windows验证改为Sql Server身份验证即可。

2.查看数据库逻辑名

在配置数据库镜像的还原数据库文件的环节中，出现数据库逻辑名不对的报错提示。使用T-SQL语句查看数据库逻辑名。

--查看数据文件及日志文件逻辑名称

USE xrdcmv5;

SELECT name AS LogicalName

FROM sys.master_files

WHERE database_id = DB_ID('xrdcmv5');

七、后记

数据库镜像自动故障转移后，镜像服务器切换角色为主体服务器，这时候应用程序能不能跟着自动切换连接对象，我查了一下资料，有关信息可以看微软官网文章：SQL Server 最佳实践文章 |Microsoft 学习

发现好像是后端方面的工作，我就偷懒不搞了，以后有机会再研究。

大艺术家彼得潘

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
sql sever2016使用证书配置具有自动故障转移功能的数据库镜像，镜像服务器与见证服务器在同一台机器上

暂停操作将镜像会话的状态设置为 SUSPENDED，并且镜像数据库不再与主体数据库保持一致，由于在数据库镜像会话处于暂停时无法截断事务日志，因此，如果数据库镜像会话暂停的时间太长，事务日志将填满，导致数据库不可用。需与证书关联，在主数据库创建镜像数据库和见证数据库的登录名，在镜像数据库创建主体数据库和见证数据库的登录名，在见证数据库中创建主体数据库和镜像数据库的登录名。执行暂停操作将保留在挂起镜像时的会话状态，执行暂停操作将保留在挂起镜像时的会话状态，当出现瓶颈时，暂停可能有利于提高主体服务器的性能。
复制链接

扫一扫