低成本高可用方案！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）加速故障转移的速度

 
很多用户会嫌弃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系统下的数据库目录结构如下：

1. /data/mssql/1433/database  
2. /data/mssql/1433/dbbackup
3. /data/mssql/1433/dump
4. /data/mssql/1433/tempdb

复制代码

 IP主机名主库192.168.22.122wwwmssql122镜像库192.168.22.124wwwmssql124见证192.168.22.128wwwmssql128正式开始搭建

• 修改hosts设置文件，三台机器都要同时修改
  

1. cat <<EOF >> /etc/hosts
2. 192.168.22.122   wwwmssql122
3. 192.168.22.124   wwwmssql124
4. 192.168.22.128   wwwmssql128
5. EOF

复制代码

2. 首先确定要做镜像数据库的恢复模式为完整模式，用以下sql语句来查看

1. --主机192.168.22.122上执行
2. SELECT [name], [recovery_model_desc] FROM sys.[databases]

复制代码

 

 
3. 在主服务器和镜像服务器上和见证服务器上创建Master Key 、创建证书 ，根据提示在各自的服务器上执行

1. --主机192.168.22.122上执行
2. USE master;
3. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master@2015key123';CREATE CERTIFICATE HOST_22_122_cert  WITH SUBJECT = 'HOST_22_122_certificate',
4. START_DATE = '09/20/2010',EXPIRY_DATE = '01/01/2099';
6. --备机192.168.22.124上执行
7. USE master;
8. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master@2015key123';CREATE CERTIFICATE HOST_22_124_cert  WITH SUBJECT = 'HOST_22_124_certificate',
9. START_DATE = '09/20/2010',EXPIRY_DATE = '01/01/2099';
11. --见证192.168.22.128上执行
12. USE master;
13. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master@2015key123';CREATE CERTIFICATE HOST_22_128_cert  WITH SUBJECT = 'HOST_22_128_certificate',
14. START_DATE = '09/20/2010',EXPIRY_DATE = '01/01/2099';

复制代码

4. 创建镜像端点，同一个实例上只能存在一个镜像端点 ，根据提示在各自的服务器上执行

1. --主机192.168.22.122上执行
2. CREATE ENDPOINT Endpoint_Mirroring 
3. STATE = STARTED 
4. AS 
5. TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 
6. FOR 
7. DATABASE_MIRRORING 
8. ( AUTHENTICATION = CERTIFICATE HOST_22_122_cert  , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
10. --备机192.168.22.124上执行
11. CREATE ENDPOINT Endpoint_Mirroring 
12. STATE = STARTED 
13. AS 
14. TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 
15. FOR 
16. DATABASE_MIRRORING 
17. ( AUTHENTICATION = CERTIFICATE HOST_22_124_cert  , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );
19. --见证192.168.22.128上执行
20. CREATE ENDPOINT Endpoint_Mirroring
21. STATE = STARTED
22. AS
23. TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
24. FOR
25. DATABASE_MIRRORING
26. ( AUTHENTICATION = CERTIFICATE HOST_22_128_cert  , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

复制代码

5.  每台机器都备份证书，然后互换，把证书scp到其他机器并且设置好权限 ，根据提示在各自的服务器上执行

1. --主机192.168.22.122上执行
2. BACKUP CERTIFICATE HOST_22_122_cert TO FILE = '/data/mssql/1433/dbbackup/HOST_22_122_cert.cer';
3. scp /data/mssql/1433/dbbackup/HOST_22_122_cert.cer  root@192.168.22.124:/data/mssql/1433/dbbackup/
4. scp /data/mssql/1433/dbbackup/HOST_22_122_cert.cer  root@192.168.22.128:/data/mssql/1433/dbbackup/
5. chown -R mssql:mssql  /data/mssql/1433/*
7. --备机192.168.22.124上执行
8. BACKUP CERTIFICATE HOST_22_124_cert TO FILE = '/data/mssql/1433/dbbackup/HOST_22_124_cert.cer';
9. scp /data/mssql/1433/dbbackup/HOST_22_124_cert.cer  root@192.168.22.128:/data/mssql/1433/dbbackup/
10. scp /data/mssql/1433/dbbackup/HOST_22_124_cert.cer  root@192.168.22.122:/data/mssql/1433/dbbackup/
11. chown -R mssql:mssql  /data/mssql/1433/*
14. --见证192.168.22.128上执行
15. BACKUP CERTIFICATE HOST_22_128_cert TO FILE = '/data/mssql/1433/dbbackup/HOST_22_128_cert.cer';
16. scp /data/mssql/1433/dbbackup/HOST_22_128_cert.cer  root@192.168.22.124:/data/mssql/1433/dbbackup/
17. scp /data/mssql/1433/dbbackup/HOST_22_128_cert.cer  root@192.168.22.122:/data/mssql/1433/dbbackup/
18. chown -R mssql:mssql  /data/mssql/1433/*

复制代码

6. 新增主备机登陆用户 ，根据提示在各自的服务器上执行

1. --主机192.168.22.122上执行
2. CREATE LOGIN [wwwmssql124LoginUser] WITH PASSWORD = 'User_Pass@2015key123'; 
3. CREATE USER [wwwmssql124User] FOR LOGIN [wwwmssql124LoginUser]; 
4. CREATE CERTIFICATE HOST_22_124_cert AUTHORIZATION [wwwmssql124User] FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_124_cert.cer';
5. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [wwwmssql124LoginUser];
7. CREATE LOGIN [wwwmssql128LoginUser] WITH PASSWORD = 'User_Pass@2015key123'; 
8. CREATE USER [wwwmssql128User] FOR LOGIN [wwwmssql128LoginUser]; 
9. CREATE CERTIFICATE HOST_22_128_cert AUTHORIZATION [wwwmssql128User] FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_128_cert.cer';
10. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [wwwmssql128LoginUser];
12. --备机192.168.22.124上执行
13. CREATE LOGIN [wwwmssql122LoginUser] WITH PASSWORD = 'User_Pass@2015key123'; 
14. CREATE USER [wwwmssql122User] FOR LOGIN [wwwmssql122LoginUser]; 
15. CREATE CERTIFICATE HOST_22_122_cert AUTHORIZATION [wwwmssql122User] FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_122_cert.cer';
16. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [wwwmssql122LoginUser];
18. CREATE LOGIN [wwwmssql128LoginUser] WITH PASSWORD = 'User_Pass@2015key123'; 
19. CREATE USER [wwwmssql128User] FOR LOGIN [wwwmssql128LoginUser]; 
20. CREATE CERTIFICATE HOST_22_128_cert AUTHORIZATION [wwwmssql128User] FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_128_cert.cer';
21. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [wwwmssql128LoginUser];
23. --见证192.168.22.128上执行
24. CREATE LOGIN [wwwmssql122LoginUser] WITH PASSWORD = 'User_Pass@2015key123'; 
25. CREATE USER [wwwmssql122User] FOR LOGIN [wwwmssql122LoginUser]; 
26. CREATE CERTIFICATE HOST_22_122_cert AUTHORIZATION [wwwmssql122User] FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_122_cert.cer';
27. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [wwwmssql122LoginUser];
29. CREATE LOGIN [wwwmssql124LoginUser] WITH PASSWORD = 'User_Pass@2015key123'; 
30. CREATE USER [wwwmssql124User] FOR LOGIN [wwwmssql124LoginUser]; 
31. CREATE CERTIFICATE HOST_22_124_cert AUTHORIZATION [wwwmssql124User] FROM FILE ='/data/mssql/1433/dbbackup/HOST_22_124_cert.cer';
32. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [wwwmssql124LoginUser];

复制代码

7.  确保各个机器都放开了5022端口，数据库镜像数据同步需要使用5022端口，使用telnet命令和ss命令测试，根据提示在各自的服务器上执行

1. ##主机192.168.22.122上执行
2. telnet 192.168.22.124 5022
3. telnet 192.168.22.128 5022
5. ##备机192.168.22.124上执行
6. telnet 192.168.22.122 5022
7. telnet 192.168.22.128 5022
9. ##见证192.168.22.128上执行
10. telnet 192.168.22.122 5022
11. telnet 192.168.22.124 5022
13. ##通过ss命令可以看到数据库在侦听5022端口
14. ss -lntup
15. Netid     State       Recv-Q      Send-Q           Local Address:Port           Peer Address:Port     Process                                           
16. tcp       LISTEN      0           128                    0.0.0.0:1433                0.0.0.0:*         users:(("sqlservr",pid=1190,fd=114))     
17. tcp       LISTEN      0           128                  127.0.0.1:1431                0.0.0.0:*         users:(("sqlservr",pid=1190,fd=120))     
18. tcp       LISTEN      0           128                  127.0.0.1:1434                0.0.0.0:*         users:(("sqlservr",pid=1190,fd=117))     
19. tcp       LISTEN      0           128                    0.0.0.0:5022                0.0.0.0:*         users:(("sqlservr",pid=1190,fd=142))     

复制代码

8. 备份数据库(完整备份+事务日志备份)在主机执行

1. --主机192.168.22.122上执行
2. DECLARE @FileName NVARCHAR(MAX)
3. --(TestDB数据库完整备份)
4. SET @FileName = '/data/mssql/1433/dbbackup/TestDBBACKUP_FullBackup_1.bak'
5. BACKUP DATABASE [TestDB]
6. TO DISK=@FileName WITH FORMAT ,COMPRESSION
8. --(TestDB数据库日志备份)
9. SET @FileName = '/data/mssql/1433/dbbackup/TestDBBACKUP_logBackup_2.bak'
10. BACKUP LOG [TestDB]
11. TO DISK=@FileName WITH FORMAT ,COMPRESSION

复制代码

用scp命令拷贝备份文件到备机192.168.22.124

1. scp /data/mssql/1433/dbbackup/TestDBBACKUP_*  root@192.168.22.124:/data/mssql/1433/dbbackup/

复制代码

9.  还原数据库(指定norecovery方式还原)在备机执行
设置一下权限

1. #备机192.168.22.124上执行
2. chown -R mssql:mssql  /data/mssql/1433/*

复制代码

在SSMS管理工具上执行

1. --备机192.168.22.124上执行
2. USE [master]
3. RESTORE DATABASE TestDB FROM  DISK = N'/data/mssql/1433/dbbackup/TestDBBACKUP_FullBackup_1.bak' WITH  FILE = 1,
4. NOUNLOAD,NORECOVERY,  REPLACE,  STATS = 5
5. GO
7. USE [master]
8. RESTORE LOG TestDB FROM  DISK = N'/data/mssql/1433/dbbackup/TestDBBACKUP_logBackup_2.bak' WITH  FILE = 1,
9. NOUNLOAD,NORECOVERY,  REPLACE,  STATS = 5
10. GO

复制代码

 
上图中实例旁边有企鹅图标，说明这三个SQL Server实例跑在Linux系统上
 
 
10.  增长镜像伙伴，需要先在备机上执行，再在主机上执行，镜像弄好之后 ，根据提示在各自的服务器上执行

1. --备机192.168.22.124上执行
2. USE [master]
3. GO
4. ALTER DATABASE [TestDB] SET PARTNER = 'TCP://192.168.22.122:5022';  --主机服务器的ip
6. --主机192.168.22.122上执行
7. USE [master]
8. GO
9. ALTER DATABASE [TestDB] SET PARTNER = 'TCP://192.168.22.124:5022';  --镜像服务器的ip
10. ALTER DATABASE [TestDB] SET WITNESS = 'TCP://192.168.22.128:5022';  --见证服务器的ip

复制代码

11.  把数据库镜像的模式修改为高安全模式

1. --主机192.168.22.122上执行
2. --修改为高安全模式
3. USE [master]
4. GO
5. ALTER DATABASE [TestDB] SET PARTNER SAFETY FULL
6. GO

复制代码

测试自动故障转移和手动故障转移

• 手动故障转移
  

1. --主机192.168.22.122上执行
2. --手动故障转移
3. USE [master]
4. GO
5. ALTER DATABASE [TestDB] SET PARTNER FAILOVER
6. GO

复制代码

• 自动故障转移
  

1. 使用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
 
 
 

本文版权归作者全部，未经作者同意不得转载。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。