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

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,独享型 2核4GB
日志服务 SLS,月写入数据量 50GB 1个月
简介: 原文: 第三篇——第二部分——第六文 监控SQL Server镜像  原文出处:http://blog.csdn.net/dba_huangzj/article/details/26846203  要优化,首先要监控,看看是否有性能问题,如果有,在哪里。
原文: 第三篇——第二部分——第六文 监控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 对象中,下面列出一些常用的计数器:

 

计数器名称 解释

Bytes Received/sec

每秒收到的字节数。
Bytes Sent/sec 每秒发送的字节数。
Log Bytes Received/sec 每秒收到的日志字节数。
Log Bytes Redone from Cache/sec

在上一秒钟内从镜像日志缓存中获得的重做日志字节数。
此计数器只在镜像服务器上使用。 在主体服务器上此值始终是 0。

Log Bytes Sent from Cache/sec 在上一秒钟内从镜像日志缓存中获得的发送日志字节数。
此计数器只在主体服务器上使用。 在镜像服务器上此值始终是 0。
Log Bytes Sent/sec 每秒发送的日志字节数。
Log Compressed Bytes Rcvd/sec 在上一秒钟内所接收日志的压缩字节数。
Log Compressed Bytes Sent/sec 在上一秒钟内所发送日志的压缩字节数。
Log Harden Time (ms) 日志块在上一秒钟内等待强制写入磁盘的时间(毫秒)。
Log Remaining for Undo KB 在故障转移之后等待由新的镜像服务器扫描的日志总字节数 (KB)。
此计数器仅可在撤消阶段在镜像服务器上使用。 撤销阶段完成后,计数器会重置为 0。 在主体服务器上此值始终是 0。
Log Scanned for Undo KB  

自故障转移开始已由新的镜像服务器扫描的日志总字节数 (KB)。
此计数器仅可在撤消阶段在镜像服务器上使用。 撤销阶段完成后,计数器会重置为 0。 在主体服务器上此值始终是 0。

Log Send Flow Control Time (ms)  

日志流消息在上一秒钟内等待发送流控制的时间(毫秒)。
在数据库镜像中,将日志数据和元数据发送到镜像伙伴是数据量最密集的操作,并可能独占数据库镜像和 Service Broker 发送缓冲区。 使用此计数器可监视数据库镜像会话使用此缓冲区的情况。

Log Send Queue KB 尚未发送到镜像服务器的日志总字节数 (KB)。
Mirrored Write Transactions/sec

在上一秒钟内写入镜像数据库并等待日志发送到镜像数据库以进行提交的事务数。
仅当主体服务器正在向镜像服务器发送日志记录时,此计数器才会增加。

Pages Sent/sec 每秒发送的页数。
Receives/sec 每秒收到的镜像消息数。
Redo Bytes/sec 每秒在镜像数据库中前滚的日志字节数。
Redo Queue KB 当前仍应用于镜像数据库以进行前滚操作的镜像日志的总字节数 (KB)。 此数据将从镜像数据库发送到主体数据库。
Send/Receive Ack Time

在上一秒钟内消息等待伙伴确认的时间(毫秒)。
在解决可能由网络瓶颈导致的问题(例如莫名其妙的故障转移、发送队列很大或事务滞后时间较长)时,此计数器非常有用。 在这些情况下,可以分析此计数器的值来确定是否是由于网络而导致出现上述问题。

Sends/sec 每秒发送的镜像消息数。
Transaction Delay 等待未终止的提交确认的延迟时间。

 

也可以在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%'



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

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
1月前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
1月前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
253 1
|
2天前
|
存储 监控 数据库
局域网监控软件中的Transact-SQL事务管理
**T-SQL事务管理**确保数据的原子性、一致性、隔离性和持久性。使用`BEGIN TRANSACTION`, `COMMIT`和`ROLLBACK`来控制事务。例如,在更新账户余额的事务中,如果所有操作成功则提交,否则回滚。错误处理通过`TRY-CATCH`结构实现,嵌套事务允许在事务内开启新的事务。通过触发器和存储过程,如`DataChangeTrigger`和`SubmitDataToWebsite`,可以自动化数据提交到网站,当表数据变动时触发。
18 5
|
4天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
23 3
|
9天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
15天前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
24 2
|
20天前
|
SQL 存储 测试技术
|
19天前
|
SQL 机器学习/深度学习 搜索推荐
SQL SERVER 转换失败
【6月更文挑战第25天】
|
24天前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之如何迁移SQL Server
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
1月前
|
SQL 监控 安全
代码审计-PHP原生开发篇&SQL注入&数据库监控&正则搜索&文件定位&静态分析
代码审计-PHP原生开发篇&SQL注入&数据库监控&正则搜索&文件定位&静态分析