丐版sqlserver AlwaysOn集群

丐版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