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

本文属于管理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警告名原因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;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的常规监控内容，但是具体的细化内容及对应解决方案需要在工作中不断积累和改进。所以暂时没办法完整的列出来，在本人工作过程中如果有新方法，会继续更新。