文檔內(nèi)無特別說明時(shí)命令在所有節(jié)點(diǎn)執(zhí)行先鱼，本文以ROOT身份操作
按照本文檔將會完成以下內(nèi)容：
- 安裝SQL Server 2019
- SQL多節(jié)點(diǎn)證書信任互訪
- SQL鏡像端點(diǎn)配置
- 配置Always On可用組
- 配置Pacemaker在SQL登錄權(quán)限
- 安裝Pacemaker
- 配置mssql-ag
- 配置虛擬IP
| 用途 | 主機(jī)名 | IP |
|---|---|---|
| 主節(jié)點(diǎn) | node01 | 1.1.1.11 |
| 輔助節(jié)點(diǎn) | node02 | 1.1.1.12 |
| 輔助節(jié)點(diǎn) | 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客戶端環(huán)境
curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/8/prod.repo
yum install -y mssql-tools unixODBC-devel
##在彈出的許可確認(rèn)，輸入YES后回車?yán)^續(xù)
加入環(huán)境變量蔬蕊，使命令可用
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
添加hosts解析及變更主機(jī)名
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
修改主機(jī)名镶奉，在各節(jié)點(diǎn)分別執(zhí)行
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語句命令中莺奔，每一個(gè)GO結(jié)束要等待確認(rèn)執(zhí)行完成后再繼續(xù)操作，否則會報(bào)錯(cuò)
創(chuàng)建主密鑰，導(dǎo)出證書并開啟鏡像端口
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
將導(dǎo)出的證書復(fù)制到各節(jié)點(diǎn)相同位置
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*
將各節(jié)點(diǎn)證書到數(shù)據(jù)庫內(nèi)栈虚，完成鏡像節(jié)點(diǎn)互信
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
創(chuàng)建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
將輔助節(jié)點(diǎn)鏈接到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
在主節(jié)點(diǎn)創(chuàng)建數(shù)據(jù)庫并加入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 創(chuàng)建 SQL Server 登錄和權(quán)限
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 創(chuàng)建 SQL Server 登錄和權(quán)限
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 包時(shí)創(chuàng)建的默認(rèn)用戶設(shè)置密碼---
passwd hacluster
##---在各節(jié)點(diǎn)設(shè)置相同的密碼---
配置Pacemaker
node01:
pcs cluster auth node01 node02 node03 -u hacluster
##---輸入前面設(shè)置的密碼---
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隨主節(jié)點(diǎn)遷移
pcs constraint order promote mssql-master then start mssql-vip
pcs property set stonith-enabled=false
##驗(yàn)證當(dāng)前的集群配置是否有錯(cuò)誤，正常情況無輸出
crm_verify -L -V
##檢查狀態(tài)
pcs status
不加入任何參數(shù)直接訪問VIP時(shí)史隆，將訪問主服務(wù)器進(jìn)行讀寫魂务，執(zhí)行讀取操作時(shí)，加入ApplicationIntent=ReadOnly參數(shù)泌射，會僅從輔助節(jié)點(diǎn)讀取數(shù)據(jù)粘姜。執(zhí)行寫入操作時(shí)加入ApplicationIntent=ReadOnly參數(shù)，會返回一個(gè)報(bào)錯(cuò)魄幕。
