第三篇——第二部分——第六文 监控SQL Server镜像

原文出处：http://blog.csdn.net/dba_huangzj/article/details/26846203

要优化，首先要监控，看看是否有性能问题，如果有，在哪里。才能开始真正的优化，所以本文以监控为入口，在上一篇已经略微提供了一些监控方面的信息

针对监控部分，本文将介绍以下内容：

1. 监控组件
2. 警告阈值
3. 数据库镜像监视器
4. 关于镜像的系统存储过程
5. 性能计数器

1.1. 监控组件：

• 数据库镜像状态表：

数据库镜像状态存储在MSDB中的dbm_monitor_data表中，每当数据库镜像状态更改时，会通过sp_dbmmonitorupdate系统存储过程创建这个表。比如在前面的域环境搭建镜像中，可以看到下面的结果：

首先按照http://blog.csdn.net/dba_huangzj/article/details/27652857 中step 6的方式把两台机器加入注册管理器中：

 

 

然后执行下面语句，可以看到有一些有价值的信息：

SELECT  * 
FROM    msdb.dbo.dbm_monitor_data 
ORDER BY local_time DESC

其中比较有价值的是role、各种rate等。

• 数据库镜像状态作业：

默认情况下，每分钟运行一次，用于更新数据库镜像状态表。运行间隔从1分钟到120分钟。这个作业实际上是调用sp_dbmmonitorupdate系统存储过程。如果SQL Server Agent没有启用，那么这个作业也不能运行，这回导致前面说到的镜像状态表的数据变得过时。

这个作业在使用SSMS开始数据库会话是自动创建，但是可以使用下面的系统存储过程自定义作业，详细内容可以查看联机丛书：

• sp_dbmmonitoraddmonitoring：创建数据库镜像监视器作业，该作业可定期更新服务器实例上每个镜像库的镜像状态。注意，如果周期太低客户端的响应时间可能会增加。比如把周期设为1分钟：EXEC sp_dbmmonitoraddmonitoring 1
• sp_dbmmonitorchangemonitoring：更改数据库镜像监视参数的值，比如把更新周期更改为5分钟：EXEC sp_dbmmonitorchangemonitoring 1,5;
• sp_dbmmonitordropmonitoring：停止并删除服务器实例上所有数据库镜像监视器作业。

警告阈值：

如果你使用过镜像监视器，会发现有一个警告选项，如图：

留意右下角的说明，通过阈值，可以设置一系列的应对措施，下面简要说明一下各个阈值：

• 未发送日志（unsent log）：错误号32042，在主体服务器上设置，在高性能模式下非常重要，如果数据库镜像被暂停，即使是高安全模式下也可以用这个阈值。
• 未还原日志（unrestored log）：错误号32043，在镜像服务器上设置，过量的未还原日志意味着镜像服务器的I/O子系统可能存在性能问题导致镜像服务器无法跟上主体服务器的进度。
• 最早未发送事务（Oldest Unsent transaction）：错误号32040，在主体服务器上设置，在高性能模式下尤其重要。
• 镜像提交开销（Mirro commit overhead）：错误号32044，在镜像服务器上配置，设置平均事务延时提交的毫秒数。这个值是主体服务器等待镜像服务器确认已经redo的时间。这个时间在高安全模式下才有意义，因为高性能模式下主体服务器不需要等待镜像服务器的确认信息。

阈值的使用在本文的【结合其他工具进行监控】和【关于镜像的系统存储过程】部分详细介绍。

数据库镜像监视器：

打开数据库镜像监视器：

可以通过下面方式打开镜像监视器：

注册镜像数据库：

如果配置好镜像，打开监视器之后就有一些相关信息，但是由于某些原因要配置镜像库的信息，可以使用注册镜像数据库的功能，如图：

然后进行注册：

默认情况下，SQL Server会优先以Windows 身份验证链接，点击确定之后，就完成添加工作。

使用监视器进行性能检查：

镜像监视器是很好的监控镜像性能的工具，使用这个工具，我们首先要看镜像状态，一般出现【已同步】或者短时间的【正在同步】是允许的，但是其他状态下，就需要检查是否有问题。监控窗口没30秒刷新一次，如果你不是sysadmin角色而是dbm_monitor数据库角色，需要等待镜像监控作业自己更新。如果是sysadmin可以手动刷新。

通过点击【历史记录】可以查看镜像的传输的历史信息。

我们可以尝试看看变动，在主体服务器中写一个死循环不停插入数据，然后看看监视器的结果，记得在查看之后停止死循环：

可以看到插入过程的确产生了日志传输。

关于镜像的系统存储过程：

前面略微提到过一些系统存储过程，实际上，镜像甚至其他高可用技术都提供了一系列系统存储过程来实现几乎甚至全部的GUI功能，作为DBA，会用系统存储过程是非常必要的。下面介绍主要的一些系统存储过程，更详细的信息请自行查看联机丛书：

• sp_dbmmonitorresults：通过返回数据库镜像状态表的信息监控，必须在MSDB库中执行。

在这个结果中，有些列要说明一下：

role

? 1: Principal
? 2: Mirror

mirroring_state

? 0: Suspended
? 1: Disconnected
? 2: Synchronizing
? 3: Pending Failover
? 4: Synchronized

witness_state

? 0: Unknown
? 1: Connected
? 2: Disconnected

• sp_dbmmonitorchangealert：用于添加或修改特定的警告阈值。下面语句是创建一个阈值，当AdventureWorks2008R2库中【最早未发送事务】超过20分钟即发出警告。

  USE msdb 
  GO 
  EXEC sp_dbmmonitorchangealert  
        @database_name = N‘AdventureWorks2008R2‘, 
        @alert_id = 1, 
        @threshold = 20, 
        @enabled = 1

  
  
  

• sp_dbmmonitorhelpalert：返回特定库中的阈值信息。如：

  USE msdb 
  GO 
  EXEC sp_dbmmonitorhelpalert  
        @database_name = N‘AdventureWorks2008R2‘, 
        @alert_id = 1

  
  
  

• sp_dbmmonitordropalert：通过把阈值设为NULL来移除警告阈值，如：

  USE msdb 
  GO 
  EXEC sp_dbmmonitordropalert  
        @database_name = N‘AdventureWorks2008R2‘, 
        @alert_id = 1 

  
  
  

性能计数器

性能计数器在可见的将来（个人估计也有2020年之前）都是很重要的监控工具，并且依托强大的功能，可以进行性能分析、警告发送等等，别大量DBA用于日常管理。关于数据库镜像，计数器主要集中在：SQLServer:Database Mirroring 对象中，下面列出一些常用的计数器：

也可以在SSMS中通过sys.dm_os_performance_counters 这个DMV查询，如：

SELECT object_name, 
       counter_name, 
       instance_name, 
       cntr_value 
FROM sys.dm_os_performance_counters 
WHERE object_name like ‘%mirror%‘

通过上面介绍的方法，对数据库镜像进行状态、阈值等方面的监控，可以尽可能快地响应镜像出现的问题。