Basic Transactional Replication Monitoring

Anyone who has had to administer SQL Server replication will be familiar with incoming incidents regarding subscriber data being out of sync with the publisher database. Without 3rd party monitoring tools to help alert or track when things fall behind, you are going to have to roll your own as Replication Monitor does not persist anything useful. This post helps you roll your own solution to track how many pending commands there are and alert when a specified threshold is breached.

A few things:

• Massive emphasis on ‘Basic’ monitoring…
• This is for transactional replication and has only been tested using a push subscription
• In addition, it is only for ONE publication, so it has limitations
  • One would have to create multiple implementations of this for multiple publications so maybe only bother with your most critical publications
• If this is something you want to use, test it in non-production first!
  • You should ideally have a non-production environment set up like production

Test setup

To demonstrate the solution for this blog post, I have the following spare lab environments on the rig:

Publisher side (distribution database lives here)

• SQL Instance: SKSQS19DV – 15.0.4430.1
• Database: tpcc

Subscriber side

• SQL Instance: SKSQS17DV\sk_dv – 14.0.1000.169 (yes, slapped hand, it needs a CU)
• Database: tpcc_repl

App server

• WIN01EHUK

We need an app server to generate some replicated commands. We will be using HammerDB to do this.

Implementation Steps

Create a table

First of all, we are going to need a table to store our monitoring telemetry. I recommend you create this in your maintenance database if you have one (e.g., DBA, DBAMaint, etc.).

USE <INSERT DB HERE>;
GO

    CREATE TABLE dbo.ReplicationCommandMonitor
    (
        MonitorID INT IDENTITY(1,1) PRIMARY KEY,
        MonitorDate DATETIME2 NOT NULL DEFAULT (SYSDATETIME()),
        PendingCommands BIGINT NOT NULL,
        CONSTRAINT CHK_PendingCommands CHECK (PendingCommands >= 0)
    );

    CREATE NONCLUSTERED INDEX IX_ReplicationCommandMonitor_MonitorDate
    ON dbo.ReplicationCommandMonitor(MonitorDate DESC)
    INCLUDE (PendingCommands);

Create a custom error message

We need something to fire if a threshold is breached:

-- Create custom error message
USE msdb
GO

EXEC sp_addmessage
    @msgnum = 50001,
    @severity = 16,
    @msgtext = N'Replication Alert: Undistributed commands (%d) exceeded threshold of %d commands.',
    @lang = 'us_english',
    @with_log = 'true';
GO

Create a SQL Agent Alert

We also need an SQL Agent alert:

-- Create SQL Server Agent Alert
USE msdb
GO

EXEC sp_add_alert
    @name = N'High Replication Lag Alert',
    @message_id = 50001,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 900,  -- 15 minutes between alerts (prevent spam)
    @notification_message = N'The number of undistributed replication commands has exceeded the configured threshold. Please investigate replication latency.',
    @include_event_description_in = 1;  -- Include error message details
GO

And to assign it to an appropriate operator:

USE msdb
GO

DECLARE @OperatorName NVARCHAR(128) = N'DBA_Operator';  -- CHANGE THIS TO THE CORRECT ONE
    
EXEC sp_add_notification
        @alert_name = N'High Replication Lag Alert',
        @operator_name = @OperatorName,
        @notification_method = 1;  -- 1 = Email

Create the SQL Agent Job

We need an agent job which runs on a schedule and will alert when a threshold is breached:

Create a job:

USE msdb
GO

EXEC sp_add_job
    @job_name = N'Monitor Replication Undistributed Commands',
    @enabled = 1,
    @description = N'Monitors undistributed commands at the distributor for transactional replication and raises alerts when threshold is exceeded.',
    @category_name = N'Database Maintenance',
    @owner_login_name = N'sa';  -- Adjust owner as needed
GO

Step one in our new job (ensure it is set to run under the distribution database):

Name it something along the lines of: Count and log pending commands

USE distribution;  -- This is the distribution database
GO

DECLARE @PendingCommandCount BIGINT;

SELECT @PendingCommandCount = ISNULL(SUM(ds.UndelivCmdsInDistDB), 0)
FROM dbo.MSdistribution_status ds
INNER JOIN dbo.MSdistribution_agents da
    ON da.id = ds.agent_id
WHERE ds.UndelivCmdsInDistDB IS NOT NULL
AND ds.agent_id =3; --See icky comment below

INSERT INTO DBA.dbo.ReplicationCommandMonitor --Change the target to the database your table is in
    (MonitorDate, PendingCommands)
VALUES
    (SYSDATETIME(), @PendingCommandCount);

PRINT 'Replication monitoring completed.';
PRINT 'Pending Commands: ' + CAST(@PendingCommandCount AS NVARCHAR(20));
PRINT 'Logged to MaintenanceDB.dbo.ReplicationCommandMonitor at ' + CONVERT(NVARCHAR(30), SYSDATETIME(), 121);

SELECT @PendingCommandCount AS PendingCommands;
GO

Things get icky in this first step as you have to figure out which agent_id is the correct one for your publication.

Something like this may help:

SELECT *
FROM dbo.MSdistribution_status ds
INNER JOIN dbo.MSdistribution_agents da
    ON da.id = ds.agent_id
WHERE ds.UndelivCmdsInDistDB IS NOT NULL

Let’s go with agent_id = 3:

Step 2 in our SQL Agent job (ensure it is set to run under the database our monitoring table resides in):

Name it something along the lines of: Alert On Threshold Breach

USE <INSERT DB HERE>;
GO

DECLARE @ThresholdValue INT = 1000;  -- Adjust this threshold as needed
DECLARE @CurrentPendingCommands INT;

SELECT TOP 1
    @CurrentPendingCommands = PendingCommands
FROM dbo.ReplicationCommandMonitor
ORDER BY MonitorDate DESC;

IF @CurrentPendingCommands > @ThresholdValue
BEGIN
    -- Raise error to trigger SQL Agent Alert
    -- This will log to Windows Application Log and trigger the alert
    RAISERROR (50001, 16, 1, @CurrentPendingCommands, @ThresholdValue) WITH LOG;

    PRINT 'ALERT TRIGGERED: Pending commands (' + CAST(@CurrentPendingCommands AS NVARCHAR(20)) + ') exceeded threshold (' + CAST(@ThresholdValue AS NVARCHAR(20)) + ')';
END
ELSE
BEGIN
    PRINT 'Threshold check passed: Pending commands (' + CAST(@CurrentPendingCommands AS NVARCHAR(20)) + ') within acceptable range (threshold: ' + CAST(@ThresholdValue AS NVARCHAR(20)) + ')';
END

SELECT
    @CurrentPendingCommands AS CurrentPendingCommands,
    @ThresholdValue AS ThresholdValue,
    CASE
        WHEN @CurrentPendingCommands > @ThresholdValue THEN 'ALERT TRIGGERED'
        ELSE 'OK'
    END AS Status;
GO

Some more twiddly bits;

--Set job to start at Step 1
EXEC sp_update_job
    @job_name = N'Monitor Replication Undistributed Commands',
    @start_step_id = 1;
GO


--Create Schedule: Run every 5 minutes
EXEC sp_add_jobschedule
    @job_name = N'Monitor Replication Undistributed Commands',
    @name = N'Every 5 Minutes',
    @enabled = 1,
    @freq_type = 4,           -- Daily
    @freq_interval = 1,       -- Every day
    @freq_subday_type = 4,    -- Minutes
    @freq_subday_interval = 5, -- Every 5 minutes
    @active_start_time = 000000,  -- 12:00:00 AM
    @active_end_time = 235959;    -- 11:59:59 PM
GO


--Assign job to local server (if not already)
EXEC sp_add_jobserver
    @job_name = N'Monitor Replication Undistributed Commands',
    @server_name = N'(local)';
GO

Checking Things Over

You should now have a table:

And a job:

In the job, you should have two steps:

Step 1:

And a schedule:

Testing the monitoring

Once the agent job starts firing, rows should start appearing in our table.

Let’s smash some load on the publisher using HammerDB:

Cool, we now have the pending commands being fed into our table:

We should now also be getting failed jobs when our threshold of 1000 is breached:

And voilà, we have alerts being fired and emails being sent out:

Caveats

This is a fragile solution regarding the following:

• agent_id is fragile and can change if the replication is ever dropped and re-created
• If the agent job is wired to Alert on Failure, you will get two alerts, as we have RASIEERROR firing an alert
• The query on the MSdistribution_status view could be intensive, so maybe relax the 5-minute schedule if this is of concern
• There are probably other things wrong with this that I have not thought about
  • Just test it if you’re concerned

Conclusion

This is a very simple monitoring solution to track pending commands in your transactional replication. If you decide to use it, ensure you test it on your non-production environment first (you have one, right?).