監(jiān)控SQL Server事務(wù)復制

成都創(chuàng)新互聯(lián)公司作為成都網(wǎng)站建設(shè)公司，專注成都網(wǎng)站建設(shè)公司、網(wǎng)站設(shè)計，有關(guān)成都定制網(wǎng)站方案、改版、費用等問題，行業(yè)涉及砂巖浮雕等多個領(lǐng)域，已為上千家企業(yè)服務(wù)，得到了客戶的尊重與認可。

通常，我們可以使用SSMS的復制監(jiān)視器來監(jiān)控復制。但我們不能24小時盯著看，得使用自動化的方式來監(jiān)控它。微軟在distribution數(shù)據(jù)庫提供了系統(tǒng)存儲過程dbo.sp_replmonitorsubscriptionpendingcmds，用于返回訂閱上等待的命令數(shù)，以及需要投遞所有這些命令到訂閱者的時間的預估。我創(chuàng)建了一個每10分鐘運行的作業(yè)，保存狀態(tài)的歷史記錄數(shù)據(jù)到一個表，數(shù)據(jù)保留14天。

這個表在訂閱者服務(wù)器的DBA數(shù)據(jù)庫創(chuàng)建，代碼如下：

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

表里數(shù)據(jù)通過監(jiān)控存儲過程生成，可以通過歷史數(shù)據(jù)查找問題。然而更需要監(jiān)控現(xiàn)在發(fā)生了什么。

有三個事可以幫助確定復制的健康情況。

1. 復制相關(guān)作業(yè)的狀態(tài)。

2. 延時，尤其是計數(shù)器Dist:Delivery Latency衡量的分發(fā)延時。

3. 訂閱等待的大量未執(zhí)行命令數(shù)。

我將注意力集中在了分發(fā)延時，因為從過去的經(jīng)驗告訴我，相比日志讀取延時，分發(fā)延時的問題更加突出。多數(shù)時候，分發(fā)延時是由于事務(wù)量的增加。例如，在發(fā)布數(shù)據(jù)的一個大表上做索引重建，會導致事務(wù)日志量的驟然增加，結(jié)果導致比正常情況更多的數(shù)據(jù)需要被復制。

如果有大量的命令等待被分發(fā)，有時候可能是分發(fā)代理作業(yè)沒有運行。另一方面，有時候是這個作業(yè)在運行，但是沒有跟上。通過重啟代理，作業(yè)開始處理未執(zhí)行的命令。

開始之前，我們需要知道復制的信息，像發(fā)布者和訂閱者的名字、分發(fā)代理作業(yè)的名字等等。微軟在分發(fā)數(shù)據(jù)庫中提供了一些存儲過程來收集這些信息。筆者的分發(fā)數(shù)據(jù)庫和訂閱者數(shù)據(jù)庫在一起，所以相比在不同的服務(wù)器，腳本更加簡單些。

1. 首先，在分發(fā)數(shù)據(jù)庫執(zhí)行sp_replmonitorhelppublisher獲取所有發(fā)布者的監(jiān)控信息。

2. 然后，在分發(fā)數(shù)據(jù)庫執(zhí)行sp_replmonitorhelppublication返回所有發(fā)布的監(jiān)控信息。

3. 最后，執(zhí)行sp_replmonitorhelpsubscription返回所有訂閱的監(jiān)控信息。

這個信息包含一些延時指標數(shù)據(jù)，所以執(zhí)行這個存儲過程后，我已經(jīng)有些關(guān)鍵信息了。

以下是用于收集信息的代碼：

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

在知道了發(fā)布者和訂閱者的基本信息后，然后，檢查分發(fā)作業(yè)的狀態(tài)。它們應該一直在運行。如果沒有運行，你要啟動它。如果我需要重啟一個作業(yè)，我會設(shè)置標識強制發(fā)送郵件告警。

我不是為了發(fā)送郵件告警而已，是為了檢查所有訂閱的狀態(tài)。如果設(shè)置的數(shù)據(jù)超過了設(shè)置的閾值，將會觸發(fā)郵件告警。我用一個游標遍歷所有的訂閱，這是最容易的收集信息的方法。我將這個信息作為其他存儲過程的參數(shù)，用于確定分發(fā)代理是否正在運行，還可以重啟代理。

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收集未執(zhí)行的命令和預計跟上的時間。

這是我想在歷史表里存儲的信息，因此我可以了解到復制執(zhí)行得怎樣了。

我們需要確定一個可以接受的延時閾值。我這里使用6分鐘，意思是，如果復制的數(shù)據(jù)庫落后于發(fā)布數(shù)據(jù)庫多余6分鐘，將受到告警。還要確定未分發(fā)命令的最大數(shù)量。如果這個數(shù)量向上波動，可能會有問題。你可以選擇在讓這個數(shù)字設(shè)置為多高時才采取行動。我選擇讓這個系統(tǒng)有80000個未分發(fā)命令。

在讓復制隊列檢查作業(yè)運行了兩周后，我獲取了這些數(shù)據(jù)。確保這些作業(yè)像索引重建作業(yè)一樣運行。我查看了一段時間未分發(fā)命令的最大數(shù)量和最大延時，并確定我的設(shè)置值會更大些。我不想因為索引重建作業(yè)導致的系統(tǒng)臨時備份而在晚上被叫醒，這是會自動恢復的。

以下的代碼需要啟用Ad Hoc Distributed Queries服務(wù)器配置選項。假設(shè)之前的腳本發(fā)現(xiàn)了問題，我創(chuàng)建了發(fā)送郵件的腳本。

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 <= 8
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @tab2 ='

Historical Latency Information 


Subscriber Undistributed Commands  Catch Up Time   Date\Time '
SET @body = @msg + @tab1 + @xml1 + ''
+ @tab2 + @xml2 + ''
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

最后，需要定期刪除復制狀態(tài)表的數(shù)據(jù)，以便數(shù)據(jù)不會太舊。

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

如果該腳本中配置的任何閾值匹配上，與有問題的計數(shù)器的訂閱相關(guān)的發(fā)布代理將會重啟，如果已經(jīng)停止，作業(yè)將會啟動。你將會受到該動作的通知郵件。在很多情況下，重啟分發(fā)代理會解決問題，復制又開始工作。如果依然沒有修復這個問題，那么作業(yè)下次運行相同的動作，又收到另一封郵件。你需要著手檢查下這種情況。

你可以在你的告警系統(tǒng)里調(diào)用第3個腳本，當任何閾值匹配時重啟分發(fā)代理作業(yè)?；蛘?，運行第1個腳本創(chuàng)建表。創(chuàng)建新的作業(yè)，在第1步運行后面3個腳本，然后將第5個腳本放到第2步。我當前每10分鐘運行這個調(diào)度。

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