点击查看代码
丐版sqlserver集群
之前试过docker的，k8s的，然后发现，还是最朴素的是最简单的，希望有大佬能够汉化，他妈的，那些英文看得人要发癫啊。
前置准备，参照丐版pxc集群:
https://www.cnblogs.com/zwnfdswww/p/18112077
如果不关防火墙:
打开对应的端口即可:
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload
sudo hostnamectl set-hostname m191
sudo hostnamectl set-hostname m192
sudo hostnamectl set-hostname m193
bash
vim /etc/hosts
10.1.161.29 m191
10.1.161.31 m192
10.1.161.32 m193
sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo
sudo yum install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup
设置密码:
Citygis@1613
systemctl status mssql-server
yum install mssql-server-agent
/opt/mssql/bin/mssql-conf set sqlagent.enabled true
systemctl restart mssql-server.service
sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel
sudo yum install -y mssql-tools unixODBC-devel
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
sqlcmd -S 10.1.161.32 -U SA -P 'Citygis@1613'
CREATE DATABASE TestDB
SELECT Name from sys.Databases
GO
USE TestDB
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
SELECT * FROM Inventory WHERE quantity > 152;
GO
QUIT
需要几台服务器，重复安装即可
测试：
navicat连一下
10.1.161.29,1433
SA Citygis@1613
如果没有驱动，去navicat目录下安装sqlncli_x64即可
sql(all)
将 SA 帐户禁用：
ALTER LOGIN SA DISABLE;
CREATE LOGIN Citygis@1613 WITH PASSWORD = 'Citygis@1613';
ALTER SERVER ROLE sysadmin ADD MEMBER Citygis@1613;
重要:新用户登录
ALTER LOGIN SA DISABLE;
Bash（all）：
sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server
Bash（all）
yum install -y mssql-server-ha
yum info mssql-server-ha
Sql（all）：
ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO
Sql(all):
CREATE LOGIN dbm_login WITH PASSWORD = '1111.aaa';
CREATE USER dbm_user FOR LOGIN dbm_login;
第一个是登录用户，第二个是执行用户
Sql（主）：
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1111.aaa';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
WITH PRIVATE KEY (
FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
ENCRYPTION BY PASSWORD = '1111.aaa'
);
ls /var/opt/mssql/data
看下文件有没有生成
Bash（主）：
cd /var/opt/mssql/data/
scp dbm_certificate.* 10.1.161.31:/var/opt/mssql/data/
scp dbm_certificate.* 10.1.161.32:/var/opt/mssql/data/
Bash（从）;
cd /var/opt/mssql/data/
chown mssql.mssql dbm_certificate.*
Sql（从）：
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1111.aaa';
CREATE CERTIFICATE dbm_certificate
AUTHORIZATION dbm_user
FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
WITH PRIVATE KEY (
FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
DECRYPTION BY PASSWORD = '1111.aaa'
);
Sql（all）;
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;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];
sudo firewall-cmd --zone=public --add-port=5022/tcp --permanent
sudo firewall-cmd --reload
Sql（all）;
select @@SERVERNAME;
Sql（主）：
CREATE AVAILABILITY GROUP [ag1]
WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL)
FOR REPLICA ON
N'm191'
WITH (
ENDPOINT_URL = N'tcp://m191:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = EXTERNAL,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
N'm192'
WITH (
ENDPOINT_URL = N'tcp://m192:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = EXTERNAL,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
N'm193'
WITH (
ENDPOINT_URL = N'tcp://m193:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = EXTERNAL,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
Sql（从）：
ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = EXTERNAL);
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE
（如果报错，可能是hosts文件里面主机名对应ip错了）
测试一下:
Sql（主）：
CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
TO DISK = N'/var/opt/mssql/data/db1.bak';
ALTER AVAILABILITY GROUP [AG1] ADD DATABASE [db1];
从节点查一下。
集群完成。
DROP AVAILABILITY GROUP group_name
可选:
Bash（all）
sudo yum install subscription-manager
用户名和密码去redhat官网申请
vi /etc/rhsm/rhsm.conf
Set to 1 to disable certificate validation:
insecure = 1
sudo subscription-manager register
sudo subscription-manager list --available
sudo subscription-manager attach --pool=<PoolID>
其中，“PoolId”是上一步中高可用性订阅的池 ID 。
subscription-manager repos --list
选一个高可用相关的软件仓库
sudo subscription-manager repos --enable=rhel-ha-for-rhel-7-server-rpms
(备用:sudo subscription-manager repos --enable=rhel-atomic-7-cdk-3.3-rpms)
如果系统自带了有，可以不执行上面的命令
Bash（all）：
yum install pacemaker pcs resource-agents corosync fence-agents-all -y
Bash（all）：
passwd hacluster （这里密码一定要设置成一样的，我这设置的是123456.com）
Bash（all）：
sudo systemctl enable pcsd
sudo systemctl start pcsd
sudo systemctl enable pacemaker
firewall-cmd --add-service=high-availability --zone=public --permanent
firewall-cmd --zone=public --add-port=2224/tcp --permanent
firewall-cmd --zone=public --add-port=3121/tcp –permanent
firewall-cmd --zone=public --add-port=5405/udp --permanent
firewall-cmd --reload
Bash（all）：
sudo pcs cluster destroy
sudo systemctl enable pacemaker
Bash（主）：
sudo pcs cluster auth m191 m192 m193 -u hacluster -p 123456.com
sudo pcs cluster setup --name AG1 m191 m192 m193
chown -R hacluster.haclient /var/log/cluster
pcs cluster start --all
pcs cluster enable –all
pcs cluster status
ps aux | grep pacemaker
corosync-cfgtool -s
corosync-cmapctl | grep members
pcs status corosync
crm_verify -L -V
（all）：
pcs property set stonith-enabled=false
pcs property set no-quorum-policy=ignore
Bash（all）：
sudo pcs property set stonith-enabled=false
Bash（all）：
yum install mssql-server-ha –y
sudo systemctl restart mssql-server
Sql（all）:
USE [master]
GO
CREATE LOGIN [pacemakerLogin] with PASSWORD= N'1111.aaa';
ALTER SERVER ROLE [sysadmin] ADD MEMBER [pacemakerLogin]
Bash（all）：
sudo echo 'pacemakerLogin' >> ~/pacemaker-passwd
sudo echo '1111.aaa' >> ~/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
Bash（主）
重要，ip记得改
sudo pcs resource create ag_cluster ocf:mssql:ag ag_name=AG1 meta failure-timeout=60s master notify=true
sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=10.1.161.70
执行完之后查看是否绑定成功
sudo pcs resource show
看下虚拟ip在哪里，去相应的主机
ip addr show
Bash（主）
sudo pcs constraint colocation add virtualip ag_cluster-master INFINITY with-rsc-role=Master
sudo pcs constraint order promote ag_cluster-master then start virtualip
sudo pcs status
测试:
navicat连一下
10.1.161.70,1433
Citygis@1613 Citygis@1613
Sql（VIP）：
Sql（VIP）：
-- group info
SELECT
g.name as ag_name,
rgs.primary_replica,
rgs.primary_recovery_health_desc as recovery_health,
rgs.synchronization_health_desc as sync_health
From sys.dm_hadr_availability_group_states as rgs
JOIN sys.availability_groups AS g
ON rgs.group_id = g.group_id
--replicas info
SELECT
g.name as ag_name,
r.replica_server_name,
rs.is_local,
rs.role_desc as role,
rs.operational_state_desc as op_state,
rs.connected_state_desc as connect_state,
rs.synchronization_health_desc as sync_state,
rs.last_connect_error_number,
rs.last_connect_error_description
From sys.dm_hadr_availability_replica_states AS rs
JOIN sys.availability_replicas AS r
ON rs.replica_id = r.replica_id
JOIN sys.availability_groups AS g
ON g.group_id = r.group_id
--DB level
SElECT
g.name as ag_name,
r.replica_server_name,
DB_NAME(drs.database_id) as [database_name],
drs.is_local,
drs.is_primary_replica,
synchronization_state_desc as sync_state,
synchronization_health_desc as sync_health,
database_state_desc as db_state
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS r
ON r.replica_id = drs.replica_id
JOIN sys.availability_groups AS g
ON g.group_id = drs.group_id
ORDER BY g.name, drs.is_primary_replica DESC;
GO
SQL Server Always On的同步原理:
所有的事务会被提交到主副本，辅助副本去读取物理日志来同步，新建的数据库需要加入到ag里面
Pacemaker的监控原理：
会用被动心跳来检查,如果发现节点有问题，会通过三角轮转进行迁移，然后还能对节点进行监控
引用:
[1] https://www.cnblogs.com/guarderming/p/12082936.html
