1

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

drizzt
  • 84
  • 3
  • 10

1 Answers1

1

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:

  • A given publisher may not have had any tokens posted in the time frame that you're looking for. That should be flagged
  • As a measure of latency, you want the publisher_commit time for the most recent token to commit to the subscriber

Good luck!

Ben Thul
  • 2,969
  • 16
  • 23