监控SQL Server事务复制

gggggds

　　监控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 ='Subscription Information
　　

　　
Subscriber   Subscriber Database   Latency(seconds)
　　
Undistributed Commands   Estimated Catch Up Time'
　　
-- 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