监控SQL Server事务复制

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

监控SQL Server事务复制

 

通常,我们可以使用SSMS的复制监视器来监控复制。但我们不能24小时盯着看,得使用自动化的方式来监控它。微软在distribution数据库提供了系统存储过程dbo.sp_replmonitorsubscriptionpendingcmds,用于返回订阅上等待的命令数,以及需要投递所有这些命令到订阅者的时间的预估。我创建了一个每10分钟运行的作业,保存状态的历史记录数据到一个表,数据保留14天。

 

这个表在订阅者服务器的DBA数据库创建,代码如下:

1
2
3
4
5
6
7
8
9
10
CREATE  TABLE  dbo.Replication_Qu_History(
Subscriber_db  varchar (50)  NOT  NULL ,
Records_In_Que  numeric (18, 0)  NULL ,
CatchUpTime  numeric (18, 0)  NULL ,
LogDate datetime  NOT  NULL ,
CONSTRAINT  PK_EPR_Replication_Que_History  PRIMARY  KEY  CLUSTERED
(
Subscriber_db  ASC , LogDate  DESC
ON  PRIMARY
GO

 

表里数据通过监控存储过程生成,可以通过历史数据查找问题。然而更需要监控现在发生了什么。

 

有三个事可以帮助确定复制的健康情况。

1. 复制相关作业的状态。

2. 延时,尤其是计数器Dist:Delivery Latency衡量的分发延时。

3. 订阅等待的大量未执行命令数。

 

我将注意力集中在了分发延时,因为从过去的经验告诉我,相比日志读取延时,分发延时的问题更加突出。多数时候,分发延时是由于事务量的增加。例如,在发布数据的一个大表上做索引重建,会导致事务日志量的骤然增加,结果导致比正常情况更多的数据需要被复制。

 

如果有大量的命令等待被分发,有时候可能是分发代理作业没有运行。另一方面,有时候是这个作业在运行,但是没有跟上。通过重启代理,作业开始处理未执行的命令。

 

开始之前,我们需要知道复制的信息,像发布者和订阅者的名字、分发代理作业的名字等等。微软在分发数据库中提供了一些存储过程来收集这些信息。笔者的分发数据库和订阅者数据库在一起,所以相比在不同的服务器,脚本更加简单些。

1. 首先,在分发数据库执行sp_replmonitorhelppublisher获取所有发布者的监控信息。

2. 然后,在分发数据库执行sp_replmonitorhelppublication返回所有发布的监控信息。

3. 最后,执行sp_replmonitorhelpsubscription返回所有订阅的监控信息。

 

这个信息包含一些延时指标数据,所以执行这个存储过程后,我已经有些关键信息了。

 

以下是用于收集信息的代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
DECLARE  @cmd NVARCHAR( max )
DECLARE  @publisher SYSNAME, @publisher_db SYSNAME, @publication SYSNAME, @pubtype  INT
DECLARE  @subscriber SYSNAME, @subscriber_db SYSNAME, @subtype  INT
DECLARE  @cmdcount  INT , @processtime  INT
DECLARE  @ParmDefinition NVARCHAR(500)
DECLARE  @JobName SYSNAME
DECLARE  @minutes  INT , @threshold  INT , @maxCommands  INT , @mail  CHAR (1) =  'N'
SET  @minutes = 60  --> Define how many minutes latency before you would like to be notified
SET  @maxCommands = 80000  ---> change this to represent the max number of outstanding commands to be proceduresed before notification
SET  @threshold = @minutes * 60
SELECT  INTO  #PublisherInfo
FROM  OPENROWSET( 'SQLOLEDB' 'SERVER=(LOCAL);TRUSTED_CONNECTION=YES;'
'SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelppublisher' )
SELECT  @publisher = publisher  FROM  #PublisherInfo
SET  @cmd =  'SELECT * INTO ##PublicationInfo FROM OPENROWSET(' 'SQLOLEDB' ',' 'SERVER=(LOCAL);TRUSTED_CONNECTION=YES' '
,' 'SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelppublication @publisher='
+ @publisher +  '' ')'
--select @cmd
EXEC  sp_executesql @cmd
SELECT  @publisher_db=publisher_db, @publication=publication, @pubtype=publication_type  FROM  ##PublicationInfo
SET  @cmd =  'SELECT * INTO ##SubscriptionInfo FROM OPENROWSET(' 'SQLOLEDB' ',' 'SERVER=(LOCAL);TRUSTED_CONNECTION=YES' '
,' 'SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelpsubscription @publisher='
+ @publisher +  ',@publication_type='  CONVERT ( CHAR (1),@pubtype) +  '' ')'
--select @cmd
EXEC  sp_executesql @cmd
ALTER  TABLE  ##SubscriptionInfo
ADD  PendingCmdCount  INT  NULL ,
EstimatedProcessTime  INT  NULL

 

在知道了发布者和订阅者的基本信息后,然后,检查分发作业的状态。它们应该一直在运行。如果没有运行,你要启动它。如果我需要重启一个作业,我会设置标识强制发送邮件告警。

 

我不是为了发送邮件告警而已,是为了检查所有订阅的状态。如果设置的数据超过了设置的阈值,将会触发邮件告警。我用一个游标遍历所有的订阅,这是最容易的收集信息的方法。我将这个信息作为其他存储过程的参数,用于确定分发代理是否正在运行,还可以重启代理。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
DECLARE  cur_sub  CURSOR  READ_ONLY  FOR
SELECT  @publisher, s.publisher_db, s.publication, s.subscriber, s.subscriber_db, s.subtype, s.distribution_agentname
FROM  ##SubscriptionInfo s
OPEN  cur_sub
FETCH  NEXT  FROM  cur_sub  INTO  @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subtype, @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
SET  @cmd =  'SELECT @cmdcount=pendingcmdcount, @processtime=estimatedprocesstime FROM OPENROWSET(' 'SQLOLEDB' ',' 'SERVER=(LOCAL);TRUSTED_CONNECTION=YES' '
,' 'SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorsubscriptionpendingcmds @publisher='  + @publisher
',@publisher_db='  + @publisher_db +  ',@publication='  + @publication
',@subscriber='  + @subscriber +  ',@subscriber_db='  + @subscriber_db
',@subscription_type='  CONVERT ( CHAR (1),@subtype) +  ';'  '' ')'
SET  @ParmDefinition = N '@cmdcount INT OUTPUT,
@processtime INT OUTPUT'
--select @cmd
EXEC  sp_executesql @cmd,@ParmDefinition,@cmdcount  OUTPUT , @processtime  OUTPUT
UPDATE  ##SubscriptionInfo
SET  PendingCmdCount = @cmdcount
, EstimatedProcessTime = @processtime
WHERE  subscriber_db = @subscriber_db
INSERT  INTO  DBA.dbo.Replication_Que_History
VALUES (@subscriber_db, @cmdcount, @processtime, GETDATE())
-- find out if the distribution job with the high number of outstanding commands running or not
-- if it is running then sometimes stopping and starting the agent fixes the issue
IF EXISTS( SELECT  FROM  tempdb.INFORMATION_SCHEMA.TABLES  WHERE  TABLE_NAME  LIKE  '##JobInfo%' )
DROP  TABLE  ##JobInfo
SET  @cmd =  'SELECT * INTO ##JobInfo FROM OPENROWSET(' 'SQLOLEDB' ',' 'SERVER=(LOCAL);TRUSTED_CONNECTION=YES' '
,' 'SET FMTONLY OFF EXEC msdb.dbo.sp_help_job @job_name=' '' ''
+ @JobName +  '' '' ',@job_aspect=' '' 'JOB' '' '' ')'
EXEC  sp_executesql @cmd
IF @cmdcount > @maxCommands  OR  (@processtime > @threshold  AND  @cmdcount > 0)
BEGIN
IF ( SELECT  current_execution_status  FROM  ##JobInfo) = 1  -- This means job is currently executing so stop/start it
BEGIN
EXEC  distribution.dbo.sp_MSstopdistribution_agent
@publisher = @publisher
, @publisher_db = @publisher_db
, @publication = @publication
, @subscriber = @subscriber
, @subscriber_db = @subscriber_db
WAITFOR DELAY  '00:00:05'  ---- 5 Second Delay
SET  @mail =  'Y'
END
END
--SELECT name, current_execution_status FROM ##JobInfo
IF ( SELECT  current_execution_status  FROM  ##JobInfo) <> 1  -- if the job is not running start it
BEGIN
EXEC  distribution.dbo.sp_MSstartdistribution_agent
@publisher = @publisher
, @publisher_db = @publisher_db
, @publication = @publication
, @subscriber = @subscriber
, @subscriber_db = @subscriber_db
SET  @mail =  'Y'  -- Send email if job has stopped and needed to be restarted
END
DROP  TABLE  ##JobInfo
FETCH  NEXT  FROM  cur_sub  INTO  @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subtype, @JobName
END
CLOSE  cur_sub
DEALLOCATE  cur_sub

 

运行sp_replmonitorsubscriptionpendingcmds收集未执行的命令和预计跟上的时间。

 

这是我想在历史表里存储的信息,因此我可以了解到复制执行得怎样了。

 

我们需要确定一个可以接受的延时阈值。我这里使用6分钟,意思是,如果复制的数据库落后于发布数据库多余6分钟,将受到告警。还要确定未分发命令的最大数量。如果这个数量向上波动,可能会有问题。你可以选择在让这个数字设置为多高时才采取行动。我选择让这个系统有80000个未分发命令。

 

在让复制队列检查作业运行了两周后,我获取了这些数据。确保这些作业像索引重建作业一样运行。我查看了一段时间未分发命令的最大数量和最大延时,并确定我的设置值会更大些。我不想因为索引重建作业导致的系统临时备份而在晚上被叫醒,这是会自动恢复的。

 

以下的代码需要启用Ad Hoc Distributed Queries服务器配置选项。假设之前的脚本发现了问题,我创建了发送邮件的脚本。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
IF @mail =  'Y'
BEGIN
DECLARE  @msg  VARCHAR ( MAX ) =  'Replication on '  + @@SERVERNAME
' may be experiencing some problems. Attempts to restart the distribution agent have been made. '
'If this is not the first message like this that you have received within the last hour, please investigate.'
DECLARE  @body NVARCHAR( MAX )
DECLARE  @xml1 NVARCHAR( MAX )
DECLARE  @tab1 NVARCHAR( MAX )
DECLARE  @xml2 NVARCHAR( MAX )
DECLARE  @tab2 NVARCHAR( MAX )
SET  @xml1 =  CAST ((  SELECT  subscriber  AS  'td' , '' ,subscriber_db  AS  'td' , '' ,
latency  AS  'td' , '' , PendingCmdCount  AS  'td' , '' , EstimatedProcessTime  AS  'td'
FROM  ##SubscriptionInfo s
FOR  XML PATH( 'tr' ), ELEMENTS )  AS  NVARCHAR( MAX ))
SET  @tab1 = '<html><body><H4>Subscription Information </H4>
<table border = 1> <tr>
<th> Subscriber </th> <th> Subscriber Database </th> <th> Latency(seconds)</th>
<th> Undistributed Commands </th> <th> Estimated Catch Up Time</th></tr>'
-- this command gives us the last 10 measurements of latency for each subscriber
SET  @xml2 =  CAST ((  SELECT  s.Subscriber_db  AS  'td' , '' , s.Records_In_Que  AS  'td' , '' , s.CatchUpTime  AS  'td' , '' CONVERT ( CHAR (22),LogDate, 100)  AS  'td'
FROM  ( SELECT  ROW_NUMBER() OVER ( PARTITION  BY  subscriber_db  ORDER  BY  Logdate  DESC  AS  'RowNumber' ,
subscriber_db
, Records_In_Que
, CatchUpTime
, Logdate
FROM  DBA.dbo.Replication_Que_History
) s
WHERE  RowNumber <= 8
FOR  XML PATH( 'tr' ), ELEMENTS )  AS  NVARCHAR( MAX ))
SET  @tab2 = '<br><br><H4>Historical Latency Information </H4>
<table border = 1>
<tr>
<th>Subscriber</th> <th>Undistributed Commands</th> <th> Catch Up Time </th> <th> Date\Time </th></tr>'
SET  @body = @msg + @tab1 + @xml1 +  '</table>'
+ @tab2 + @xml2 +  '</body></html>'
DECLARE  @ to  NVARCHAR(200)
SELECT  @ to  ''  -- INSERT YOUR EMAIL ADDRESS HERE
EXEC  msdb.dbo.sp_send_dbmail
@body = @body,
@body_format = 'HTML' ,
@recipients = @ to ,
@subject =  'Possible Replication Problem'  ;
END
DROP  TABLE  #PublisherInfo
DROP  TABLE  ##PublicationInfo
DROP  TABLE  ##SubscriptionInfo

 

最后,需要定期删除复制状态表的数据,以便数据不会太旧。

1
2
3
DECLARE  @delDate datetime = getdate()-10
DELETE  FROM  DBA.dbo.Replication_Que_History
WHERE  LogDate < @deldate

 

如果该脚本中配置的任何阈值匹配上,与有问题的计数器的订阅相关的发布代理将会重启,如果已经停止,作业将会启动。你将会受到该动作的通知邮件。在很多情况下,重启分发代理会解决问题,复制又开始工作。如果依然没有修复这个问题,那么作业下次运行相同的动作,又收到另一封邮件。你需要着手检查下这种情况。

 

你可以在你的告警系统里调用第3个脚本,当任何阈值匹配时重启分发代理作业。或者,运行第1个脚本创建表。创建新的作业,在第1步运行后面3个脚本,然后将第5个脚本放到第2步。我当前每10分钟运行这个调度。

 

这个进程主要是为了帮助处理事务复制的间歇性停工。使用复制监视器定期监视复制进程仍然重要。这个进程只是为了阻止下班时间的电话骚扰,只需要启动下分发代理作业就可以修复。


















本文转自UltraSQL51CTO博客,原文链接: http://blog.51cto.com/ultrasql/1907283,如需转载请自行联系原作者







相关实践学习
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
26天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
1月前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
252 1
|
4天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
10天前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
21 2
|
15天前
|
SQL 存储 测试技术
|
14天前
|
SQL 机器学习/深度学习 搜索推荐
SQL SERVER 转换失败
【6月更文挑战第25天】
|
19天前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之如何迁移SQL Server
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
25天前
|
SQL 监控 安全
代码审计-PHP原生开发篇&SQL注入&数据库监控&正则搜索&文件定位&静态分析
代码审计-PHP原生开发篇&SQL注入&数据库监控&正则搜索&文件定位&静态分析
|
28天前
|
SQL 存储 关系型数据库
关系型数据库中的SQL Server
【6月更文挑战第11天】
55 3
|
26天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程