作者：桦仔
10余年DBA工作经验
微信：debolop
QQ交流群：740052625
公众号：数据库实战派
背景
最近遇到一个客户需求，客户的生产环境有大量的SQL Server数据库（大概120套），客户的诉求是需要把生产环境的Windows系统转为Linux系统，转为Linux系统之后需要沿用原来的数据库高可用方案。把数据库转到Linux系统比较容易，因为从SQL Server 2017开始就支持Linux系统，但是要沿用原来的数据库高可用方案就有点难度。了解到客户之前的生产环境所用的数据库高可用方案是数据库镜像，数据库版本是SQL Server 2019，因为客户的生产环境高可用性要求不算太高，所以无论是数据库版本还是数据库镜像高可用方案都完全满足要求。
于是本人建议客户把SQL Server迁移到Linux系统之后，依然使用之前的数据库镜像这种数据库高可用方案。数据库镜像这种技术本身搭建非常简单，不需要借助任何第三方组件，搭建步骤跟Windows系统上的一模一样，而且从SQL Server 2005版本开始就已经引入数据库镜像，历经9个大版本迭代，稳定性久经考验。
在SQL Server 2016版本引入了多线程并行redo，它可以
（1）显著减少镜像集群和AlwaysOn可用性组集群的数据同步延迟。
（2）数据库的启动速度显著加快，数据库启动需要经历redo阶段。
（3）加快故障转移的速度
很多用户会嫌弃Linux系统上的AlwaysOn可用性组搭建麻烦，因为需要借助Pacemaker与Corosync等第三方组件，搭建过程有一定的难度。对于生产环境上的数据库高可用性和性能要求不是很高的情况下，完全可以使用数据库镜像来替代AlwaysOn可用性组。
搭建步骤
下面进行演示如何在Linux系统上搭建SQL Server数据库镜像，因为需要数据库自动故障转移能力，所以需要准备三台机器，第三台机器作为见证服务器，这个要求跟Windows系统上的部署方式是一模一样的。前提假设用户已经在Linux系统上安装好SQL Server数据库镜像。
环境要求
适用的数据库版本：SQL Server 2017 到 SQL Server 2022
适用的操作系统版本：CentOS 7.X 到 CentOS 9.X（其他Linux发行版也可以，Ubuntu或者SUSE）
Linux系统下的数据库目录结构如下：
/data/mssql/1433/database
/data/mssql/1433/dbbackup
/data/mssql/1433/dump
/data/mssql/1433/tempdb
正式开始搭建
修改hosts配置文件，三台机器都要同时修改
cat <<EOF >> /etc/hosts
192.168.22.122 wwwmssql122
192.168.22.124 wwwmssql124
192.168.22.128 wwwmssql128
EOF
--主机192.168.22.122
SELECT [name], [recovery_model_desc] FROM sys.[databases]
3. 在主服务器和镜像服务器上和见证服务器上创建Master Key 、创建证书 ，根据提示在各自的服务器上执行
--主机192.168.22.122上执行
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master@2015key123';CREATE CERTIFICATE HOST_22_122_cert WITH SUBJECT = 'HOST_22_122_certificate',
START_DATE = '09/20/2010',EXPIRY_DATE = '01/01/2099';
--备机192.168.22.124上执行
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master@2015key123';CREATE CERTIFICATE HOST_22_124_cert WITH SUBJECT = 'HOST_22_124_certificate',
START_DATE = '09/20/2010',EXPIRY_DATE = '01/01/2099';
--见证192.168.22.128上执行
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master@2015key123';CREATE CERTIFICATE HOST_22_128_cert WITH SUBJECT = 'HOST_22_128_certificate',
START_DATE = '09/20/2010',EXPIRY_DATE = '01/01/2099';
--主机192.168.22.122上执行
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_22_122_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
--备机192.168.22.124上执行
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_22_124_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
--见证192.168.22.128上执行
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_22_128_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
--主机192.168.22.122上执行
BACKUP CERTIFICATE HOST_22_122_cert TO FILE = '/data/mssql/1433/dbbackup/HOST_22_122_cert.cer';
scp /data/mssql/1433/dbbackup/HOST_22_122_cert.cer root@192.168.22.124:/data/mssql/1433/dbbackup/
scp /data/mssql/1433/dbbackup/HOST_22_122_cert.cer root@192.168.22.128:/data/mssql/1433/dbbackup/
chown -R mssql:mssql /data/mssql/1433/*
--备机192.168.22.124上执行
BACKUP CERTIFICATE HOST_22_124_cert TO FILE = '/data/mssql/1433/dbbackup/HOST_22_124_cert.cer';
scp /data/mssql/1433/dbbackup/HOST_22_124_cert.cer root@192.168.22.128:/data/mssql/1433/dbbackup/
scp /data/mssql/1433/dbbackup/HOST_22_124_cert.cer root@192.168.22.122:/data/mssql/1433/dbbackup/
chown -R mssql:mssql /data/mssql/1433/*
--见证192.168.22.128上执行
BACKUP CERTIFICATE HOST_22_128_cert TO FILE = '/data/mssql/1433/dbbackup/HOST_22_128_cert.cer';
scp /data/mssql/1433/dbbackup/HOST_22_128_cert.cer root@192.168.22.124:/data/mssql/1433/dbbackup/
scp /data/mssql/1433/dbbackup/HOST_22_128_cert.cer root@192.168.22.122:/data/mssql/1433/dbbackup/
chown -R mssql:mssql /data/mssql/1433/*
--主机192.168.22.122上执行
CREATE LOGIN [wwwmssql124LoginUser] WITH PASSWORD = 'User_Pass@2015key123';
CREATE USER [wwwmssql124User] FOR LOGIN [wwwmssql124LoginUser];
CREATE CERTIFICATE HOST_22_124_cert AUTHORIZATION [wwwmssql124User] FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_124_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [wwwmssql124LoginUser];
CREATE LOGIN [wwwmssql128LoginUser] WITH PASSWORD = 'User_Pass@2015key123';
CREATE USER [wwwmssql128User] FOR LOGIN [wwwmssql128LoginUser];
CREATE CERTIFICATE HOST_22_128_cert AUTHORIZATION [wwwmssql128User] FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_128_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [wwwmssql128LoginUser];
--备机192.168.22.124上执行
CREATE LOGIN [wwwmssql122LoginUser] WITH PASSWORD = 'User_Pass@2015key123';
CREATE USER [wwwmssql122User] FOR LOGIN [wwwmssql122LoginUser];
CREATE CERTIFICATE HOST_22_122_cert AUTHORIZATION [wwwmssql122User] FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_122_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [wwwmssql122LoginUser];
CREATE LOGIN [wwwmssql128LoginUser] WITH PASSWORD = 'User_Pass@2015key123';
CREATE USER [wwwmssql128User] FOR LOGIN [wwwmssql128LoginUser];
CREATE CERTIFICATE HOST_22_128_cert AUTHORIZATION [wwwmssql128User] FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_128_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [wwwmssql128LoginUser];
--见证192.168.22.128上执行
CREATE LOGIN [wwwmssql122LoginUser] WITH PASSWORD = 'User_Pass@2015key123';
CREATE USER [wwwmssql122User] FOR LOGIN [wwwmssql122LoginUser];
CREATE CERTIFICATE HOST_22_122_cert AUTHORIZATION [wwwmssql122User] FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_122_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [wwwmssql122LoginUser];
CREATE LOGIN [wwwmssql124LoginUser] WITH PASSWORD = 'User_Pass@2015key123';
CREATE USER [wwwmssql124User] FOR LOGIN [wwwmssql124LoginUser];
CREATE CERTIFICATE HOST_22_124_cert AUTHORIZATION [wwwmssql124User] FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_124_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [wwwmssql124LoginUser];
##主机192.168.22.122上执行
telnet 192.168.22.124 5022
telnet 192.168.22.128 5022
##备机192.168.22.124上执行
telnet 192.168.22.122 5022
telnet 192.168.22.128 5022
##见证192.168.22.128上执行
telnet 192.168.22.122 5022
telnet 192.168.22.124 5022
##通过ss命令可以看到数据库在侦听5022端口
ss -lntup
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
tcp LISTEN 0 128 0.0.0.0:1433 0.0.0.0:* users:(("sqlservr",pid=1190,fd=114))
tcp LISTEN 0 128 127.0.0.1:1431 0.0.0.0:* users:(("sqlservr",pid=1190,fd=120))
tcp LISTEN 0 128 127.0.0.1:1434 0.0.0.0:* users:(("sqlservr",pid=1190,fd=117))
tcp LISTEN 0 128 0.0.0.0:5022 0.0.0.0:* users:(("sqlservr",pid=1190,fd=142))
--主机192.168.22.122上执行
DECLARE @FileName NVARCHAR(MAX)
--(TestDB数据库完整备份)
SET @FileName = '/data/mssql/1433/dbbackup/TestDBBACKUP_FullBackup_1.bak'
BACKUP DATABASE [TestDB]
TO DISK=@FileName WITH FORMAT ,COMPRESSION
--(TestDB数据库日志备份)
SET @FileName = '/data/mssql/1433/dbbackup/TestDBBACKUP_logBackup_2.bak'
BACKUP LOG [TestDB]
TO DISK=@FileName WITH FORMAT ,COMPRESSION
用scp命令拷贝备份文件到备机192.168.22.124
scp /data/mssql/1433/dbbackup/TestDBBACKUP_* root@192.168.22.124:/data/mssql/1433/dbbackup/
设置一下权限
#备机192.168.22.124上执行
chown -R mssql:mssql /data/mssql/1433/*
在SSMS管理工具上执行
--备机192.168.22.124上执行
USE [master]
RESTORE DATABASE TestDB FROM DISK = N'/data/mssql/1433/dbbackup/TestDBBACKUP_FullBackup_1.bak' WITH FILE = 1,
NOUNLOAD,NORECOVERY, REPLACE, STATS = 5
GO
USE [master]
RESTORE LOG TestDB FROM DISK = N'/data/mssql/1433/dbbackup/TestDBBACKUP_logBackup_2.bak' WITH FILE = 1,
NOUNLOAD,NORECOVERY, REPLACE, STATS = 5
GO上图中实例旁边有企鹅图标，说明这三个SQL Server实例跑在Linux系统上
10. 增加镜像伙伴，需要先在备机上执行，再在主机上执行，镜像弄好之后 ，根据提示在各自的服务器上执行
--备机192.168.22.124上执行
USE [master]
GO
ALTER DATABASE [TestDB] SET PARTNER = 'TCP://192.168.22.122:5022'; --主机服务器的ip
--主机192.168.22.122上执行
USE [master]
GO
ALTER DATABASE [TestDB] SET PARTNER = 'TCP://192.168.22.124:5022'; --镜像服务器的ip
ALTER DATABASE [TestDB] SET WITNESS = 'TCP://192.168.22.128:5022'; --见证服务器的ip
--主机192.168.22.122上执行
--修改为高安全模式
USE [master]
GO
ALTER DATABASE [TestDB] SET PARTNER SAFETY FULL
GO测试自动故障转移和手动故障转移
手动故障转移
--主机192.168.22.122上执行
--手动故障转移
USE [master]
GO
ALTER DATABASE [TestDB] SET PARTNER FAILOVER
GO
自动故障转移
使用poweroff命令直接关机或者killall sqlservr命令来直接停止SQL Server实例进程
测试结果
无论是手动故障转移还是自动故障转移（poweroff命令直接关机还是killall sqlservr 命令直接停止SQL Server实例进程）都没有任何问题。
总结
本文介绍了在Linux环境下为SQL Server数据库设置高可用性方案，数据库镜像自SQL Server 2005起开始稳定支持且无需借助第三方组件。数据库镜像作为一种简便且成本低廉的高可用性解决方案，不仅可以在Linux系统上顺利运行还具备自动和手动故障转移能力。通过简单的步骤配置，用户可以确保数据库在主服务器故障时快速切换完全满足生产环境的高可用性的需求。
参考文章
https://ithelp.ithome.com.tw/articles/10031291
https://www.tatvasoft.com/blog/how-to-configure-database-mirroring-for-sql-server/
https://www.sqlshack.com/sql-server-database-mirroring/
https://codingsight.medium.com/configure-database-mirroring-in-sql-server-c819f3ef3648
加入我们的微信群，与我们一起探讨数据库技术，以及SQL Server、 MySQL、PostgreSQL、MongoDB、Oracle、Redis 的相关话题。
微信群仅供学习交流使用，没有任何广告或商业活动。
