SQL Server 2016 + AlwaysOn 無域叢集

目錄

• AlwaysOn
  • 搭建 WSFC
  • 配置 AlwaysOn
  • 可讀副本的負載均衡
  • 使用故障轉移

[========]

AlwaysOn

AlwaysOn 可用性組概述 (SQL Server)

搭建 WSFC

配置計算機的 DNS 字尾

1. 配置計算機的 DNS 字尾，注意在同個工作組
   
   
   
   

2. 每個節點的機器都要做域名解析，修改 host 檔案C:\Windows\System32\drivers\etc
   

安裝故障轉移叢集

1. 節點伺服器新增故障轉移群集功能
   

2. 等待安裝完成

驗證叢集

1. 開啟故障轉移叢集管理工具
   
2. 點選驗證配置
   
3. 新增叢集節點
   
4. 執行所有測試
   
5. 可以點選檢視驗證報告，勾選立即使用經過驗證的節點建立叢集
   

建立叢集

1. 配置叢集的管理名稱和管理IP
   

2. 等待配置完成
   

3. 可以檢視叢集報告，稍後配置檔案共享見證

建立檔案共享見證

由於我們是兩個節點的故障轉移叢集，所以需要加上共享資料夾，如果是奇數節點，這一步是不需要做的！

1. 配置叢集仲裁
   
2. 選擇檔案共享見證
   
   
3. 在叢集節點之外的一臺伺服器上建立共享資料夾\\XIANGMU4TEST01\share，並設定 Everyone 完全控制的許可權

配置 AlwaysOn

1. 開啟 AlwaysOn
   
   

2. 需要重啟 SQL Server 服務

3. 檢查，如果 AlwaysOn 啟用成功，在伺服器屬性裡可以看到啟用HADR為True
   
   

4. 檢視各節點的投票數

SELECT * FROM  sys.dm_hadr_cluster_members;

1. 檢視 cluster

SELECT * FROM SYS.[dm_hadr_cluster]

新建可用性組

但是既然節點沒有加入AD，那麼久不能用域認證，只能用證照認證，因此需要建立證照和端點。在配置可用性組前各節點進行證照認證信任。

1. 修改 SQL 服務登陸賬號為本機管理員賬號
   
   

2. 建立證照，兩個節點都要建立證照，注意修改證照名稱

--節點二：建立主金鑰/證照，備份證照。
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AAA111aaa';
GO
CREATE CERTIFICATE Cert_server228
WITH SUBJECT = 'Cert_server228',
START_DATE = '2017-01-01',EXPIRY_DATE = '2099-12-30';
GO
BACKUP CERTIFICATE Cert_server228
TO FILE = 'C:\Storage\Cert_server228.cer';
GO

注意：備份證照的資料夾要先建立好，並且賦予許可權

1. 將建立好的證照放到另一臺節點伺服器，並加入證照

--節點二：建立其他節點證照
USE master;
GO
CREATE CERTIFICATE Cert_server227
FROM FILE = 'C:\Storage\Cert_server227.cer';
GO

證照建立好後如下

1. 重新建立端點，授權賬號設定為本機管理員賬號，驗證方式使用上面建立的證照

--節點：建立端點
CREATE ENDPOINT [testag_endpoint]
    AUTHORIZATION [POSTEST2-2016\Administrator]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
    FOR DATA_MIRRORING
    (ROLE = ALL,AUTHENTICATION = CERTIFICATE Cert_server228, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

如果存在已有的端點，則需要將已有端點刪除

1. 在主節點伺服器操作新建可用性組。
   

2. 設定可用性組名稱，建議勾選資料庫級別執行狀況檢測。
   

• 在之前的 AlwaysOn 2012 和 2014 中，如果例項健康出現問題，將觸發故障轉移。如果有一個資料庫有問題，只要例項OK，可用性組就不會故障轉移。
• 在 AlwaysOn 2016 中，勾選之後，不論是一個例項有問題，還是一個或多個資料庫有問題，都會發生故障轉移。

1. 選擇資料庫，要求資料庫已經進行了完整備份
   

2. 新增副本節點，並勾選自動故障轉移，設定輔助副本為可讀副本
   

3. 將端點 URL 設定為 IP 方式，這裡端點會顯示為我們上面建立的端點，登陸賬號為我們上面設定的賬號
   

4. “備份首選項”和“偵聽器”不需要設定，保持預設就行，可用性偵聽器我們後面再新增，可以直接點選“下一步”

5. 資料同步這裡建議使用完整的資料庫和日誌備份的方式更方便，需要有一個共享資料夾，並且節點伺服器要有相同的資料庫檔案目錄結構

6. 等待可用性組建立成功
   

7. 建立成功後資料庫狀態顯示為已同步
   
   故障轉移叢集會顯示擁有一個群集角色
   
   

8. 顯示皮膚可以檢視叢集資訊
   

建立偵聽器

一個偵聽器包括虛擬IP地址、虛擬網路名稱、埠號三個元素，一旦建立成功，虛擬網路名稱會註冊到DNS中，同時為可用性組資源新增IP地址資源和網路名稱資源。使用者就可以使用此名稱來連線到可用性組中。

1. 新增偵聽器
   

2. 選擇使用靜態 IP
   

3. 建立成功後，在故障轉移叢集管理器裡的角色節點，可以看到客戶端訪問名稱和 IP 地址
   

4. 連線資料庫時使用偵聽器的地址
   

可讀副本的負載均衡

SQL Server 2016 支援多個只讀副本負載分擔只讀操作。

右鍵一個可用性副本可以檢視副本的只讀性設定：

主角色中的連線

主角色中支援的連線訪問型別：

• 允許所有連線（ALL）：主資料庫同時允許讀寫連線和只讀連線。這是主角色的預設行為。
• 僅允許讀/寫連線（READ_WRITE）：允許 ApplicationIntent=ReadWrite 或未設定連線條件的連線。不允許 ApplicationIntent=ReadOnly 的連線。僅允許讀寫連線可幫助防止客戶錯誤地將讀意向工作負荷連線到主副本。

修改指令碼：

USE [master]
GO
ALTER AVAILABILITY GROUP [posTestAg]
MODIFY REPLICA ON N'POSTEST1-2016\POSTEST' WITH (PRIMARY_ROLE(ALLOW_CONNECTIONS = READ_WRITE))
GO

可讀輔助副本

輔助角色支援的連線訪問型別：

• 無連線（NO）：不允許任何使用者連線。輔助資料庫不可用於讀訪問。這是輔助角色中的預設行為。
• 僅讀意向連線（READ_ONLY）：輔助資料庫僅接受連線引數 ApplicationIntent=ReadOnly 的連線，其它的連線方式無法連線。
• 允許任何只讀連線（ALL）：輔助資料庫全部可用於讀訪問連線。此選項允許較低版本的客戶端進行連線。

修改指令碼：

USE [master]
GO
ALTER AVAILABILITY GROUP [posTestAg]
MODIFY REPLICA ON N'POSTEST1-2016\POSTEST' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
GO

設定資料庫例項監聽埠

1. Sql Server 配置管理工具找到資料庫例項的網路配置，
   

2. 右鍵 TCP/IP，預設配置的是動態埠
   

3. 這裡修改為固定埠 1433
   

4. 最後需要重啟 Sql Server 服務。

配置可用性組只讀路由

執行下面 sql 獲取可用性組名稱：

select name,* from  sys.availability_groups

執行下面 sql 獲取要配置的可用性副本名稱（伺服器例項）：

select replica_server_name,* from sys.availability_replicas

1. 為可用性副本（伺服器例項）配置只讀路由 URL，路由地址可以填 IP 或者計算機名，埠填資料庫例項繫結的 TCP/IP 埠。
   執行指令碼：

USE [master]
GO
ALTER AVAILABILITY GROUP [posTestAg]
MODIFY REPLICA ON N'POSTEST1-2016\POSTEST' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://10.98.98.227:1433'))
GO
ALTER AVAILABILITY GROUP [posTestAg]
MODIFY REPLICA ON N'POSTEST2-2016\POSTEST' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://10.98.98.228:1433'))
GO

也可以右鍵可用性組選擇屬性，通過 UI 方式更改

1. 每個自動轉移副本配置只讀路由列表

USE [master]
GO
ALTER AVAILABILITY GROUP [posTestAg]
MODIFY REPLICA ON
N'POSTEST1-2016\POSTEST' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('POSTEST2-2016\POSTEST'),'POSTEST1-2016\POSTEST')));
GO
ALTER AVAILABILITY GROUP [posTestAg]
MODIFY REPLICA ON
N'POSTEST2-2016\POSTEST' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('POSTEST1-2016\POSTEST'),'POSTEST2-2016\POSTEST')));
GO

使用“()”對只讀路由列表進行分組，
List 1: 'POSTEST1-2016\POSTEST'
List 2: 'POSTEST2-2016\POSTEST'
按如下方式工作：

1. 路由到第一個列表中的副本，如果第一個列表存在多個副本，則對讀取連線進行輪詢分發。
2. 如果第一列表中任一副本不可用了，路由將繼續到第一個列表中的其他可用副本。
3. 如果第一個列表中的所有副本都不可訪問，將會路由到下一個列表

4. 如果第一個列表中的任一副本可用，將會恢復路由到第一個列表

5. 測試只讀路由效果，sqlcmd 使用 Readonly 指定為只讀連線，

sqlcmd -S 10.98.98.231 -K Readonly -d test -Q "select @@servername" -e

只讀連線被連線到輔助副本

使用 SSMS 或者 EF 等需要在連線字串設定引數ApplicationIntent=ReadOnly

注意：
想要使用輔助副本負載讀操作，必須要在連線字串中配置為只讀連線，然後在程式中指定使用只讀連線進行資料庫讀取操作，AlwaysOn 自身不能自動負載均衡。

配置賬號

為每個資料庫節點配置登陸賬號，然後通過監聽器使用該賬號登陸資料庫。

1. 主副本建立登陸賬號 satest

2. 檢視主庫上該賬號的 sid 為庫名

SELECT * FROM [test]..sysusers

1. 在副本資料庫上建立對應賬號，其中 sid對應的值是主庫上所查到的sid的值

CREATE LOGIN [satest] WITH PASSWORD=N'xxxxxxx',
SID =0x99AD266AFD26F841B3E49EF9633B0D4B, DEFAULT_DATABASE=[test],
 CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

使用故障轉移

建議使用 SSMS 中 AlwaysOn 進行手動故障轉移。