Script to Automate The Replication Monitoring
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 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
)
--EXEC usp_replmonitor_Create
/*** Create the SPOC on Monitor Database ***/
/*** It requires the Publisher Name as parameter ***/
CREATE PROC usp_replmonitor_Create (@Publisher_Name VARCHAR(200))
AS
BEGIN
SET NOCOUNT ON
DECLARE @Pub_Type TINYINT
DECLARE @C INT
DECLARE @DBProfile VARCHAR(100)
DECLARE @ReplM TABLE
([ID] INT NOT NULL IDENTITY,
subscriber sysname null ,
publication sysname null ,
last_distsync datetime null
)
SELECT @DBProfile=Name FROM msdb.dbo.sysmail_profile WHERE Profile_ID=1
/*** 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 = 'DBAGroup@SQLServer.com',
@body = @String,
@subject = 'Replication is Failing';
END
END
