管理SQL Server AlwaysOn（5）——常規監控（1）——常規監控

標籤：property   添加   資料庫   eve   lis   man   from   fill   dbn   

本文屬於管理SQL Server AlwaysOn 系列文章

前言：
前面幾節提到了如何對AlwaysOn做常規管理，這一節和接下來的一節專門對“監控”進行解釋和示範。管理和監控這兩個詞在很多時候是混淆的，但是我們大概也可以區分出來，比如我做備份，算管理，對錯誤、異常進行響應這也是管理，但是對錯誤、異常的捕獲和通知DBA這就是監控了，而且監控有時候是不需要進行幹預的，比如我監控磁碟空間，當空間充足的時候，我可以不管。在日常的DBA工作中，我本人對監控的重視程度遠大於所謂的管理，因為有了合理的監控，你就可以預見很多問題，實際幹預的工作量也會少很多，在很多地方還能進行自動化管理等。還能發現效能問題。所以，我認為在DBA工作中，應該以監控先行為重。另外，監控往往也跟警報綁定在一起，以便觸發監控閾值時快速通知責任人。對於只有少量的可用性群組需要監控時，SSMS提供的AlwaysOn儀表板和AlwaysOn健康跟蹤（AlwaysOn Health Trace）就可以作為監控的工具。囉嗦了那麼多，我們下面進入正式的講解。

AlwaysOn儀表板（Dashboard）：
儀表板是具有一定程度互動的報表介面，用於顯示AlwaysOn環境的情況。可以通過所示開啟：


開啟之後會看到這個樣子：

從第二個圖看到，【同步狀態】這一列，會有三種可能的內容：已同步（SYNCHRONIZED）、未同步（NOT SYNCHRONIZING）、正在同步（SYNCHRONIZING）。對於同步複本，應該為“已同步”狀態，其他狀態都意味著不正常。對於非同步副本，卻不應該顯示“已同步”，而是應該為“正在同步”。但是不管哪種同步模式，“未同步”狀態都意味著不正常。

對於同步狀態，每個副本還可能有以下幾種操作狀態：  PENDING_FAILOVER ,  PENDING ,  ONLINE ,  OFFLINE ,  FAILED ,  FAILED_NO_QUORUM和NULL（當副本未串連時）。詳細內容可以從聯機叢書中查看：sys.dm_hadr_availability_replica_states 和 角色和操作狀態

的右上方，有三個超連結：【啟動容錯移轉嚮導】、【查看AlwaysOn運行狀態事件】、【查看群集仲裁資訊】，其中第二個【查看AlwaysOn運行狀態事件】是一些內建的擴充事件，下節會介紹。第三個包含了現有群集的配置資訊，如：

接下來在右方的【添加/刪除列】中：

點開之後可以看到下面的內容：

在這裡，可以動態添加和移除需要顯示在儀表板上的列。也可以在空白處右鍵，然後進行組的展開和摺疊操作：

AlwaysOn健康跟蹤（Health Trace）：
AlwaysOn健康跟蹤是一個擴充事件會話，關於擴充事件可以看我之前的系列文章：SQL Server 擴充事件（Extented Events）從入門到進階（1）——從SQL Trace到Extented Events。當第一次建立可用性群組的時候就會自動建立。可以在所示的地方看到並開啟：

通過開啟擴充事件，可以看到即時捕獲的資料，或者通過修改配置擷取定製的配置，另外也可以通過AlwaysOn儀表板中右上方第二個選項【查看AlwaysOn 健全狀態事件】來查看。

由於這個功能本質上屬於擴充事件範疇，關於擴充事件可以見我前面給出的連結文章，所以在這裡不累贅。這個功能很重要，我們後續的很多監控指令碼都或多或少借用擴充事件來實現。關於常規監控的基礎部分講到這裡，下一節“管理SQL Server AlwaysOn（5）——常規監控（2）——擴充事件監控”會專門介紹如何使用擴充事件進行監控。另外，在本文中，我儘可能多地把一些非擴充事件的監控指令碼給出來以便讀者參考。


監控內容參考：
從工作經曆和國外專家總結的知識來看，對於SQL Server AlwaysOn，需要監控的內容主要有以下幾個，如有收集，我也會陸續補上：

1. WSFC的健康情況、配置資訊。
2. SQL Server TCP接聽程式資訊。
3. SQL Server檔案所在盤的空間。（這部分專題形式展現，完成之後提供連結）
4. AlwaysOn接聽程式。
5. Failover（或者SQL Server 可用性群組、執行個體的狀態變更）。
6. AlwaysOn資料庫的狀態變更。
7. 日誌傳輸、重做速率。

AlwaysOn監控指令碼示範：
1. T-SQL尋找當前SQL執行個體是否主副本：
由於很多操作都只能在主副本上執行，而且從管理的角度，也有必要知道當前的主副本是哪一個，所以我們首先需要找到主副本在哪裡：

IF SERVERPROPERTY (‘IsHadrEnabled‘) = 1BEGINSELECT   AGC.name -- 可用性群組名 , RCS.replica_server_name -- SQL叢集節點名 , ARS.role_desc  -- 副本角色 , AGL.dns_name  -- 接聽程式名FROM  sys.availability_groups_cluster AS AGCINNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCSON RCS.group_id = AGC.group_idINNER JOIN sys.dm_hadr_availability_replica_states AS ARSON ARS.replica_id = RCS.replica_idINNER JOIN sys.availability_group_listeners AS AGLON AGL.group_id = ARS.group_idWHERE  ARS.role_desc = ‘PRIMARY‘ --限定主副本，如果不限定，則可以查看所有副本END

下面兩個圖分別是有WHERE條件和沒有WHERE條件的結果：

2. 從sys.messages中查看AlwaysOn的的錯誤：
在這裡，我們使用sys.messages目錄檢視，關於這個視圖的說明可以看聯機叢書：sys.messages  ，我們使用下面的T-SQL語句檢查記錄在冊的AlwaysOn相關的錯誤，這個錯誤可以在後續發出警告時使用，這裡示範的只是最簡單的樣子，讀者可以進行適當的修改以滿足自身需求：

SELECT message_id [error_number], severity, --嚴重性，在1~25之間textFROM sys.messages WHERE text LIKE (‘%availability%‘)AND  is_event_logged = 1;--1=出現錯誤時將訊息計入時間日誌

3. AlwaysOn所用到的DMV：

select * from sys.dm_hadr_clusterselect * from sys.dm_hadr_cluster_membersselect * from sys.dm_hadr_cluster_networksselect * from sys.availability_groupsselect * from sys.availability_groups_clusterselect * from sys.dm_hadr_availability_group_statesselect * from sys.availability_replicasselect * from sys.dm_hadr_availability_replica_cluster_nodesselect * from sys.dm_hadr_availability_replica_cluster_statesselect * from sys.dm_hadr_availability_replica_statesselect * from sys.dm_hadr_auto_page_repairselect * from sys.dm_hadr_database_replica_statesselect * from sys.dm_hadr_database_replica_cluster_statesselect * from sys.availability_group_listener_ip_addressesselect * from sys.availability_group_listenersselect * from sys.dm_tcp_listener_states

4. SQL Server TCP接聽程式資訊：

SELECT listener_id, ip_address, is_ipv4, port, type_desc, state_desc, start_timeFROM sys.dm_tcp_listener_states WITH (NOLOCK) OPTION (RECOMPILE);

5. AG狀態：

-- AG Status DECLARE @HADRName VARCHAR(25)SET @HADRName = @@SERVERNAMESELECT n.group_name,n.replica_server_name,n.node_name,rs.role_desc,db_name(drs.database_id) AS ‘DBName‘,drs.synchronization_state_desc,drs.synchronization_health_descFROM sys.dm_hadr_availability_replica_cluster_nodes nJOIN sys.dm_hadr_availability_replica_cluster_states cs ON n.replica_server_name = cs.replica_server_nameJOIN sys.dm_hadr_availability_replica_states rs ON rs.replica_id = cs.replica_idJOIN sys.dm_hadr_database_replica_states drs ON rs.replica_id = drs.replica_idWHERE n.replica_server_name <> @HADRName

6. SQL Server代理警報：

USE masterGOSELECT message_id AS ErrorNumber,TEXTFROM sys.messagesWHERE TEXT LIKE (‘%availability%‘)AND language_id = 1033

可以看到很多內容：
這裡整理了一些可能有用的錯誤號碼做後續的擴充：

關於AG有用的錯誤號碼

ErrorNumber	警告名	原因
1480	AG角色變更（Failover）	暗示著可用性群組發生了容錯移轉，需要檢查轉移原因並確定是否需要轉移回去。
976	資料庫不可訪問（Database Not Accessible）	資料庫不可用於查詢，可能資料掛起或次要複本不可用於讀訪問。
983	資料庫不可訪問（Database Not Accessible）	副本角色正在解析（resolving），資料庫不可訪問，檢查SQL 錯誤記錄檔和網路、儲存及其他相關的錯誤資訊事件記錄），可能因為啟動、容錯移轉、通訊或者群集錯誤引起。
3402	資料庫正在還原（Database Restoring）	資料庫正在還原或者其他不能進行恢複的狀態，檢查資料庫是否損壞或者在掛起狀態。
19406	AG副本狀態變更（AG Replica Changed States）	副本狀態因為啟動、容錯移轉、通訊或者群集故障等問題導致變更。檢查日誌再細分處理手段。
35206	連線逾時（Connection Timeout）	檢查網路及防火牆問題，或者是否有副本傳輸故障。
35250	聯結資料庫失敗（The connection to the primary replica is not active）	因為對主副本的串連失敗導致命令無法處理，檢查SQL錯誤記錄檔是否真正偵聽連接埠及所有IP。
35264	資料移動掛起（Data Movement Suspended）	需要手動恢複資料移動，檢查SQL日誌找到原因。
35273	資料庫不可訪問（Database Inaccessible）	因為與主副本的會話中斷導致恢複失敗。可能由於WSFC仲裁、連結、端點配置或者許可權問題導致。
35274	資料庫恢複掛起（Database Recovery Pending）	次要複本在等待接收來自於主副本的交易記錄才能恢複聯機，確保主副本所在的執行個體為聯機狀態。
35275	資料庫掛起（Database in Suspect State）	資料庫處於潛在損壞的狀態，不能串連到可用性群組。還原資料庫並重連可用性群組。
35276	資料庫不同步（Database Out of Sync）	需要手工幹預以便恢複同步。
41091	副本離線（Replica Going Offline）	如果重複出現需要檢查原因。副本離線可能因為租用到期或更新失敗。或者網路問題，或者sp_server_diagnostic查詢逾時。
41131	AG聯機失敗（Failed to Bring AG Online）	確認WSFC節點是否聯機，並且在WSFC群集中存在AG資源
41142	副本不能成為主要角色（Replica Cannot Become Primary）	1個或多個資料庫不同步或者不能串連到可用性群組，或者群集以強制仲裁模式啟動。
41406	AG未為自動容錯移轉做好準備（AG Not Ready for Auto Failover）	主次副本以自動容錯移轉模式配置，但是次要複本未準備成功。副本可能處於不可用狀態。檢查次要複本的其他資訊。
41414	次要複本未串連（Secondary Not Connected）	最少一個次要複本不能串連到主副本。檢查SQL錯誤記錄檔是否連接埠和IP偵聽正常。

針對如1480即AG角色變更，我們可以在SQL 代理的警告中添加監控，模版如下，一旦發生變更則發送郵件給DBA：

EXEC msdb.dbo.sp_add_alert@name = N‘[Name of Alert]’,@message_id = 1480,@severity = 0,@enabled = 1,@delay_between_responses = 0,@include_event_description_in = 1;GOEXEC msdb.dbo.sp_add_notification@alert_name = N‘[Name of Alert]’,@operator_name = N‘[Operator]’,@notification_method = 1;GO

其他部分的監控將在後續逐步完善。






7. 相關效能計數器：下面兩個計數器是專門用於AlwaysOn的計數器，可以瞭解當前可用性群組的運行健康狀態和效能表現。SQLServer:Availability Replica和SQLServer:Database Replica

• SQLServer:Availability Replica：監控粒度為可用性群組層級。
  
• SQLServer:Database Replica：監控粒度為可用性群組中的資料庫層級。
  

這兩個計數器使用期間需要區分在主副本還是次要複本中，有些是均可監控，有些是僅對某種角色才有意義。下面列表來自於《SQL Server 2012實施與管理實戰指南》p135中的內容：

不同角色的可用性複本可使用的AlwaysOn效能計數器

計數器名	主副本	次要複本/輔助副本
Availability Replica:Sends to Replica / Sec	√	√
Availability Replica:Receives from Replica / Sec	√	√
Availability Replica:Bytes Sent to Replica / Sec	√	√
Availability Replica:Bytes Received from Replica / Sec	√	√
Availability Replica:Sends to Transport / Sec	√	√
Availability Replica:Bytes Sent to Transport / Sec	√	√
Availability Replica:Resent Messages / Sec	√	√
Availability Replica:Flow Control Time	√
Availability Replica:Flow Control / Sec	√
Database Replica:Redo Bytes Remaining		√
Database Replica:Log Bytes Received / Sec		√
Database Replica:File Bytes Received / Sec		√
Database Replica:Log Remaining to Undo		√
Database Replica:Total Log Requiring Undo		√
Database Replica:Redone Bytes / Sec		√
Database Replica:Recovery Queue		√
Database Replica:Log Send Queue		√
Database Replica:Transaction Delay	√
Database Replica:Mirrored Write Transactions / Sec	√

計數器的具體解釋請自行搜尋。另外我們可以通過下面的T-SQL命令來查詢計數器的內容。

select object_name,counter_name,instance_name,cntr_valuefrom sys.dm_os_performance_counters where object_name like ‘%replica%‘

總結：
到目前為止，介紹了對AlwaysOn的常規監控內容，但是具體的細化內容及對應解決方案需要在工作中不斷積累和改進。所以暫時沒辦法完整的列出來，在本人工作過程中如果有新方法，會繼續更新。

管理SQL Server AlwaysOn（5）——常規監控（1）——常規監控