監控SQL Server事務複製

標籤：replication   事務複製   sql server replication   

監控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 PRIMARYGO

 

表裡資料通過監控儲存區過程產生，可以通過曆史資料尋找問題。然而更需要監控現在發生了什麼。

 

有三個事可以協助確定複製的健康情況。

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 INTDECLARE @subscriber SYSNAME, @subscriber_db SYSNAME, @subtype INTDECLARE @cmdcount INT, @processtime INTDECLARE @ParmDefinition NVARCHAR(500)DECLARE @JobName SYSNAMEDECLARE @minutes INT, @threshold INT, @maxCommands INT, @mail CHAR(1) = ‘N‘SET @minutes = 60 --> Define how many minutes latency before you would like to be notifiedSET @maxCommands = 80000 ---> change this to represent the max number of outstanding commands to be proceduresed before notificationSET @threshold = @minutes * 60SELECT * INTO #PublisherInfoFROM OPENROWSET(‘SQLOLEDB‘, ‘SERVER=(LOCAL);TRUSTED_CONNECTION=YES;‘, ‘SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelppublisher‘)SELECT @publisher = publisher FROM #PublisherInfoSET @cmd = ‘SELECT * INTO ##PublicationInfo FROM OPENROWSET(‘‘SQLOLEDB‘‘,‘‘SERVER=(LOCAL);TRUSTED_CONNECTION=YES‘‘,‘‘SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelppublication @publisher=‘+ @publisher + ‘‘‘)‘--select @cmdEXEC sp_executesql @cmdSELECT @publisher_db=publisher_db, @publication=publication, @pubtype=publication_type FROM ##PublicationInfoSET @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 @cmdEXEC sp_executesql @cmdALTER TABLE ##SubscriptionInfoADD PendingCmdCount INT NULL,EstimatedProcessTime INT NULL

 

在知道了發行者和訂閱者的基本資料後，然後，檢查分發作業的狀態。它們應該一直在運行。如果沒有運行，你要啟動它。如果我需要重啟一個作業，我會設定標識強制發送郵件警示。

 

我不是為了發送郵件警示而已，是為了檢查所有訂閱的狀態。如果設定的資料超過了設定的閾值，將會觸發郵件警示。我用一個遊標遍曆所有的訂閱，這是最容易的收集資訊的方法。我將這個資訊作為其他預存程序的參數，用於確定分發代理是否正在運行，還可以重啟代理。

DECLARE cur_sub CURSOR READ_ONLY FORSELECT @publisher, s.publisher_db, s.publication, s.subscriber, s.subscriber_db, s.subtype, s.distribution_agentnameFROM ##SubscriptionInfo sOPEN cur_subFETCH NEXT FROM cur_sub INTO @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subtype, @JobNameWHILE @@FETCH_STATUS = 0BEGINSET @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 @cmdEXEC sp_executesql @cmd,@ParmDefinition,@cmdcount OUTPUT, @processtime OUTPUTUPDATE ##SubscriptionInfoSET PendingCmdCount = @cmdcount, EstimatedProcessTime = @processtimeWHERE subscriber_db = @subscriber_dbINSERT INTO DBA.dbo.Replication_Que_HistoryVALUES(@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 issueIF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘##JobInfo%‘)DROP TABLE ##JobInfoSET @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 @cmdIF @cmdcount > @maxCommands OR (@processtime > @threshold AND @cmdcount > 0)BEGINIF (SELECT current_execution_status FROM ##JobInfo) = 1 -- This means job is currently executing so stop/start itBEGINEXEC distribution.dbo.sp_MSstopdistribution_agent@publisher = @publisher, @publisher_db = @publisher_db, @publication = @publication, @subscriber = @subscriber, @subscriber_db = @subscriber_dbWAITFOR DELAY ‘00:00:05‘ ---- 5 Second DelaySET @mail = ‘Y‘ENDEND--SELECT name, current_execution_status FROM ##JobInfoIF (SELECT current_execution_status FROM ##JobInfo) <> 1 -- if the job is not running start itBEGINEXEC distribution.dbo.sp_MSstartdistribution_agent@publisher = @publisher, @publisher_db = @publisher_db, @publication = @publication, @subscriber = @subscriber, @subscriber_db = @subscriber_dbSET @mail = ‘Y‘ -- Send email if job has stopped and needed to be restartedENDDROP TABLE ##JobInfoFETCH NEXT FROM cur_sub INTO @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subtype, @JobNameENDCLOSE cur_subDEALLOCATE cur_sub

 

運行sp_replmonitorsubscriptionpendingcmds收集未執行的命令和預計跟上的時間。

 

這是我想在曆史表裡儲存的資訊，因此我可以瞭解到複製執行得怎樣了。

 

我們需要確定一個可以接受的延時閾值。我這裡使用6分鐘，意思是，如果複製的資料庫落後於發行集資料庫多餘6分鐘，將受到警示。還要確定未分發命令的最大數量。如果這個數量向上波動，可能會有問題。你可以選擇在讓這個數字設定為多高時才採取行動。我選擇讓這個系統有80000個未分發命令。

 

在讓複製隊列檢查作業運行了兩周后，我擷取了這些資料。確保這些作業像索引重建作業一樣運行。我查看了一段時間未分發命令的最大數量和最大延時，並確定我的設定值會更大些。我不想因為索引重建作業導致的系統臨機操作備份而在晚上被叫醒，這是會自動回復的。

 

以下的代碼需要啟用Ad Hoc Distributed Queries伺服器配置選項。假設之前的指令碼發現了問題，我建立了發送郵件的指令碼。

IF @mail = ‘Y‘BEGINDECLARE @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 sFOR 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 subscriberSET @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, LogdateFROM DBA.dbo.Replication_Que_History) sWHERE RowNumber <= 8FOR 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 HEREEXEC msdb.dbo.sp_send_dbmail@body = @body,@body_format =‘HTML‘,@recipients = @to,@subject = ‘Possible Replication Problem‘ ;ENDDROP TABLE #PublisherInfoDROP TABLE ##PublicationInfoDROP TABLE ##SubscriptionInfo

 

最後，需要定期刪除複製狀態表的資料，以便資料不會太舊。

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

 

如果該指令碼中配置的任何閾值匹配上，與有問題的計數器的訂閱相關的發布代理將會重啟，如果已經停止，作業將會啟動。你將會受到該動作的通知訊息。在很多情況下，重啟分發代理會解決問題，複製又開始工作。如果依然沒有修複這個問題，那麼作業下次運行相同的動作，又收到另一封郵件。你需要著手檢查下這種情況。

 

你可以在你的警示系統裡調用第3個指令碼，當任何閾值匹配時重啟分發代理作業。或者，運行第1個指令碼建立表。建立新的作業，在第1步運行後面3個指令碼，然後將第5個指令碼放到第2步。我當前每10分鐘運行這個調度。

 

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

本文出自 “SQL Server Deep Dive” 部落格，請務必保留此出處http://ultrasql.blog.51cto.com/9591438/1907283

監控SQL Server事務複製