Problem
In my environment we have a few servers where we have SQL Server replication setup and we need to make sure replication is always in sync, because business users connect to these servers for current up to date data. In this tip I will show a process we put in place to notify us of any issues with SQL Server replication.
Solution
We can automate SQL Server replication monitoring in this step by step process that will send email notifications. This will be done using a centralized server to collect the data and send out the notification email.
SQL Server Replication Notification via Email
Step 1 – Create Linked Servers
Create a linked server to each distributor that is part of replication. These should be created on your centralized monitoring server. Also, test each linked server to make sure you can successfully connect.
Step 2 – Create Table to Store the List of Servers
Create a table to store details of each distributor server which is used for replication.
CREATE TABLE [dbo].[Distributor_Servers]([S[SN] [int] IDENTITY(1,1) NOT NULL, [DISTRIBUTOR] [sysname] NULL) ON [PRIMARY] GO
Step 3 – Insert Records into Table
Insert records into this table of all distributors as shown below:
Step 4 – Create Stored Procedure on each Replication Distributor
Create the below stored procedure on each distributor to pull the replication information. I recommend creating this in a database specifically for database administration tasks. I have a database called DBA on each of my instances where I put objects like this.
CREATE PROCEDURE [dbo].[usp_GetReplicationMonitorData]--@Mailtext VARCHAR(5000) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @Sqltext NVARCHAR(MAX)
DECLARE @Publisher SYSNAME, @PublisherDB SYSNAME, @Publication SYSNAME, @Distdb SYSNAME,
@Subscriber SYSNAME, @SubscriberDB SYSNAME
IF OBJECT_ID('tempdb..#REPLMONITORPUBLISHER') IS NOT NULL
DROP TABLE #REPLMONITORPUBLISHER
CREATE TABLE #REPLMONITORPUBLISHER
(
SN INT IDENTITY(1,1)
,PUBLISHER SYSNAME NULL
,DISTRIBUTION_DB SYSNAME NULL
,STATUS INT NULL
,WARNING INT NULL
,PUBLICATIONCOUNT INT NULL
,RETURNSTAMP VARCHAR(100) NULL
)
IF OBJECT_ID('tempdb..#REPLMONITORSUBSCRIPTION') IS NOT NULL
DROP TABLE #REPLMONITORSUBSCRIPTION
CREATE TABLE #REPLMONITORSUBSCRIPTION
(
SN INT IDENTITY(1,1)
,PUBLISHER SYSNAME NULL
,DISTRIBUTION_DB SYSNAME NULL
,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 FLOAT NULL
,AGENTNOTRUNNING INT NULL
,AGENTNOTRUNNINGTHRESHOLD INT NULL
,TIMETOEXPIRATION INT NULL
,EXPIRATIONTHRESHOLD INT NULL
,LAST_DISTSYNC DATETIME NULL
,DISTRIBUTION_AGENTNAME SYSNAME NULL
,MONITORRANKING INT NULL
,DISTRIBUTIONAGENTJOBID BINARY(16) NULL
,DISTRIBUTIONAGENTID INT NULL
,DISTRIBUTIONAGENTPROFILEID INT NULL
,LOGREADERAGENTNAME SYSNAME NULL
)
IF OBJECT_ID('tempdb..#REPLLOGREADERAGENT') IS NOT NULL
DROP TABLE #REPLLOGREADERAGENT
CREATE TABLE #REPLLOGREADERAGENT
(
SN INT IDENTITY(1,1)
,DBNAME SYSNAME NULL
,NAME SYSNAME NULL
,STATUS INT NULL
,PUBLISHER SYSNAME NULL
,PUBLISHER_DB SYSNAME NULL
,START_TIME DATETIME NULL
,TIME DATETIME NULL
,DURATION INT NULL
,COMMENTS NVARCHAR(1000)
,DELIVERY_TIME INT NULL
,DELIVERED_TRANSACTIONS INT NULL
,DELIVERED_COMMANDS INT NULL
,AVERAGE_COMMANDS INT NULL
,DELIVERY_RATE INT NULL
,DELIVERY_LATENCY INT NULL
,ERROR_ID INT NULL
,JOB_ID BINARY(16) NULL
,LOCAL_JOB INT NULL
,PROFILE_ID INT NULL
,AGENT_ID INT NULL
,LOCAL_TIMESTAMP BINARY(8) NULL
)
IF OBJECT_ID('tempdb..#REPLDISTRIBUTORAGENT') IS NOT NULL
DROP TABLE #REPLDISTRIBUTORAGENT
CREATE TABLE #REPLDISTRIBUTORAGENT
(
SN INT IDENTITY(1,1)
,DBNAME SYSNAME NULL
,NAME SYSNAME NULL
,STATUS INT NULL
,PUBLISHER SYSNAME NULL
,PUBLISHER_DB SYSNAME NULL
,PUBLICATION SYSNAME NULL
,SUBSCRIBER SYSNAME NULL
,SUBSCRIBER_DB SYSNAME NULL
,SUBSCRIPTION_TYPE INT NULL
,START_TIME DATETIME NULL
,TIME DATETIME NULL
,DURATION INT NULL
,COMMENTS NVARCHAR(1000)
,DELIVERY_TIME INT NULL
,DELIVERED_TRANSACTIONS INT NULL
,DELIVERED_COMMANDS INT NULL
,AVERAGE_COMMANDS INT NULL
,DELIVERY_RATE INT NULL
,DELIVERY_LATENCY INT NULL
,ERROR_ID INT NULL
,JOB_ID BINARY(16) NULL
,LOCAL_JOB INT NULL
,PROFILE_ID INT NULL
,AGENT_ID INT NULL
,LOCAL_TIMESTAMP BINARY(8) NULL
,OFFLOAD_ENABLED BIT NULL
,OFFLOAD_SERVER VARCHAR(50)
,SUBSCRIBER_TYPE INT NULL
)
INSERT INTO #REPLMONITORPUBLISHER
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;',
'set fmtonly off exec sp_replmonitorhelppublisher')
DECLARE CURSORPUBLISHER CURSOR FOR
SELECT PUBLISHER, DISTRIBUTION_DB
FROM #REPLMONITORPUBLISHER;
OPEN CURSORPUBLISHER
FETCH NEXT FROM CURSORPUBLISHER
INTO @Publisher, @Distdb
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sqltext = 'SELECT ''' + @Publisher + ''', ''' + @Distdb + ''', STATUS, WARNING,
SUBSCRIBER, SUBSCRIBER_DB,PUBLISHER_DB, PUBLICATION , PUBLICATION_TYPE, SUBTYPE,
LATENCY, LATENCYTHRESHOLD, AGENTNOTRUNNING, AGENTNOTRUNNINGTHRESHOLD,
TIMETOEXPIRATION, EXPIRATIONTHRESHOLD, LAST_DISTSYNC, DISTRIBUTION_AGENTNAME,
MONITORRANKING, DISTRIBUTIONAGENTJOBID, DISTRIBUTIONAGENTID,
DISTRIBUTIONAGENTPROFILEID, LOGREADERAGENTNAME FROM
OPENROWSET (''SQLOLEDB'',''Server=(local);TRUSTED_CONNECTION=YES;'',
''set fmtonly off EXEC ' + @Distdb + '..sp_replmonitorhelpsubscription @publisher = '''''
+ @Publisher +''''', @publication_type = 0 '') '
INSERT INTO #REPLMONITORSUBSCRIPTION (PUBLISHER, DISTRIBUTION_DB, STATUS, WARNING,
SUBSCRIBER, SUBSCRIBER_DB, PUBLISHER_DB, PUBLICATION ,PUBLICATION_TYPE, SUBTYPE,
LATENCY, LATENCYTHRESHOLD, AGENTNOTRUNNING, AGENTNOTRUNNINGTHRESHOLD,
TIMETOEXPIRATION, EXPIRATIONTHRESHOLD, LAST_DISTSYNC, DISTRIBUTION_AGENTNAME,
MONITORRANKING, DISTRIBUTIONAGENTJOBID, DISTRIBUTIONAGENTID, DISTRIBUTIONAGENTPROFILEID,
LOGREADERAGENTNAME)
EXEC (@Sqltext)
FETCH NEXT FROM CURSORPUBLISHER
INTO @Publisher, @Distdb
END
CLOSE CURSORPUBLISHER;
DEALLOCATE CURSORPUBLISHER;
INSERT INTO #REPLLOGREADERAGENT
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;',
'set fmtonly off EXEC sp_MSenum_replication_agents @type = 2')
INSERT INTO #REPLDISTRIBUTORAGENT
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;',
'set fmtonly off EXEC sp_MSenum_replication_agents @type = 3')
-- Default latency threshold is 30 sec.
--It is common to have 1-2 minute Publisher to Subscriber latency in replication.
-- With default threshold, we are getting frequent latency alerts.
--Hence changing latency threshold to 120 sec for monitoring solution.
UPDATE #REPLMONITORSUBSCRIPTION SET LATENCYTHRESHOLD = 120
SELECT S.PUBLISHER, S.PUBLISHER_DB, S.PUBLICATION, S.DISTRIBUTION_DB, S.SUBSCRIBER,
S.SUBSCRIBER_DB, S.LATENCY,
S.LAST_DISTSYNC,
CASE WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 34 THEN 'Excellent'
WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 59 THEN 'Good'
WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 84
THEN 'Fair' WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 99
THEN 'Poor' ELSE 'Critical' END AS PERFORMANCE,
CASE S.WARNING WHEN 0 THEN 'NA' WHEN 1 THEN 'Expiration'
WHEN 2 THEN 'Latency' ELSE 'Unknown' END AS WARNING,
CASE L.STATUS WHEN 1 THEN 'Started' WHEN 2 THEN 'Stopped'
WHEN 3 THEN 'In Progress' WHEN 4 THEN 'Idle' WHEN 5
THEN 'Retrying' WHEN 6 THEN 'Failed' ELSE 'Unknown' END AS LOGREADERAGENTSTATUS,
CASE D.STATUS WHEN 1 THEN 'Started' WHEN 2 THEN 'Stopped' WHEN 3 THEN 'In Progress'
WHEN 4 THEN 'Idle' WHEN 5
THEN 'Retrying' WHEN 6 THEN 'Failed' ELSE 'Unknown' END AS DISTRIBUTIONAGENTSTATUS
FROM #REPLMONITORSUBSCRIPTION S
LEFT OUTER JOIN #REPLLOGREADERAGENT L ON S.LOGREADERAGENTNAME = L.NAME
AND S.DISTRIBUTION_DB = L.DBNAME
AND S.PUBLISHER = L.PUBLISHER AND S.PUBLISHER_DB = L.PUBLISHER_DB
LEFT OUTER JOIN #REPLDISTRIBUTORAGENT D ON S.DISTRIBUTION_AGENTNAME = D.NAME
AND S.DISTRIBUTION_DB = L.DBNAME AND S.PUBLISHER = L.PUBLISHER
AND S.PUBLISHER_DB = L.PUBLISHER_DB
AND S.PUBLICATION = D.PUBLICATION AND S.SUBSCRIBER = D.SUBSCRIBER
AND S.SUBSCRIBER_DB = D.SUBSCRIBER_DB
DROP TABLE #REPLMONITORPUBLISHER
DROP TABLE #REPLMONITORSUBSCRIPTION
DROP TABLE #REPLLOGREADERAGENT
DROP TABLE #REPLDISTRIBUTORAGENT
END
GOStep 5 – Create Stored Procedure on Centralized Collection Server
Create this replication monitoring stored procedure on the centralized monitoring server to collect replication status information, consolidate the results and send email notification. I recommend creating this in a database specifically for database administration tasks. I have a database called DBA on each of my instances where I put objects like this.
CREATE PROCEDURE [dbo].[usp_GetReplicationStatus]@mode BIT = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @Recipients VARCHAR(275)
DECLARE @MailSubject VARCHAR(275)
DECLARE @Xml NVARCHAR(MAX)
DECLARE @Mailtext NVARCHAR(MAX)
DECLARE @Server VARCHAR(25)
DECLARE @Curdate DATETIME
DECLARE @MailString NVARCHAR(MAX)
DECLARE @Note NVARCHAR(1000)
DECLARE @Sql VARCHAR (200)
DECLARE @Distributor SYSNAME
SET @Recipients = 'Atul.Gaikwad@SQLDBAExperts.com'
IF OBJECT_ID('tempdb..#REPLICATIONMONITOR') IS NOT NULL
DROP TABLE #REPLICATIONMONITOR
CREATE TABLE #REPLICATIONMONITOR
(
SN INT IDENTITY(1,1)
,PUBLISHER SYSNAME NULL
,PUBLISHER_DB SYSNAME NULL
,PUBLICATION SYSNAME NULL
,DISTRIBUTION_DB SYSNAME NULL
,SUBSCRIBER SYSNAME NULL
,SUBSCRIBER_DB SYSNAME NULL
,LATENCY INT NULL
,LAST_DISTSYNC DATETIME NULL
,PERFORMANCE VARCHAR(20) NULL
,WARNING VARCHAR(20) NULL
,LOGREADERAGENTSTATUS VARCHAR(50) NULL
,DISTRIBUTIONAGENTSTATUS VARCHAR(50) NULL
)
DECLARE DistServer CURSOR FOR
SELECT DISTRIBUTOR FROM Distributor_Servers;
OPEN DistServer
FETCH NEXT FROM DistServer
INTO @Distributor
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = @Distributor + '.DBA.dbo.usp_GetReplicationMonitorData'
INSERT INTO #REPLICATIONMONITOR
EXEC (@Sql)
FETCH NEXT FROM DistServer
INTO @Distributor
END
CLOSE DistServer;
DEALLOCATE DistServer;
SET @Mailtext ='<html>
<style type="text/css">
table.gridtable {
font-family: verdana,arial,sans-serif;
font-size:11px;
color:#000000;
border-width: 1px;
border-color: #666666;
border-collapse: collapse;
}
table.gridtable th {
border-width: 1px;
font-size:10px;
border-style: solid;
border-color: #666666;
}
table.gridtable td {
border-width: 1px;
font-size:10px;
border-style: solid;
border-color: #666666;
}
</style>
<body>
<table class="gridtable">
<tr bgcolor = ''''#808080''''>
<th>Publisher</th> <th>Publisher Database</th> <th>Publication</th>
<th>Distribution Database</th>
<th>Subscriber</th><th>Subscriber Database</th><th>Latency</th>
<th>Performance</th><th>Warning</th><th>LogReader Agent Status</th>
<th>Distributor Agent Status </th><th>Last Distribution Sync</th>
</tr>'
SET @Note = '<b>Note:</b><br/>
1. LogReader Agent and Distributor Agent status should always be either Idle or In Progress.
If agent status has different value then verify it manually and rectify the issue.<br/>
2. Performance values are based on latency ratio (Latency/LatencyThreshold):
Excellent (0-34%), Good (35-59%), Fair (60-84%), Poor (85-99%), Critical (100%+).
We have used 120 sec as latency threshold.<br/>
3. Warning values: - i) Expiration - subscription not synchronized within retention period
threshold
ii) Latency - Publisher to Subscriber latency has exceeded threshold iii) NA - No warnings
<br/></body></html>'
IF @mode = 0
BEGIN
SET @Xml = CAST(( SELECT 'tr/@bgcolor'= CASE PERFORMANCE WHEN 'Excellent'
THEN '#90EE90' WHEN 'Good' THEN '#90EE90' WHEN 'Fair' THEN '#FFA500'
ELSE '#FF4500' END,
td = PUBLISHER, '', td = PUBLISHER_DB , '', td = PUBLICATION, '',
td = DISTRIBUTION_DB, '', td = SUBSCRIBER, '',
td = SUBSCRIBER_DB, '', td = LATENCY, '', td = PERFORMANCE, '',
td = WARNING, '', td = LOGREADERAGENTSTATUS, '',
td = DISTRIBUTIONAGENTSTATUS, '', td = LAST_DISTSYNC, ''
FROM #REPLICATIONMONITOR ORDER BY PUBLISHER, LATENCY DESC
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @Mailtext = @Mailtext + @Xml +'</table><br/>' + @Note
SET @MailSubject = 'Replication Status Snapshot'
SET @MailString = 'SQLDBAExperts.msdb.dbo.sp_send_dbmail @profile_name =
''SQLDBAExpertsmail'', @recipients = ''' + @Recipients + ''',
@subject = ''' + @MailSubject + ''', @body = ''' + @Mailtext + ''',
@body_format = ''HTML'' '
EXEC (@MailString)
END
ELSE
BEGIN
IF EXISTS(SELECT 1 FROM #REPLICATIONMONITOR WHERE LOGREADERAGENTSTATUS NOT IN
('In Progress', 'Idle')
OR DISTRIBUTIONAGENTSTATUS NOT IN ('In Progress', 'Idle') OR WARNING = 'Expiration'
OR PERFORMANCE IN ('Poor', 'Critical'))
BEGIN
SET @Xml = CAST(( SELECT 'tr/@bgcolor' = CASE 1 WHEN 1 THEN '#FF4500' ELSE '#FF4500' END,
td = PUBLISHER, '', td = PUBLISHER_DB , '', td = PUBLICATION, '', td = DISTRIBUTION_DB, '',
td = SUBSCRIBER, '',
td = SUBSCRIBER_DB, '', td = LATENCY, '', td = PERFORMANCE, '', td = WARNING, '',
td = LOGREADERAGENTSTATUS, '',
td = DISTRIBUTIONAGENTSTATUS, '', td = LAST_DISTSYNC, ''
FROM #REPLICATIONMONITOR WHERE LOGREADERAGENTSTATUS NOT IN ('In Progress', 'Idle')
OR DISTRIBUTIONAGENTSTATUS NOT IN ('In Progress', 'Idle') OR WARNING = 'Expiration'
OR PERFORMANCE IN ('Poor', 'Critical')
ORDER BY PUBLISHER, LATENCY DESC
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @Mailtext = @Mailtext + @Xml +'</table><br/>' + @Note
SET @MailSubject = 'Replication Error - Please check Status for Publications and
Subscriptions listed in email'
SET @MailString = 'SQLDBAExperts.msdb.dbo.sp_send_dbmail @profile_name =
''SQLDBAExpertsmail'', @recipients = ''' + @Recipients + ''',
@subject = ''' + @MailSubject + ''', @body = ''' + @Mailtext + ''',
@body_format = ''HTML'' '
EXEC (@MailString)
END
END
DROP TABLE #REPLICATIONMONITOR
ENEND
Step 6 – Create SQL Agent Job on Centralized Server to Collect Data
Create a replication monitoring SQL Server Agent job on the centralized server to send replication status notifications at a particular interval.
This can be done using this script to create the job:
USE [msdb]GO /****** Object: Job [Monitor Replication - Shiftwise] Script Date: 04/22/2016 03:53:50 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [2 DBA On-Demand] Script Date: 04/22/2016 03:53:50 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'2 DBA On-Demand' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'2 DBA On-Demand' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name= N'Monitor Replication - Shiftwise', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'This job will send Replication status report to DBA team for all the Publishers/Subscribers configured.', @category_name=N'2 - DBA - On-Demand', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Replication status] Script Date: 04/22/2016 03:53:50 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Replication status', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'exec usp_GetReplicationStatus 0', @database_name=N'DBA', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Replication Status Schedule', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=8, @freq_subday_interval=8, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20120920, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959, @schedule_uid=N'a4b8a45f-5b47-40f1-86e6-3a55dc395052' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO
Or you can create a new SQL Server Agent Job using the SSMS GUI as shown below:
Name the job:
Add a job step to call the replication monitoring stored procedure:
Add the stored procedure call in the job step:
Schedule the job to run as needed:
Replication Notification Emails
Now that the replication monitoring is in place, you will receive email notification like below based on the scheduled you setup. If there are replication issues, the email will highlight that row RED so it easily stands out.
Next Steps
- How to Automate Log shipping monitoring and email timely status notification.
- Read more replication tips
Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager handling small to large SQL Server production support projects. He is technology savvy and believes in automation and innovation. He likes to automate anything and everything which can help save time and make a DBAs life effortless and smooth, to focus on larger projects. He has extensive hands on experience in SQL Server administration and high availability solutions like clustering, replication, log shipping, mirroring, etc. He also possesses strong knowledge of SSRS and SSIS.
- MSSQLTips Awards: Rookie of the Year Contender – 2016
Everything seems to work except this error. If I run the SP on the Distributor, I get results. It’s coming from my Monitoring server. Transaction context in use by another session.
Msg 3930, Level 16, State 1, Procedure usp_GetReplicationStatus, Line 52 [Batch Start Line 0]
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
It would be better if there is a practical session attached (video)
this code is not working on SQL 2017.Do we have any update one.
Hi Atul, Hope you’re doing good. I like your script and really appreciate your work. But you didn’t mention this script is on ONLY works for (TRANSACTIONAL REPLICATION) so can you please help us to also make a script for (MERGE REPLICATION), I’ll be really thankful to you!
Hi Atul, i have multiple replication setup on different servers for a client. how can we automate monitoring of all the replication status using a central managed server.