Gathering Tracer Token Data
Published: Jan 08, 2016
After my last post surrounding Tracer Tokens I was told by a friend that it was a little harsh to simply end saying that you can just join to a few system tables and make something meaningful. Therefore I’ve given in and this post will show you how you can use tracer token and system data to your advantage.

So, what I’m going to do is to show you a nice way to post your tracer tokens via a scheduled job, posting to all publications, and then collect and store this data in a table.

So where do we start?

Well, I’m going to make a proc which will scan through all the databases in your server, find any publications, and then post a tracer token into each one.

The idea being that we will then place this proc in a scheduled job (which I won’t walk you through as I’m sure you can work that bit out) and that’s it… we’ll have tracer tokens running through all of our publications ready for us to monitor.

What we will need to do is obtain a list of all databases, then loop through them looking for publications (with error handling for non-replicated databases, of course).

Then we loop through those databases and associated publications placing our tracer tokens. It’s that simple:

create procedure [dbo].[replicationPostTracerToken]
as
   set nocount on

   declare
@counter smallint, @sql nvarchar(max)
  
set @counter = 1

  
-- Obtain list of databases
  
declare @database table
  
(
      
id smallint identity(1, 1),
      
databaseName varchar(50)
   )
  
insert into @database
  
select name
  
from sys.databases

  
-- create table ready to hold publication list and relevant database
  
declare @publication table
  
(
      
id int identity(1, 1),
      
dbName varchar(100),
      
publication varchar(250)
   )

  
-- loop through each database looking for publications
  
while @counter <= (select max(id) from @database)
  
begin
       select
@sql = 'select ''' + databaseName + ''', name from ' + databaseName + '.dbo.syspublications where sync_method != 0'
      
from @database
      
where id = @counter

      
-- try catch because not all databases are replicated
      
begin try
          
insert into @publication
          
exec sp_executeSQL @sql
      
end try
      
-- null catch
      
begin catch
      
end catch

      
select @counter += 1
  
end

   select
@counter = 1

  
-- having got all publications and databases, loop through placing tracer tokens
  
while @counter <= (select max(id) from @publication)
  
begin
       select
@sql = 'exec ' + dbName + '.sys.sp_postTracerToken @publication = ''' + publication + ''''
      
from @publication
      
where id = @counter

      
exec sp_executeSQL @sql

      
set @counter = @counter + 1
  
end

   set nocount off
go


Okay, having done this we now need something to collect these tokens and do something useful with them.

Therefore we’ll create a main data table and a variable holding table, collate the results, and store them for analysis:

create procedure [dbo].[replicationCollectTracerToken]
as
  
-- create table for data if it doesn't already exist
  
if object_id('tracerTokens') is null
  
begin
       create table
dbo.tracerTokens
      
(
          
publicationID int,
          
subscriberID int,
          
publication varchar(250),
          
subscriberName varchar(100),
          
agentName varchar(1000),
          
publisherCommit datetime,
          
distributorCommit datetime,
          
subscriberCommit datetime,
          
distributorSecs int,
          
subscriberSecs int,
          
constraint pk_tracerTokens primary key clustered(publicationID, subscriberID, publisherCommit)
       )
  
end

   set nocount on
  
-- create variable table
  
declare @results table
  
(
      
publicationID int,
      
subscriberID int,
      
publication varchar(250),
      
subscriberName varchar(100),
      
agentName varchar(1000),
      
publisherCommit datetime,
      
distributorCommit datetime,
      
subscriberCommit datetime,
      
distributorSecs int,
      
subscriberSecs int
  
)

  
-- insert data into variable table.  Include system table info to make tokens meaningful
  
insert into @results
  
select t.publication_id, a.subscriber_id, p.publication, s.name subscriberName, a.name agentName,
          
t.publisher_commit, t.distributor_commit, h.subscriber_commit,
          
datediff(s,t.publisher_commit,t.distributor_commit),
          
datediff(s,t.distributor_commit,h.subscriber_commit)
  
from distribution.dbo.mstracer_tokens t with (nolock)
  
join distribution.dbo.mstracer_history h with (nolock)
  
on t.tracer_id = h.parent_tracer_id
  
join distribution.dbo.msPublications p with (nolock)
  
on t.publication_id = p.publication_id
  
join distribution.dbo.msDistribution_agents a with (nolock)
  
on h.agent_id = a.id
  
join master.sys.servers s with (nolock)
  
on a.subscriber_id = s.server_id

  
-- insert into main data table, avoiding duplicates
  
insert into tracerTokens
  
select r.*
  
from @results r
  
join
  
(
      
select publicationID, subscriberID, publisherCommit
      
from @results
      
except
       select
publicationID, subscriberID, publisherCommit
      
from tracerTokens
  
) x
  
on r.publicationID = x.publicationID
  
and r.subscriberID = x.subscriberID
  
and r.publisherCommit = x.publisherCommit

  
-- this is a cleanup to ensure no missed data
   -- as data could have been added in a previous
   -- run which hadn't gone end to end at the time but now has
  
update t
  
set t.distributorCommit = r.distributorCommit,
      
t.subscriberCommit = r.subscriberCommit,
      
t.distributorSecs = r.distributorSecs,
      
t.subscriberSecs = r.subscriberSecs
  
from tracerTokens t
  
left join @results r
  
on t.publicationID = r.publicationID
  
and t.subscriberID = r.subscriberID
  
and t.publisherCommit = r.publisherCommit
  
where t.distributorCommit is null
   or
t.subscriberCommit is null
go


It’s as simple as that. Now you have a table of token data which you can easily use for reporting and alerting with regard to latencies in your replicated environment.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron