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.