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] PRIMARYKEY CLUSTERED,
DistributorLatency INT NULL,
SubscriberServerName VARCHAR(255) NOT NULL,
SubscriberDatabaseName VARCHAR(255)NOT NULL,
SubscriberLatency INT NULL,
OverallLatency INTNULL,
PublicationName [varchar](255) NOT NULL CONSTRAINT [DF_ReplicationLatency:PublicationName] DEFAULT ('x'),
RunDateTime DATETIMECONSTRAINT [DF_ReplicationLatency:RunDateTime] DEFAULT (GETDATE()),
IsArchived BITCONSTRAINT [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
USEdistribution;
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 )
SELECTpublisher_db, publication
FROMdistribution.dbo.MSpublications
WHEREpublication_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 BYpublisher_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
