我与许多发布者(50 多个)和一个中央订阅者(数据收集)进行事务复制。是否有某种方法(SQL 服务器功能、第三方工具)可以从一个点(理想情况下是从中央订阅者)监控所有发布?(类似于使用复制监视器监控具有多个发布的服务器)
谢谢
答案1
是的。我的做法是让 SQL Agent 作业定期向发布者发送跟踪令牌,然后从分发者(存储其历史记录的地方)监控其进度。我在分发数据库中创建了一个有助于此的视图。
USE [distribution]
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[tokens]'))
DROP VIEW [dbo].[tokens]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[tokens] as
select
ps.name as [publisher],
p.publisher_db,
p.publication,
ss.name as [subscriber],
da.subscriber_db,
t.publisher_commit,
t.distributor_commit,
h.subscriber_commit,
datediff(second, t.publisher_commit, t.distributor_commit) as [pub to dist (s)],
datediff(second, t.distributor_commit ,h.subscriber_commit) as [dist to sub (s)],
datediff(second, t.publisher_commit, h.subscriber_commit) as [total latency (s)]
from mstracer_tokens t
inner join MStracer_history h
on t.tracer_id = h.parent_tracer_id
inner join mspublications p
on p.publication_id = t.publication_id
inner join sys.servers ps
on p.publisher_id = ps.server_id
inner join msdistribution_agents da
on h.agent_id = da.id
inner join sys.servers ss
on da.subscriber_id = ss.server_id
我把它留给读者作为练习,将其转化为监控。我会给你我所拥有的,但最近意识到它有一个错误。但要记住以下几点:
- 特定发布者可能没有在您要查找的时间范围内发布任何代币。这应该被标记
- 作为延迟的衡量标准,您希望发布者提交最新令牌的时间是将其提交给订阅者
祝你好运!