監控SQL Server事務復制

監控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分鐘運行這個調度。

這個進程主要是為了幫助處理事務復制的間歇性停工。使用復制監視器定期監視復制進程仍然重要。這個進程只是為了阻止下班時間的電話騷擾，只需要啟動下分發代理作業就可以修復。