监控SQL Server事务复制
通常，我们可以使用SSMS的复制监视器来监控复制。但我们不能24小时盯着看，得使用自动化的方式来监控它。微软在distribution数据库提供了系统存储过程dbo.sp_replmonitorsubscriptionpendingcmds，用于返回订阅上等待的命令数，以及需要投递所有这些命令到订阅者的时间的预估。我创建了一个每10分钟运行的作业，保存状态的历史记录数据到一个表，数据保留14天。
这个表在订阅者服务器的DBA数据库创建，代码如下：
|
1
2
3
4
5
6
7
8
9
10
|
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返回所有订阅的监控信息。
这个信息包含一些延时指标数据，所以执行这个存储过程后，我已经有些关键信息了。
以下是用于收集信息的代码：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
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
|
在知道了发布者和订阅者的基本信息后，然后，检查分发作业的状态。它们应该一直在运行。如果没有运行，你要启动它。如果我需要重启一个作业，我会设置标识强制发送邮件告警。
我不是为了发送邮件告警而已，是为了检查所有订阅的状态。如果设置的数据超过了设置的阈值，将会触发邮件告警。我用一个游标遍历所有的订阅，这是最容易的收集信息的方法。我将这个信息作为其他存储过程的参数，用于确定分发代理是否正在运行，还可以重启代理。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
|
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服务器配置选项。假设之前的脚本发现了问题，我创建了发送邮件的脚本。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
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
|
最后，需要定期删除复制状态表的数据，以便数据不会太旧。
|
1
2
3
|
DECLARE
@delDate datetime = getdate()-10
DELETE
FROM
DBA.dbo.Replication_Que_History
WHERE
LogDate < @deldate
|
如果该脚本中配置的任何阈值匹配上，与有问题的计数器的订阅相关的发布代理将会重启，如果已经停止，作业将会启动。你将会受到该动作的通知邮件。在很多情况下，重启分发代理会解决问题，复制又开始工作。如果依然没有修复这个问题，那么作业下次运行相同的动作，又收到另一封邮件。你需要着手检查下这种情况。
你可以在你的告警系统里调用第3个脚本，当任何阈值匹配时重启分发代理作业。或者，运行第1个脚本创建表。创建新的作业，在第1步运行后面3个脚本，然后将第5个脚本放到第2步。我当前每10分钟运行这个调度。
这个进程主要是为了帮助处理事务复制的间歇性停工。使用复制监视器定期监视复制进程仍然重要。这个进程只是为了阻止下班时间的电话骚扰，只需要启动下分发代理作业就可以修复。
本文转自UltraSQL51CTO博客，原文链接： http://blog.51cto.com/ultrasql/1907283，如需转载请自行联系原作者
