setenforce 0
)或配置為寬松模式,防火墻開放SQL Server默認端口(1433)、Always On端點端口(5022)。在所有節點執行以下命令安裝必要工具:
sudo yum install -y wget curl yum-utils
下載并添加Microsoft官方SQL Server倉庫:
sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2022.repo
使用YUM安裝SQL Server引擎:
sudo yum install -y mssql-server
運行配置腳本,設置SA密碼(需符合復雜度要求)和實例版本:
sudo /opt/mssql/bin/mssql-conf setup
選擇“Enterprise”或“Standard”版本(根據需求),完成安裝后啟動服務:
sudo systemctl start mssql-server
sudo systemctl enable mssql-server
在每個節點的SQL Server實例中啟用Always On:
USE master;
GO
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Always On Availability Groups', 1;
RECONFIGURE;
GO
重啟SQL Server服務使配置生效:
sudo systemctl restart mssql-server
若需更高級的高可用性(如跨節點自動故障轉移),需創建WSFC:
Install-WindowsFeature -Name Failover-Clustering
)。在主節點(Primary Replica)上執行以下SQL命令,創建可用性組:
CREATE AVAILABILITY GROUP [YourAGName]
FOR DATABASE [YourDatabaseName] -- 替換為目標數據庫
REPLICA ON
'Node1' WITH (
ENDPOINT_URL = 'TCP://Node1_IP:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, -- 同步提交(強一致性)
FAILOVER_MODE = AUTOMATIC, -- 自動故障轉移
PRIMARY_ROLE (ALLOW_CONNECTIONS = ALL), -- 主節點允許所有連接
SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY) -- 輔助節點只讀
),
'Node2' WITH (
ENDPOINT_URL = 'TCP://Node2_IP:5022',
FAILOVER_MODE = AUTOMATIC
);
GO
在輔助節點(Secondary Replica)上執行以下命令,將節點加入可用性組:
ALTER AVAILABILITY GROUP [YourAGName]
ADD REPLICA ON
'Node2' WITH (
ENDPOINT_URL = 'TCP://Node2_IP:5022',
FAILOVER_MODE = AUTOMATIC
);
GO
在主節點上備份數據庫,并還原到輔助節點(需使用WITH NORECOVERY
選項):
-- 主節點備份
BACKUP DATABASE [YourDatabaseName]
TO DISK = '/var/opt/mssql/backup/YourDatabaseName.bak'
WITH INIT, STATS = 10;
GO
-- 輔助節點還原(在輔助節點執行)
RESTORE DATABASE [YourDatabaseName]
FROM DISK = '/var/opt/mssql/backup/YourDatabaseName.bak'
WITH NORECOVERY, MOVE 'YourDatabaseName_Data' TO '/var/opt/mssql/data/YourDatabaseName.mdf',
MOVE 'YourDatabaseName_Log' TO '/var/opt/mssql/data/YourDatabaseName.ldf';
GO
在主節點上執行以下命令,授予連接權限并驗證狀態:
-- 授予連接權限
ALTER AVAILABILITY GROUP [YourAGName]
GRANT CONNECT TO [YourLoginName]; -- 替換為實際登錄名
GO
-- 驗證可用性組狀態
SELECT ag.name AS AGName, ar.replica_server_name AS ReplicaName,
ars.role_desc AS Role, ars.synchronization_health_desc AS SyncStatus
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;
GO
若未使用WSFC,可通過Pacemaker+Corosync實現集群編排:
在所有節點執行:
sudo yum install -y pacemaker corosync pcs
編輯/etc/corosync/corosync.conf
,定義節點通信:
totem {
version: 2
cluster_name: sql_cluster
transport: udpu
interface {
ringnumber: 0
bindnetaddr: 192.168.1.0 # 替換為節點所在網段
mcastport: 5405
}
}
nodelist {
node {
ring0_addr: Node1_IP
nodeid: 1
}
node {
ring0_addr: Node2_IP
nodeid: 2
}
}
quorum {
provider: corosync_votequorum
two_node: 1 # 兩節點集群啟用
}
啟動Corosync和Pacemaker:
sudo systemctl start corosync
sudo systemctl start pacemaker
sudo systemctl enable corosync
sudo systemctl enable pacemaker
創建SQL Server資源腳本(sql_resource.sh
):
#!/bin/bash
sudo pcs resource create mssqlha ocf:mssql:fci \
op monitor interval=30s \
op start timeout=120s \
op stop timeout=120s
sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 \
ip=192.168.1.100 # 虛擬IP(客戶端連接地址)
sudo pcs constraint colocation add virtualip with mssqlha INFINITY
sudo pcs constraint order mssqlha then virtualip
執行腳本并驗證集群狀態:
chmod +x sql_resource.sh
./sql_resource.sh
sudo pcs status
sqlcmd
連接虛擬IP,驗證數據庫訪問。pcs status
查看狀態)。sysadmin
角色)。