首页 > 代码库 > 管理SQL Server AlwaysOn（5）——常规监控（1）——常规监控

管理SQL Server AlwaysOn（5）——常规监控（1）——常规监控

2024-09-08 17:39:44   216人阅读

本文属于管理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‘) = 1
BEGIN
	SELECT
	   AGC.name -- 可用性组名
	 , RCS.replica_server_name -- SQL群集节点名
	 , ARS.role_desc  -- 副本角色
	 , AGL.dns_name  -- 侦听器名
	FROM  sys.availability_groups_cluster AS AGC
			INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
			ON RCS.group_id = AGC.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
			ON ARS.replica_id = RCS.replica_id
			INNER JOIN sys.availability_group_listeners AS AGL
			ON AGL.group_id = ARS.group_id
	WHERE  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之间
text
FROM sys.messages 
WHERE text LIKE (‘%availability%‘)
AND  is_event_logged = 1;--1=出现错误时将消息计入时间日志


3. AlwaysOn所用到的DMV：

select * from sys.dm_hadr_cluster
select * from sys.dm_hadr_cluster_members
select * from sys.dm_hadr_cluster_networks
select * from sys.availability_groups
select * from sys.availability_groups_cluster
select * from sys.dm_hadr_availability_group_states
select * from sys.availability_replicas
select * from sys.dm_hadr_availability_replica_cluster_nodes
select * from sys.dm_hadr_availability_replica_cluster_states
select * from sys.dm_hadr_availability_replica_states
select * from sys.dm_hadr_auto_page_repair
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_database_replica_cluster_states
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_group_listeners
select * from sys.dm_tcp_listener_states

4. SQL Server TCP侦听器信息：

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

技术分享


5. AG状态：


-- AG Status 
DECLARE @HADRName VARCHAR(25)

SET @HADRName = @@SERVERNAME

SELECT 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_desc
FROM sys.dm_hadr_availability_replica_cluster_nodes n
JOIN sys.dm_hadr_availability_replica_cluster_states cs ON n.replica_server_name = cs.replica_server_name
JOIN sys.dm_hadr_availability_replica_states rs ON rs.replica_id = cs.replica_id
JOIN sys.dm_hadr_database_replica_states drs ON rs.replica_id = drs.replica_id
WHERE n.replica_server_name <> @HADRName

技术分享


6. SQL Server代理警报：


USE master
GO

SELECT message_id AS ErrorNumber
	,TEXT
FROM sys.messages
WHERE TEXT LIKE (‘%availability%‘)
	AND language_id = 1033
可以看到很多内容：
技术分享

这里整理了一些可能有用的错误号做后续的扩展：

关于AG有用的错误号
ErrorNumber警告名原因
1480AG角色变更（Failover）暗示着可用性组发生了故障转移，需要检查转移原因并确定是否需要转移回去。
976数据库不可访问（Database Not Accessible）数据库不可用于查询，可能数据挂起或次要副本不可用于读访问。
983数据库不可访问（Database Not Accessible）副本角色正在解析（resolving），数据库不可访问，检查SQL 错误日志和网络、存储及其他相关的错误信息事件日志），可能因为启动、故障转移、通讯或者群集错误引起。
3402数据库正在还原（Database Restoring）数据库正在还原或者其他不能进行恢复的状态，检查数据库是否损坏或者在挂起状态。
19406AG副本状态变更（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查询超时。
41131AG联机失败（Failed to Bring AG Online）确认WSFC节点是否联机，并且在WSFC群集中存在AG资源
41142副本不能成为主要角色（Replica Cannot Become Primary）1个或多个数据库不同步或者不能连接到可用性组，或者群集以强制仲裁模式启动。
41406AG未为自动故障转移做好准备（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;

GO

EXEC 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_value
from sys.dm_os_performance_counters
 where object_name like ‘%replica%‘


总结：


到目前为止，介绍了对AlwaysOn的常规监控内容，但是具体的细化内容及对应解决方案需要在工作中不断积累和改进。所以暂时没办法完整的列出来，在本人工作过程中如果有新方法，会继续更新。

管理SQL Server AlwaysOn（5）——常规监控（1）——常规监控


联系
我们