Friday 25 January 2013

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