丐版sqlserver AlwaysOn叢集

赚窝囊费的小苏發表於2024-04-12
SQLServer
點選檢視程式碼 
丐版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

相關文章