您现在的位置是:首页 > 文章详情

监控SQL Server事务复制

日期:2017-03-16点击:539

监控SQL Server事务复制

 

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

 

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

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返回所有订阅的监控信息。

 

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

 

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

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

 

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

 

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

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服务器配置选项。假设之前的脚本发现了问题,我创建了发送邮件的脚本。

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

 

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

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

 

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

 

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

 

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



原文链接:https://blog.51cto.com/ultrasql/1907283
关注公众号

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。

持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。

文章评论

共有0条评论来说两句吧...

文章二维码

扫描即可查看该文章

点击排行

推荐阅读

最新文章