I have transactional replication with many publishers(50+) and one central subscriber(data collecting). Is there some way(feature of SQL server, 3th party tool) how to monitor all publications from one point ideally from central subscriber?(something like monitoring server with multiple publications with Replication monitor)
Thx
Yes there is. The way I do it is I have a SQL Agent job post tracer tokens at a regular interval to the publishers and then monitor their progress from the distributor (where their history is stored). I created a view in the distribution database that helps with that.
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
I leave it as an exercise to the reader to turn that into monitoring. I'd give you what I have, but recently realized that it has a bug in it. But things to keep in mind:
Good luck!
