丐版sqlserver AlwaysOn集群

点击查看代码

丐版sqlserver集群之前试过docker的，k8s的，然后发现，还是最朴素的是最简单的，希望有大佬能够汉化，他妈的，那些英文看得人要发癫啊。前置准备，参照丐版pxc集群:
https://www.cnblogs.com/zwnfdswww/p/18112077
如果不关防火墙:
打开对应的端口即可:
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanentsudo firewall-cmd --reloadsudo hostnamectl set-hostname m191
sudo hostnamectl set-hostname m192
sudo hostnamectl set-hostname m193bash
vim /etc/hosts10.1.161.29 m191
10.1.161.31 m192
10.1.161.32 m193sudo 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@1613systemctl status mssql-serveryum install mssql-server-agent/opt/mssql/bin/mssql-conf set sqlagent.enabled true
systemctl restart mssql-server.servicesudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.reposudo yum remove unixODBC-utf16 unixODBC-utf16-develsudo yum install -y mssql-tools unixODBC-develecho 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profileecho 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrcsource ~/.bashrcsqlcmd -S 10.1.161.32 -U SA -P 'Citygis@1613'CREATE DATABASE TestDBSELECT Name from sys.DatabasesGO
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  1sudo systemctl restart mssql-serverBash（all）
yum install -y mssql-server-ha 
yum info mssql-server-haSql（all）：ALTER EVENT SESSION  AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);GOSql(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_certificateTO 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_certificateAUTHORIZATION dbm_userFROM 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 --permanentsudo firewall-cmd --reloadSql（all）;select @@SERVERNAME;Sql（主）：CREATE AVAILABILITY GROUP [ag1]
WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL)
FOR REPLICA ONN'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.confSet to 1 to disable certificate validation:
insecure = 1sudo subscription-manager registersudo subscription-manager list --availablesudo 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 -yBash（all）：passwd hacluster  （这里密码一定要设置成一样的，我这设置的是123456.com）Bash（all）：sudo systemctl enable pcsdsudo systemctl start pcsdsudo systemctl enable pacemakerfirewall-cmd --add-service=high-availability --zone=public --permanentfirewall-cmd --zone=public --add-port=2224/tcp --permanentfirewall-cmd --zone=public --add-port=3121/tcp –permanentfirewall-cmd --zone=public --add-port=5405/udp --permanent firewall-cmd --reloadBash（all）：sudo pcs cluster destroysudo systemctl enable pacemakerBash（主）：sudo pcs cluster auth m191 m192 m193 -u hacluster -p 123456.comsudo pcs cluster setup --name AG1 m191 m192 m193 chown -R hacluster.haclient /var/log/clusterpcs cluster start --all
pcs cluster enable –allpcs cluster statusps aux | grep pacemakercorosync-cfgtool -scorosync-cmapctl | grep memberspcs status corosynccrm_verify -L -V（all）：
pcs property set stonith-enabled=falsepcs property set no-quorum-policy=ignoreBash（all）：sudo pcs property set stonith-enabled=falseBash（all）：yum install mssql-server-ha –ysudo systemctl restart mssql-serverSql（all）:USE [master]GOCREATE LOGIN [pacemakerLogin] with PASSWORD= N'1111.aaa';ALTER SERVER ROLE [sysadmin] ADD MEMBER [pacemakerLogin]Bash（all）：sudo echo 'pacemakerLogin' >> ~/pacemaker-passwdsudo echo '1111.aaa' >> ~/pacemaker-passwdsudo mv ~/pacemaker-passwd /var/opt/mssql/secrets/passwdsudo chown root:root /var/opt/mssql/secrets/passwdsudo chmod 400 /var/opt/mssql/secrets/passwdBash（主）重要，ip记得改sudo pcs resource create ag_cluster ocf:mssql:ag ag_name=AG1 meta failure-timeout=60s master notify=truesudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=10.1.161.70执行完之后查看是否绑定成功
sudo pcs resource show
看下虚拟ip在哪里，去相应的主机
ip  addr showBash（主）sudo pcs constraint colocation add virtualip ag_cluster-master INFINITY with-rsc-role=Mastersudo pcs constraint order promote ag_cluster-master then start virtualipsudo pcs status测试:
navicat连一下10.1.161.70,1433
Citygis@1613 Citygis@1613
Sql（VIP）：Sql（VIP）：-- group infoSELECTg.name as ag_name,rgs.primary_replica,rgs.primary_recovery_health_desc as recovery_health,rgs.synchronization_health_desc as sync_healthFrom sys.dm_hadr_availability_group_states as rgsJOIN sys.availability_groups AS gON rgs.group_id = g.group_id--replicas infoSELECTg.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_descriptionFrom sys.dm_hadr_availability_replica_states AS  rsJOIN sys.availability_replicas AS rON rs.replica_id = r.replica_idJOIN sys.availability_groups AS gON g.group_id = r.group_id--DB levelSElECTg.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_stateFROM sys.dm_hadr_database_replica_states AS drsJOIN sys.availability_replicas AS rON r.replica_id = drs.replica_idJOIN sys.availability_groups AS gON g.group_id = drs.group_idORDER BY g.name, drs.is_primary_replica DESC;GOSQL Server Always On的同步原理:所有的事务会被提交到主副本，辅助副本去读取物理日志来同步，新建的数据库需要加入到ag里面Pacemaker的监控原理：会用被动心跳来检查,如果发现节点有问题，会通过三角轮转进行迁移，然后还能对节点进行监控引用:
[1] https://www.cnblogs.com/guarderming/p/12082936.html