How To Monitor Replication in SQL Server

 

This post takes you through the T-SQL script How to Monitor Replication in SQL Server. Usually we need to monitor replication periodically through out the day. I have designed a solution to monitor replication form T-SQL. It’ll send a mail to DBA team that replication is failing. Create the below two objects (Table and PROC) and create a job which calls the stored procedure for every 5 minutes.
This will save us a lot of time and effort for sure

Now here is the script to automate replication monitoring in sql server

Create a Table and a stored procedure. Please have a look at procedure code, all sections are provided with required comments.

Table Creation

/***** Create RepliMonitor Table *****/
CREATE TABLE dbo.repmonitor (
	[ID] INT NOT NULL IDENTITY,
	[status] int null,
	warning int null ,
	subscriber sysname null ,
	subscriber_db sysname null ,
	publisher_db sysname null ,
	publication sysname null ,
	publication_type int null ,
	subtype int null ,
	latency int null ,
	latencythreshold int null ,
	agentnotrunning int null ,
	agentnotrunningthreshold int null ,
	timetoexpiration int null ,
	expirationthreshold int null ,
	last_distsync datetime null ,
	distribution_agentname sysname null ,
	mergeagentname sysname null ,
	mergesubscriptionfriendlyname sysname null ,
	mergeagentlocation sysname null ,
	mergeconnectiontype int null ,
	mergePerformance int null ,
	mergerunspeed float null ,
	mergerunduration int null ,
	monitorranking int null ,
	distributionagentjobid binary(30) null ,
	mergeagentjobid binary(30) null ,
	distributionagentid int null ,
	distributionagentprofileid int null ,
	mergeagentid int null ,
	mergeagentprofileid int null ,
	logreaderagentname sysname null
);

Stored Procedure Creation:

/*** Create the SPOC on Monitor Database ***/

/*** It requires the Publisher Name as parameter ***/
/*** Execute the Stored Procedure 
	 EXEC usp_replmonitor_Create @Publisher_Name = 'Repli_Publisher_01'
					@DBProfile = 'ProdMailPro',
					@Email = 'dbateam@company.com';	
***/
CREATE PROC usp_replmonitor_Create(	@Publisher_Name VARCHAR(200), 
					@DBProfile	VARCHAR(100) = NULL,
					@Email		VARCHAR(1000) )
AS
BEGIN
SET NOCOUNT ON

DECLARE @Pub_Type TINYINT;
DECLARE @C INT;
DECLARE @ReplM TABLE
		(	[ID]	INT NOT NULL IDENTITY,
			subscriber sysname null,
			publication sysname null,
			last_distsync datetime null
		);

IF (@DBProfile IS NULL)
BEGIN
	SELECT @DBProfile = Name FROM msdb.dbo.sysmail_profile WHERE Profile_ID = 1;
END

/*** Clears the previous data ***/

TRUNCATE TABLE repmonitor;

/*** For Transactional Replication ***/

SET @Pub_Type=0

INSERT INTO repmonitor
EXEC [DSADistribution].[DBO].[sp_replmonitorhelpsubscription]
				@publisher = @Publisher_Name,
				@publication_type = @Pub_Type

 
/*** For Snapshot Replication ***/

SET @Pub_Type=1

INSERT INTO repmonitor
EXEC [DSADistribution].[DBO].[sp_replmonitorhelpsubscription]
				@publisher = @Publisher_Name,
				@publication_type = @Pub_Type

 
/*** For Merge Replication ***/

SET @Pub_Type = 2

INSERT INTO repmonitor
EXEC [DSADistribution].[DBO].[sp_replmonitorhelpsubscription]
				@publisher = @Publisher_Name,
				@publication_type = @Pub_Type

/*** Check if any subscription is failing ***/

SET @C=0

SELECT @C=COUNT(1)FROM repmonitor WHERE Status = 6;

IF(@C>0)
BEGIN
	DECLARE @String varchar(8000);
	SET @String='Replication is Failing @ '+@Publisher_Name+' for below Subscribers'+CHAR(13)+CHAR(13);
	SET @String=@String+'Subscriber'+''+'Publication'+char(13);
	
	SELECT	@String = @String + subscriber +' '+publication+char(13)
	FROM 	repmonitor 
	WHERE	Status=6;

/*** Notify DBA Group ***/

EXEC msdb.dbo.sp_send_dbmail
		@profile_name = @DBProfile,
		@recipients =@Email,
		@body = @String,
		@subject ='Replication is Failing';
END

END

Here is the script file: monitor_replication

We can schedule a job to execute this stored procedure.