How to monitor SQL Server replication agent status

Determine the replication agents’ status

Insight on replication agent status can be collected manually or programmatically. This article will consider both cases, and also when a replication agents’ status will be determined in relation with all publications, or a particular one.

Review the replication agents’ status in Replication Monitor (SQL Server Management Studio)

Expand the Replication and Local publications folders in Object Explorer, right-click on a desired publication, and choose Launch Replication Monitor option:

The Replication Monitor is a SQL Server Replication native module, which provides necessary information on vital aspects of the replication (e.g. publication and subscription). Along with other features like adding publisher, filtering various information, replication agents’ configuration and maintenance, it also continuously monitors the status of replication agents for particular or for all publications.

The dialog initially shows the information screen, at the first attempt (not shown). The selected branch in the picture above represents the publisher, and on the right, there are information about particular publication or publications, like status, is it in synchronizing process and other.

To review the replication agents’ status, choose the Agents tab in active view, and set Maintenance jobs agent type:

In this case, replication agents are not active (Not running status), and to check relevance of the information displayed, look at the last refresh timestamp in the upper-right corner of the dialog.

Actions which can be performed here are starting/stopping the replication agents, and drill into properties of the Replications agents’ checkup job:

If needed, the Replication agents’ checkup properties can be modified as a regular SQL Server job (refer to creating and maintaining job section from this article).

Replication agents’ status values reference

In the Replication Monitor, the agents’ status is descriptive: running or not running.

To identify the status value, use this as a reference:

Get information on replication agents’ status programmatically (T-SQL and PowerShell)

To collect replication agents’ status information, it can be achieved with two programmatic methods:

  • Method 1: Querying the dbo.MSReplication_monitordata table from a distribution database, with this T-SQL script:
USE distribution
SELECT status AS ReplicationAgentsStatus 
FROM dbo.MSReplication_monitordata
WHERE publication = 'ALL'

A result of this query is a single numeric value (Idle status, in the case above), which represents the replication agents’ status for all publications.

To get information of the agents’ status for the particular publication, modify the where statement like this:

WHERE publication = <name_of_the_publication>
--Names of the publications can be queried within publication column in the same table
--e.g. SELECT publication FROM dbo.MSReplication_monitordata

Replication agents’ status for particular publication is Succeeded, in this case.

#Execute system stored procedure sp_replmonitorhelppublication, against the 
distribution database
#Extract the whole set of results into temp1.txt file
Invoke-Sqlcmd -Query "USE distribution
EXEC sp_replmonitorhelppublication" -ServerInstance "TIKVICKI" | Out-File d:\temp1.txt

#Filtering string which contains information on replication agents' status, and 
bridge it to another temporary file, temp2.txt
Get-Content d:\temp1.txt | where { $_.Contains("status                   : ") } | 
out-file d:\temp2.txt

#Modify and normalize previously mentioned string
#Rename the file into Replication_Agents_Status and append the timestamp (moment of 
the execution)
(Get-Content d:\temp2.txt).replace('status                   ', 'Replication agents 
status') | 
Set-Content d:\Replication_Agents_Status_$(get-date -f yyyy-MM-dd-hh-mm-ss).txt

#Remove temporary files
Remove-Item d:\temp1.txt -Force
Remove-Item d:\temp2.txt -Force

This is how Replication_Agents_Status_(timestamp) should look:

This PowerShell script uses sp_replmonitorhelppublication execution to insert the whole result in the temporary text file. Next step is extracting and modifying the string which contains the information on replication agents’ status and parsing it into another temporary text file, which will become the reference file. Upon every execution of the script, it will rename the reference file into Replication_Agents_Status and append the timestamp. After the process, temporary files will be removed automatically.

Note: Due to the technical limitation of used stored procedure, it is not possible to simply filter the Status column only and return the single value with T-SQL, because of nested stored procedures execution.

Monitor SQL Server replication agents’ status with ApexSQL Monitor

Along with included system, SQL Server and database metrics, ApexSQL Monitor supports the implementation of custom metrics, which help in diagnostics and performance monitoring of the SQL Server specific areas, like SQL Server Replication is.

In this article, a custom metric for monitoring replication agents’ status will be created.

Replication agents’ status custom metric configuration

To create mentioned custom metric, choose a desired SQL server instance in the left pane, go to the Configuration subsystem, and choose the Custom metrics tab:

Custom metrics tab

Input following set of information in corresponding fields like in the picture above and below:

Name Replication agents’ status
Description This metric displays replication agents’ status for all publications (SQL Server Replication feature). Each value represents the overall status (1 – Started; 2 – Succeeded; 3 – In progress; 4 – Idle; 5 – Retrying; 6 – Failed.
Performance category SQL Server performance
Unit (None)
Use percent scale for chart (0-100) No
Period (User-defined)
Metric return type Execution
Query text

For all publications:

USE distribution
SELECT status FROM dbo.MSReplication_monitordata
WHERE publication = 'ALL'

For particular publication:

USE distribution
SELECT status FROM dbo.MSReplication_monitordata
WHERE publication = '<publication_name>'

Custom metrics tab - query text

Note: statement use distribution must remain in the query, because the status information is collecting from distribution database.

Optionally, validate the metric by clicking the Test button.

Select tags

In the dialog above, select the SQL server instance to run the query against it.

After the successful attempt, the information message will appear, and return the single value from the query:

The information message

Replication agents’ status metric

After creation, the metric will appear in the Custom metrics list:

Custom metrics list

Also, it will appear in the Metrics tab list:

Metrics tab list

Review and resolve alerts related to Replication agents’ status

If Alert period (in a desired time span) and thresholds (with values ‘3’, ‘4’, ‘5’, for triggering alert) are set to the metric, ApexSQL Monitor will provide alert on every change of replication agents’ status.

When mentioned alerts trigger, review them in Alerts subsystem:

Alerts subsystem

After a review, alerts related to Replication agents’ status metric can be resolved:

Replication agents' status

As can be seen, the value of the presented alert is ‘4’, which represents the Idle status. Therefore, the scale on the graph is related to the default values of replication agents’ status.

Automate the monitoring of replication agents’ status

To automate and enhance the alerting related to this custom metric, configure email notifications.

Useful resources

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.

 

February 24, 2017