低成本高可用方案！Linux系统下SQL Server数据库镜像设置全流程详解
低成本高可用方案！Linux系统下SQL Server数据库镜像设置全流程详解背景
最近遇到一个客户需求，客户的生产环境有大量的SQL Server数据库（大概180套），客户的诉求是需要把生产环境的Windows系统转为Linux系统，转为Linux系统之后需要沿用原来的数据库高可用方案。把数据库转到Linux系统比较轻易，由于从SQL Server 2017开始就支持Linux系统，但是要沿用原来的数据库高可用方案就有点难度。了解到客户之前的生产环境所用的数据库高可用方案是数据库镜像，由于客户的生产环境高可用性要求不算太高，数据库镜像技能已经可以完全满足要求。
于是本人建议客户把SQL Server迁移到Linux系统之后，依然使用之前的数据库镜像这种数据库高可用方案。数据库镜像这种技能本身搭建非常简单，不需要借助任何第三方组件，搭建步调跟Windows系统上的一模一样，而且从SQL Server 2005版本开始就已经引入数据库镜像，历经9个大版本迭代，稳固性久经检验。
在SQL Server 2016版本引入了多线程并行redo，它可以
（1）显著减少镜像集群和AlwaysOn可用性组集群的数据同步延迟。
（2）数据库的启动速度显著加速，数据库启动需要经历redo阶段。
（3）加速故障转移的速度
https://img2024.cnblogs.com/blog/257159/202501/257159-20250109065937572-1289165343.png
很多用户会嫌弃Linux系统上的AlwaysOn可用性组搭建麻烦，由于需要借助Pacemaker与Corosync等第三方组件，搭建过程有一定的难度。对于生产环境上的数据库高可用性和性能要求不是很高的情况下，完全可以使用数据库镜像来更换AlwaysOn可用性组。
https://img2024.cnblogs.com/blog/257159/202501/257159-20250108235146468-721622365.png
搭建步调
下面进行演示怎样在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 IP主机名主库192.168.22.122wwwmssql122镜像库192.168.22.124wwwmssql124见证192.168.22.128wwwmssql128正式开始搭建
[*]修改hosts设置文件，三台机器都要同时修改
cat <<EOF >> /etc/hosts
192.168.22.122 wwwmssql122
192.168.22.124 wwwmssql124
192.168.22.128 wwwmssql128
EOF2. 首先确定要做镜像数据库的恢复模式为完整模式，用以下sql语句来查看
--主机192.168.22.122上执行
SELECT , FROM sys.
https://img2024.cnblogs.com/blog/257159/202501/257159-20250108235225385-1081423434.png
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';4. 创建镜像端点，同一个实例上只能存在一个镜像端点 ，根据提示在各自的服务器上执行
--主机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 );5. 每台机器都备份证书，然后互换，把证书scp到其他机器并且设置好权限 ，根据提示在各自的服务器上执行
--主机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/*6. 新增主备机登陆用户 ，根据提示在各自的服务器上执行
--主机192.168.22.122上执行
CREATE LOGIN WITH PASSWORD = 'User_Pass@2015key123';
CREATE USER FOR LOGIN ;
CREATE CERTIFICATE HOST_22_124_cert AUTHORIZATION FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_124_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ;
CREATE LOGIN WITH PASSWORD = 'User_Pass@2015key123';
CREATE USER FOR LOGIN ;
CREATE CERTIFICATE HOST_22_128_cert AUTHORIZATION FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_128_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ;
--备机192.168.22.124上执行
CREATE LOGIN WITH PASSWORD = 'User_Pass@2015key123';
CREATE USER FOR LOGIN ;
CREATE CERTIFICATE HOST_22_122_cert AUTHORIZATION FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_122_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ;
CREATE LOGIN WITH PASSWORD = 'User_Pass@2015key123';
CREATE USER FOR LOGIN ;
CREATE CERTIFICATE HOST_22_128_cert AUTHORIZATION FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_128_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ;
--见证192.168.22.128上执行
CREATE LOGIN WITH PASSWORD = 'User_Pass@2015key123';
CREATE USER FOR LOGIN ;
CREATE CERTIFICATE HOST_22_122_cert AUTHORIZATION FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_122_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ;
CREATE LOGIN WITH PASSWORD = 'User_Pass@2015key123';
CREATE USER FOR LOGIN ;
CREATE CERTIFICATE HOST_22_124_cert AUTHORIZATION FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_124_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ;7. 确保各个机器都放开了5022端口，数据库镜像数据同步需要使用5022端口，使用telnet命令和ss命令测试，根据提示在各自的服务器上执行
##主机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)) https://img2024.cnblogs.com/blog/257159/202501/257159-20250108235303668-496306385.png
8. 备份数据库(完整备份+事务日志备份)在主机执行
--主机192.168.22.122上执行
DECLARE @FileName NVARCHAR(MAX)
--(TestDB数据库完整备份)
SET @FileName = '/data/mssql/1433/dbbackup/TestDBBACKUP_FullBackup_1.bak'
BACKUP DATABASE
TO DISK=@FileName WITH FORMAT ,COMPRESSION
--(TestDB数据库日志备份)
SET @FileName = '/data/mssql/1433/dbbackup/TestDBBACKUP_logBackup_2.bak'
BACKUP LOG
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/9. 还原数据库(指定norecovery方式还原)在备机执行
设置一下权限
#备机192.168.22.124上执行
chown -R mssql:mssql /data/mssql/1433/*在SSMS管理工具上执行
--备机192.168.22.124上执行
USE
RESTORE DATABASE TestDB FROM DISK = N'/data/mssql/1433/dbbackup/TestDBBACKUP_FullBackup_1.bak' WITH FILE = 1,
NOUNLOAD,NORECOVERY, REPLACE, STATS = 5
GO
USE
RESTORE LOG TestDB FROM DISK = N'/data/mssql/1433/dbbackup/TestDBBACKUP_logBackup_2.bak' WITH FILE = 1,
NOUNLOAD,NORECOVERY, REPLACE, STATS = 5
GOhttps://img2024.cnblogs.com/blog/257159/202501/257159-20250108235341885-1750341506.png
上图中实例旁边有企鹅图标，说明这三个SQL Server实例跑在Linux系统上
10. 增长镜像伙伴，需要先在备机上执行，再在主机上执行，镜像弄好之后 ，根据提示在各自的服务器上执行
--备机192.168.22.124上执行
USE
GO
ALTER DATABASE SET PARTNER = 'TCP://192.168.22.122:5022'; --主机服务器的ip
--主机192.168.22.122上执行
USE
GO
ALTER DATABASE SET PARTNER = 'TCP://192.168.22.124:5022'; --镜像服务器的ip
ALTER DATABASE SET WITNESS = 'TCP://192.168.22.128:5022'; --见证服务器的ip11. 把数据库镜像的模式修改为高安全模式
--主机192.168.22.122上执行
--修改为高安全模式
USE
GO
ALTER DATABASE SET PARTNER SAFETY FULL
GO测试自动故障转移和手动故障转移
[*]手动故障转移
--主机192.168.22.122上执行
--手动故障转移
USE
GO
ALTER DATABASE SET PARTNER FAILOVER
GO
[*]自动故障转移
使用poweroff命令直接关机或者killall sqlservr命令来直接停止SQL Server实例进程https://img2024.cnblogs.com/blog/257159/202501/257159-20250108235440067-383177724.png
测试结果
https://img2024.cnblogs.com/blog/257159/202501/257159-20250108235447980-928876756.png
无论是手动故障转移还是自动故障转移（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
https://img2024.cnblogs.com/blog/257159/202409/257159-20240908204310924-1005667056.png
本文版权归作者全部，未经作者同意不得转载。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]