centos7.9搭建SqlServer 2017高可用集群

目录 Hide

一、说明
1. 1.官方配置顺序
2. 2.实验环境ip
二、基础配置
三、SqlServer配置
1. 1.安装sqlserver和sqlserver工具
2. 2.sqlserver可用组配置(AG)
四、Pacemaker配置
1. 1.pacemaker安装
2. 2.pacemaker集群配置
五、Pacemaker与SqlServer集成
1. 1.集成
2. 2.测试
六、常用命令
1. 1.sqlserver命令
七、文档地址

一、说明

linux系统下面配置SqlServer高可用集群主要分三大部分：sqlserver配置、pacemaker集群配置、pacemaker集群与sqlserver集成。

1.官方配置顺序

2.实验环境ip

ip：192.168.1.183 主机名：node31

ip：192.168.1.184 主机名：node32

ip：192.168.1.185 主机名：node33

SQL Server master：192.168.1.183

SQL Server slave：192.168.1.184

SQL Server slave：192.168.1.185

pacemaker集群：192.168.1.183-185

二、基础配置

1 配置时间同步

yum install ntp -y; 
ntpdate cn.pool.ntp.org; 
echo "1 * * * * /usr/sbin/ntpdate cn.pool.ntp.org" >>/var/spool/cron/root;

2 关闭防火墙

systemctl stop firewalld.service

3.配置主机名和hosts

hostname nodexx;
echo "nodexx" > /etc/hostname;
echo "192.168.1.183 node31" >> /etc/hosts
echo "192.168.1.183 node32" >> /etc/hosts
echo "192.168.1.183 node33" >> /etc/hosts

三、SqlServer配置

1.安装sqlserver和sqlserver工具

1.1 安装yum源

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo;
sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo

1.2 安装sqlserver

sudo yum install -y mssql-server mssql-tools unixODBC-devel;

1.3 启动

这里会选择版本、以及语言，按照提示配置即可。

sudo /opt/mssql/bin/mssql-conf setup

2.sqlserver可用组配置(AG)

2.1 启动配置选项

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled  1
sudo systemctl restart mssql-server

2.2 启用 AlwaysOn_health 事件会话

可选择性地启用 AlwaysOn 可用性组的扩展事件，以便在对可用性组进行故障排除时帮助诊断根本原因。

在每个 SQL Server 实例上运行以下命令：

sqlcmd -S "127.0.0.1,1433" -d "master" -U SA -P "passwd@passwd.com" -Q "ALTER EVENT SESSION  AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO"

有关此 XE 会话的详细信息，请参阅 Always On 扩展事件。

2.3 创建证书

sqlcmd -S "127.0.0.1,1433" -d "master" -U SA -P "passwd@passwd.com" -Q "CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passwd@passwd.com';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
   TO FILE = '/var/opt/mssql/data/cert/dbm_certificate.cer'
   WITH PRIVATE KEY (
           FILE = '/var/opt/mssql/data/cert/dbm_certificate.pvk',
           ENCRYPTION BY PASSWORD = 'passwd@passwd.com'
       );
GO"

2.4 将证书拷贝到其他节点

如果是生产环境的话，要配置三节点证书互认。

cd /var/opt/mssql/data
scp dbm_certificate.* root@**<node2>**:/var/opt/mssql/data/

2.5 修改证书所属用户和组

cd /var/opt/mssql/data/cert
chown mssql:mssql /var/opt/mssql/data/cert/
chown mssql:mssql dbm_certificate.*

2.6 在辅助节点关联证书

在辅助节点执行

sqlcmd -S "127.0.0.1,1433" -d "master" -U SA -P "passwd@passwd.com" -Q "
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passwd@passwd.com';
CREATE CERTIFICATE dbm_certificate
    FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
    FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
    DECRYPTION BY PASSWORD = 'passwd@passwd.com'
            );
GO"

2.7 在所有节点创建数据库镜像终结点

sqlcmd -S "127.0.0.1,1433" -d "master" -U SA -P "passwd@passwd.com" -Q "
CREATE ENDPOINT Hadr_endpoint
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
	    ROLE = ALL,
	    AUTHENTICATION = CERTIFICATE dbm_certificate,
		ENCRYPTION = REQUIRED ALGORITHM AES
		);
ALTER ENDPOINT Hadr_endpoint STATE = STARTED;
GO"

2.8 创建AG可用组

sqlcmd -S "127.0.0.1,1433" -d "master" -U SA -P "passwd@passwd.com" -Q "
CREATE AVAILABILITY GROUP ag1
     WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL)
     FOR REPLICA ON
         N'node31' 
 	      	WITH (
  	       ENDPOINT_URL = N'tcp://node31:5022',
  	       AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
  	       FAILOVER_MODE = EXTERNAL,
  	       SEEDING_MODE = AUTOMATIC
  	       ),
         N'node32' 
  	    WITH ( 
  	       ENDPOINT_URL = N'tcp://node32:5022', 
  	       AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
  	       FAILOVER_MODE = EXTERNAL,
  	       SEEDING_MODE = AUTOMATIC
  	       ),
  	   N'node33'
         WITH( 
  	      ENDPOINT_URL = N'tcp://node33:5022', 
  	      AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
  	      FAILOVER_MODE = EXTERNAL,
  	      SEEDING_MODE = AUTOMATIC
  	      );

ALTER AVAILABILITY GROUP ag1 GRANT CREATE ANY DATABASE;
GO"

2.9 关联次要副本到AG

sqlcmd -S "127.0.0.1,1433" -d "master" -U SA -P "passwd@passwd.com" -Q "ALTER AVAILABILITY GROUP ag1 JOIN WITH (CLUSTER_TYPE = EXTERNAL);		 
ALTER AVAILABILITY GROUP ag1 GRANT CREATE ANY DATABASE;
GO"

2.10 创建Pacemaker的SQL Server登录名

sqlcmd -S "127.0.0.1,1433" -d "master" -U SA -P "passwd@passwd.com" -Q "
USE master
GO
CREATE LOGIN pacemakerLogin with PASSWORD= N'passwd@passwd.com'

ALTER SERVER ROLE sysadmin ADD MEMBER pacemakerLogin
GO"

2.11 保存登陆凭证

echo 'pacemakerLogin' >> ~/pacemaker-passwd
echo 'passwd@passwd.com' >> ~/pacemaker-passwd
sudo mv ~/pacemaker-passwd /var/opt/mssql/secrets/passwd
sudo chown root:root /var/opt/mssql/secrets/passwd
sudo chmod 400 /var/opt/mssql/secrets/passwd # Only readable by root

2.12 为Pacemaker登陆用户创建相关权限

sqlcmd -S "127.0.0.1,1433" -d "master" -U SA -P "passwd@passwd.com" -Q "
GRANT ALTER, CONTROL, VIEW DEFINITION ON AVAILABILITY GROUP::ag1 TO pacemakerLogin
GRANT VIEW SERVER STATE TO pacemakerLogin
GO"

2.13 创建数据库

创建数据库，并将数据库配置为完全恢复模式，并具有效日志备份。

sqlcmd -S "127.0.0.1,1433" -d "master" -U SA -P "passwd@passwd.com" -Q "CREATE DATABASE TestDB;
ALTER DATABASE TestDB SET RECOVERY FULL;
BACKUP DATABASE TestDB
   TO DISK = N'/var/opt/mssql/data/TestDB.bak';
GO"

2.14 将数据库添加到可用性组

sqlcmd -S "127.0.0.1,1433" -d "master" -U SA -P "passwd@passwd.com" -Q "ALTER AVAILABILITY GROUP ag1 ADD DATABASE TestDB;
Go"

2.15 设置从库允许只读

sqlcmd -S 192.168.1.186,1433 -U SA -P 'passwd@passwd.com' -Q "
USE master ALTER AVAILABILITY GROUP ag1   MODIFY REPLICA ON   N'node33' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL ));
GO"

四、Pacemaker配置

1.pacemaker安装

1.1 安装pacemaker

yum install pcs pacemaker fence-agents-all

1.2 为hacluster用户创建密码

安装完pacemaker后，会自动创建hacluster用户

# passwd hacluster
Changing password for user hacluster.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

1.3 启动服务

# systemctl start pcsd.service
# systemctl enable pcsd.service

2.pacemaker集群配置

2.1 验证节点用户

[root@z1 ~]# pcs cluster auth node31 node32 node33
Username: hacluster
Password:
node31: Authorized
node32: Authorized
node33: Authorized

2.2 创建集群

[root@z1 ~]# pcs cluster setup --start --name my_cluster node31 node32 node33

2.3 启动集群

[root@z1 ~]# pcs cluster enable --all

2.4 查看集群状态

[root@z1 ~]# pcs cluster status
Cluster Status:
 Last updated: Thu Jul 25 13:01:26 2013
 Last change: Thu Jul 25 13:04:45 2013 via crmd on z2.example.com
 Stack: corosync
 Current DC: z2.example.com (2) - partition with quorum
 Version: 1.1.10-5.el7-9abe687
 2 Nodes configured
 0 Resources configured

2.5 设置防火墙允许

如果未关闭防火墙，可以执行下面的命令：

# firewall-cmd --permanent --add-service=high-availability
# firewall-cmd --add-service=high-availability

五、Pacemaker与SqlServer集成

1.集成

1.1 安装SQL Server代理

在所有SQL Server节点执行

sudo yum install mssql-server-ha

1.2 配置隔离

这里因为是在虚拟机配置的，所以禁用隔离配置。如果在生产环境部署，根据情况进行配置。

sudo pcs property set stonith-enabled=false

有关 STONITH 和隔离的信息，请参阅以下文章：

Pacemaker 从头开始群集
隔离和 STONITH
Pacemaker 的 Red Hat 高可用性附加项：隔离

1.3 设置集群属性 cluster-recheck-interval

sudo pcs property set cluster-recheck-interval=2min

有关 Pacemaker 群集属性的信息，请参阅 Pacemaker 群集属性。

1.4 创建可用性组资源

sudo pcs resource create ag_cluster ocf:mssql:ag ag_name=ag1 meta failure-timeout=60s master notify=true

1.5 创建虚拟ip

sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=192.168.1.186

到这里基于centos7.9搭建SQL Server2017 高可用集群就完成。

官方文档后面还有主机托管约束、排序约束。因为不影响搭建，所以这里就不配置了。

1.6 查看主节点是否配置虚拟ip

2: ens192: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:50:56:bd:56:6c brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.183/24 brd 192.168.1.255 scope global noprefixroute ens192
       valid_lft forever preferred_lft forever
    inet 192.168.1.186/24 brd 192.168.1.255 scope global secondary ens192
       valid_lft forever preferred_lft forever
    inet6 fe80::250:56ff:febd:566c/64 scope link 
       valid_lft forever preferred_lft forever

1.7 查看集群状态

可以看到现在Master为node31、Slaves节点为node32、node33

虚拟ip在node31上面

[root@node31 data]# pcs status 
Cluster name: my_cluster
Stack: corosync
Current DC: node32 (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum
Last updated: Sat Apr  9 15:48:36 2022
Last change: Fri Apr  8 17:26:43 2022 by hacluster via crmd on node32

3 nodes configured
4 resource instances configured

Online: [ node31 node32 node33 ]

Full list of resources:

 Master/Slave Set: ag_cluster-master [ag_cluster]
     Masters: [ node31 ]
     Slaves: [ node32 node33 ]
 virtualip	(ocf::heartbeat:IPaddr2):	Started node31

Daemon Status:
  corosync: active/enabled
  pacemaker: active/enabled
  pcsd: active/enabled

2.测试

2.1 使用虚拟ip连接SQL Server

sqlcmd -S 192.168.1.186,1433 -U SA -P 'passwd@passwd.com'

2.2 创建测试数据

# 创建名为 Inventory 的新表
CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)

# 将数据插入新表
INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
GO

2.3 在辅助节点查看是否有数据插入

SELECT * FROM Inventory WHERE quantity > 152;
GO

六、常用命令

1.sqlserver命令

# 连接数据库
sqlcmd -S localhost -U SA -P '<YourPassword>'

# 创建数据库
CREATE DATABASE TestDB

# 返回数据库名称
SELECT Name from sys.Databases

七、文档地址

centos7安装sqlserver

sqlserver启动高可用组

将sqlserver高可用组加入Pacemaker

SQL Server数据库关于Alway On可用性组说明

当SQL Server爱上Linux：配置 SQL Server 2017 上的可用性组初体验

RedHat配置Pacemaker文档

RedHat配置Pacemaker文档2

Pacemaker 群集属性