Replication Latency

I've used Replication a lot over the years and contrary to the opinions of most DBA’s I know, I think it is a brilliant feature.  Replication I feel gets a bad reputation for causing a number of problems for DBA’s, in most cases it is something they inherit when they change roles.  The fact of the matter as with anything, when implemented correctly it works fantastically well.  Whatever your implementation of Replication is, be it for High Availability or Disaster Recovery one of the biggest challenges is monitoring it.  I've used and continue to use Replication Monitor, system tables and system procedures in various implementations to keep an eye on a number of areas of Replication. 

The most recent addition to my Replication monitoring arsenal is a script I wrote using system procedures to monitor replication latency.  It inserts a tracer token waits while this is delivered then gets the latency information of said token and writes the results to a table.  This results table can then be queried to produce reports and or alerts.

The script for the table is below;

/*

      -----------------------------------------------------------------

      Create ReplicationLatency table

      -----------------------------------------------------------------

   

      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

    

      -----------------------------------------------------------------

*/

-- Set database Context

USE master;

GO

-- Create ReplicationLatency table

IF NOT EXISTS ( SELECT  1

                FROM    sys.objects

                WHERE   [object_id] = OBJECT_ID('dbo.ReplicationLatency')

                        AND [type] = 'U' )

CREATE TABLE dbo.ReplicationLatency

(

      ReplicationLatencyID INT IDENTITY(1,1) CONSTRAINT [PK_ReplicationLatency:ReplicationLatencyID] PRIMARY KEY CLUSTERED,

      DistributorLatency INT NULL,

      SubscriberServerName VARCHAR(255) NOT NULL,

      SubscriberDatabaseName VARCHAR(255)NOT NULL,

      SubscriberLatency INT NULL,

      OverallLatency INT NULL,

      PublicationName [varchar](255) NOT NULL CONSTRAINT [DF_ReplicationLatency:PublicationName] DEFAULT ('x'),

      RunDateTime DATETIME CONSTRAINT [DF_ReplicationLatency:RunDateTime] DEFAULT (GETDATE()),

      IsArchived BIT CONSTRAINT [DF_ReplicationLatency:IsArchived] DEFAULT (0)

);

GO

The IsArchived BIT column is used solely for an archive strategy I have to move “old” data out of production databases on expensive fast disk arrays to archive databases on less expensive SATA arrays.  Yes I have created the table in master, it would be a good idea to move it to another database which you see fit.

The script below does all the work.  I simply use this in a SQL Agent Job to execute every 10 minutes to record the results.  I have tested this on SQL Server 2008 R2 only.

/*

      -----------------------------------------------------------------

      Replication latency Monitor

      -----------------------------------------------------------------

   

      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

    

      -----------------------------------------------------------------

*/

-- Set database context

USE distribution;

GO

-- Declare variables

DECLARE @publication AS SYSNAME

DECLARE @publisher_db AS SYSNAME

DECLARE @tokenID AS INT

DECLARE @MinPubID INT

DECLARE @MaxPubID INT

DECLARE @SQL VARCHAR(4000)

DECLARE @Delay VARCHAR(24)

-- Build @Delay string.  This is to give the tracer token time to reach the subscriber and may need to be updated depending on your environment

SET @Delay = 'WAITFOR DELAY ''00:00:05''';

-- Drop temporary table if exists

IF OBJECT_ID('tempDB.dbo.#Publications') IS NOT NULL

    DROP TABLE #Publications ;

IF OBJECT_ID('tempDB.dbo.#tokens') IS NOT NULL

    DROP TABLE #tokens ;

-- Create temporary tables

CREATE TABLE #Publications

    (

      ID INT IDENTITY(1, 1),

      publisher_db SYSNAME,

      publication SYSNAME

    );

CREATE TABLE #tokens

      (

            tracer_id INT,

            publisher_commit DATETIME

      );

-- Insert publications

INSERT INTO #Publications ( publisher_db, publication )

SELECT publisher_db, publication

FROM distribution.dbo.MSpublications

WHERE publication_type = 0; -- Transactional publications  

-- Setup loop Control

SELECT @MinPubID = MIN(ID) FROM #Publications;

SELECT @MaxPubID = MAX(ID) FROM #Publications;

WHILE @MinPubID <= @MaxPubID

BEGIN

     

      -- Set Publication

      SELECT @publication = Publication FROM #Publications WHERE ID = @MinPubID;

      SELECT @publisher_db = publisher_db FROM #Publications WHERE ID = @MinPubID;

     

      -- Truncate #tokens ready for next publication

      TRUNCATE TABLE #tokens;

     

      -- Build @SQL string

      SET @SQL = 'EXEC ' + @publisher_db + '.sys.sp_posttracertoken

        @publication = ' + @publication

     

      -- Insert a new tracer token in the publication database 

      EXEC ( @SQL ); 

      -- Wait 10 seconds for the token to make it to the Subscriber

      EXEC ( @Delay );

      -- Build @SQL string

      SET @SQL = 'INSERT #tokens (tracer_id, publisher_commit)

      EXEC ' + @publisher_db + '.sys.sp_helptracertokens @publication = ' + @publication

     

      -- Return tracer token information to a temp table

      EXEC ( @SQL );

     

      -- Get tracer token ID

      SET @tokenID = (SELECT TOP 1 tracer_id FROM #tokens

      ORDER BY publisher_commit DESC);

      -- Build @SQL string

      SET @SQL = ' INSERT master.dbo.ReplicationLatency (DistributorLatency, SubscriberServerName, SubscriberDatabaseName, SubscriberLatency, OverallLatency)

      EXEC ' + @publisher_db + '.sys.sp_helptracertokenhistory

        @publication = ' + @publication + ',

        @tracer_id = ' + CONVERT(varchar,@tokenID)

       

      -- Get history for the tracer token

      EXEC ( @SQL )

      -- Update the PublicationName for most recently inserted record

      UPDATE SSIS.dbo.ReplicationLatency

      SET PublicationName = @publication

      WHERE PublicationName = 'x';     
 

      -- Incriment loop control

      SELECT @MinPubID = @MinPubID + 1

END

Enjoy!

Chris