美文网首页
SQL Server AG 配合Pacemaker完成三节点读写

SQL Server AG 配合Pacemaker完成三节点读写

作者: Demon宝宝 | 来源:发表于2020-11-04 22:23 被阅读0次

文档内无特别说明时命令在所有节点执行，本文以ROOT身份操作

按照本文档将会完成以下内容：

  1. 安装SQL Server 2019
  2. SQL多节点证书信任互访
  3. SQL镜像端点配置
  4. 配置Always On可用组
  5. 配置Pacemaker在SQL登录权限
  6. 安装Pacemaker
  7. 配置mssql-ag
  8. 配置虚拟IP
用途 主机名 IP
主节点 node01 1.1.1.11
辅助节点 node02 1.1.1.12
辅助节点 node03 1.1.1.13
侦听器VIP mssql 1.1.1.10

以下正文

添加YUM源并安装

curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo
yum install -y  mssql-server mssql-server-ha

免交互安装MSSQL

MSSQL_PID=Developer ACCEPT_EULA=Y MSSQL_SA_PASSWORD='Demon@666' /opt/mssql/bin/mssql-conf -n setup

开放防火墙

firewall-cmd --zone=public --add-port=1433/tcp --permanent
firewall-cmd --reload

安装MSSQL客户端环境

curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/8/prod.repo
yum install -y mssql-tools unixODBC-devel
##在弹出的许可确认，输入YES后回车继续

加入环境变量，使命令可用

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

添加hosts解析及变更主机名

echo "1.1.1.11 node01" >> /etc/hosts
echo "1.1.1.12 node02" >> /etc/hosts
echo "1.1.1.13 node03" >> /etc/hosts
echo "1.1.1.10 mssql" >> /etc/hosts

修改主机名，在各节点分别执行

node01:

hostnamectl set-hostname node01

node02

hostnamectl set-hostname node02

node03:

hostnamectl set-hostname node03

开启可用性功能

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

注意SQL语句命令中，每一个GO结束要等待确认执行完成后再继续操作，否则会报错

创建主密钥，导出证书并开启镜像端口

node01:

sqlcmd -S localhost -U SA -P 'Demon@666'
##---下面是SQL命令---
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Demon@666';
GO
CREATE CERTIFICATE node01_Cert
WITH SUBJECT = 'node01 AG Certificate',
EXPIRY_DATE = '09/09/2099';
GO
BACKUP CERTIFICATE node01_Cert
TO FILE = '/var/opt/mssql/data/node01_Cert.cer';
GO
CREATE ENDPOINT AGEP
STATE = STARTED
AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE node01_Cert,
    ROLE = ALL);
GO
exit

node02:

sqlcmd -S localhost -U SA -P 'Demon@666'
##---下面是SQL命令---
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Demon@666';
GO
CREATE CERTIFICATE node02_Cert
WITH SUBJECT = 'node02 AG Certificate',
EXPIRY_DATE = '09/09/2099';
GO
BACKUP CERTIFICATE node02_Cert
TO FILE = '/var/opt/mssql/data/node02_Cert.cer';
GO
CREATE ENDPOINT AGEP
STATE = STARTED
AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE node02_Cert,
    ROLE = ALL);
GO
exit

node03:

sqlcmd -S localhost -U SA -P 'Demon@666'
##---下面是SQL命令---
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Demon@666';
GO
CREATE CERTIFICATE node03_Cert
WITH SUBJECT = 'node03 AG Certificate',
EXPIRY_DATE = '09/09/2099';
GO
BACKUP CERTIFICATE node03_Cert
TO FILE = '/var/opt/mssql/data/node03_Cert.cer';
GO
CREATE ENDPOINT AGEP
STATE = STARTED
AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE node03_Cert,
    ROLE = ALL);
GO
exit

放行镜像需要的端口

firewall-cmd --zone=public --add-port=5022/tcp --permanent
firewall-cmd --reload

将导出的证书复制到各节点相同位置

node01:

scp node02:/var/opt/mssql/data/node* /var/opt/mssql/data/
scp node03:/var/opt/mssql/data/node* /var/opt/mssql/data/
chown mssql. /var/opt/mssql/data/node*

node02:

scp node01:/var/opt/mssql/data/node* /var/opt/mssql/data/
scp node03:/var/opt/mssql/data/node* /var/opt/mssql/data/
chown mssql. /var/opt/mssql/data/node*

node03:

scp node01:/var/opt/mssql/data/node* /var/opt/mssql/data/
scp node02:/var/opt/mssql/data/node* /var/opt/mssql/data/
chown mssql. /var/opt/mssql/data/node*

将各节点证书到数据库内，完成镜像节点互信

node01:

sqlcmd -S localhost -U SA -P 'Demon@666'
##---下面是SQL命令---
CREATE LOGIN node02Login WITH PASSWORD = 'Demon@666';
GO
CREATE USER node02User FOR LOGIN node02Login;
GO
CREATE CERTIFICATE node02_Cert
AUTHORIZATION node02User
FROM FILE = '/var/opt/mssql/data/node02_Cert.cer';
GO
CREATE LOGIN node03Login WITH PASSWORD = 'Demon@666';
GO
CREATE USER node03User FOR LOGIN node03Login;
GO
CREATE CERTIFICATE node03_Cert
AUTHORIZATION node03User
FROM FILE = '/var/opt/mssql/data/node03_Cert.cer';
GO
GRANT CONNECT ON ENDPOINT::AGEP TO node02Login;
GO
GRANT CONNECT ON ENDPOINT::AGEP TO node03Login;
GO
exit

node02:

sqlcmd -S localhost -U SA -P 'Demon@666'
##---下面是SQL命令---
CREATE LOGIN node01Login WITH PASSWORD = 'Demon@666';
GO
CREATE USER node01User FOR LOGIN node01Login;
GO
CREATE CERTIFICATE node01_Cert
AUTHORIZATION node01User
FROM FILE = '/var/opt/mssql/data/node01_Cert.cer';
GO
CREATE LOGIN node03Login WITH PASSWORD = 'Demon@666';
GO
CREATE USER node03User FOR LOGIN node03Login;
GO
CREATE CERTIFICATE node03_Cert
AUTHORIZATION node03User
FROM FILE = '/var/opt/mssql/data/node03_Cert.cer';
GO
GRANT CONNECT ON ENDPOINT::AGEP TO node01Login;
GO
GRANT CONNECT ON ENDPOINT::AGEP TO node03Login;
GO
exit

node03:

sqlcmd -S localhost -U SA -P 'Demon@666'
##---下面是SQL命令---
CREATE LOGIN node01Login WITH PASSWORD = 'Demon@666';
GO
CREATE USER node01User FOR LOGIN node01Login;
GO
CREATE CERTIFICATE node01_Cert
AUTHORIZATION node01User
FROM FILE = '/var/opt/mssql/data/node01_Cert.cer';
GO
CREATE LOGIN node02Login WITH PASSWORD = 'Demon@666';
GO
CREATE USER node02User FOR LOGIN node02Login;
GO
CREATE CERTIFICATE node02_Cert
AUTHORIZATION node02User
FROM FILE = '/var/opt/mssql/data/node02_Cert.cer';
GO
GRANT CONNECT ON ENDPOINT::AGEP TO node02Login;
GO
GRANT CONNECT ON ENDPOINT::AGEP TO node01Login;
GO
exit

创建AG集群

node01:

sqlcmd -S localhost -U SA -P 'Demon@666'
##---下面是SQL命令---
CREATE AVAILABILITY GROUP [AG01]
WITH (CLUSTER_TYPE = EXTERNAL)
FOR REPLICA ON
 N'node01' WITH (
   ENDPOINT_URL = N'TCP://node01:5022',
   FAILOVER_MODE = EXTERNAL,
   SEEDING_MODE = AUTOMATIC,
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
   PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE, READ_ONLY_ROUTING_LIST = (('node02','node03'))),
   SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL = N'TCP://node01:1433')),
N'node02' WITH (
   ENDPOINT_URL = N'TCP://node02:5022',
   FAILOVER_MODE = EXTERNAL,
   SEEDING_MODE = AUTOMATIC,
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
   PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE, READ_ONLY_ROUTING_LIST = (('node01','node03'))),
   SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL = N'TCP://node02:1433')),
N'node03' WITH (
   ENDPOINT_URL = N'TCP://node03:5022',
   FAILOVER_MODE = EXTERNAL,
   SEEDING_MODE = AUTOMATIC,
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
   PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE, READ_ONLY_ROUTING_LIST = (('node01','node02'))),
   SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL = N'TCP://node03:1433'))
LISTENER 'mssql' (WITH IP ( ('1.1.1.10','255.255.255.0') ), Port = 1433);
GO
exit

将辅助节点链接到AG

node02:

sqlcmd -S localhost -U SA -P 'Demon@666'
##---下面是SQL命令---
ALTER AVAILABILITY GROUP [AG01] JOIN WITH (CLUSTER_TYPE = EXTERNAL);
GO
ALTER AVAILABILITY GROUP [AG01] GRANT CREATE ANY DATABASE;
GO
exit

node03:

sqlcmd -S localhost -U SA -P 'Demon@666'
##---下面是SQL命令---
ALTER AVAILABILITY GROUP [AG01] JOIN WITH (CLUSTER_TYPE = EXTERNAL);
GO
ALTER AVAILABILITY GROUP [AG01] GRANT CREATE ANY DATABASE;
GO
exit

在主节点创建数据库并加入AG

node01:

sqlcmd -S localhost -U SA -P 'Demon@666'
##---下面是SQL命令---
CREATE DATABASE [demondb];
ALTER DATABASE [demondb] SET RECOVERY FULL;
BACKUP DATABASE [demondb] 
TO DISK = N'/var/opt/mssql/data/demondb.bak';
GO
ALTER AVAILABILITY GROUP [AG01] ADD DATABASE [demondb];
GO
exit

为 Pacemaker 创建 SQL Server 登录和权限

node01:

sqlcmd -S localhost -U SA -P 'Demon@666'
##---下面是SQL命令---
CREATE LOGIN PMLogin WITH PASSWORD ='Demon@666';
GO
GRANT VIEW SERVER STATE TO PMLogin;
GO
GRANT ALTER, CONTROL, VIEW DEFINITION ON AVAILABILITY GROUP::AG01 TO PMLogin;
GO
exit

为 Pacemaker 创建 SQL Server 登录和权限

echo 'PMLogin' > /var/opt/mssql/secrets/passwd
echo 'Demon@666' >> /var/opt/mssql/secrets/passwd
chown root:root /var/opt/mssql/secrets/passwd 
chmod 400 /var/opt/mssql/secrets/passwd

安装Pacemaker

yum -y install pacemaker pcs fence-agents-all resource-agents
firewall-cmd --permanent --add-service=high-availability
firewall-cmd --reload
systemctl enable pcsd
systemctl start pcsd
systemctl enable pacemaker
##---为Pacemaker 和 Corosync 包时创建的默认用户设置密码---
passwd hacluster
##---在各节点设置相同的密码---

配置Pacemaker

node01:

pcs cluster auth node01 node02 node03 -u hacluster
##---输入前面设置的密码---
pcs cluster setup --name mssql node01 node02  node03 --start --all --enable
pcs cluster start --all
pcs cluster enable --all
pcs cluster cib cfg
##绑定AG组
pcs -f cfg resource create mssql ocf:mssql:ag ag_name=AG01 meta failover-timeout=5s master notify=true
##绑定虚拟IP
pcs -f cfg resource create mssql-vip ocf:heartbeat:IPaddr2 ip=1.1.1.10 cidr_netmask=24
pcs -f cfg constraint colocation add  mssql-vip mssql-master INFINITY with-rsc-role=Master
pcs cluster cib-push cfg
##使虚拟IP随主节点迁移
pcs constraint order promote mssql-master then start mssql-vip
pcs property set stonith-enabled=false
##验证当前的集群配置是否有错误，正常情况无输出
crm_verify -L -V
##检查状态
pcs status

不加入任何参数直接访问VIP时，将访问主服务器进行读写，执行读取操作时，加入ApplicationIntent=ReadOnly参数，会仅从辅助节点读取数据。执行写入操作时加入ApplicationIntent=ReadOnly参数，会返回一个报错。

到此结束

相关文章

网友评论

      本文标题：SQL Server AG 配合Pacemaker完成三节点读写

      本文链接：https://www.haomeiwen.com/subject/tuqnvktx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|SQL Server AG 配合Pacemaker完成三节点读写|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！