SQL Server Replication Alerts and Health Check using Azure Durable Functions (1)
One of my clients has a SQL Server replication process set up by a vendor, but they have no access to it and there are no notifications when the replication fails.
To address this issue, I am researching and proposing solutions to create a proof of concept (POC) that includes configuring a SQL Server replication environment and setting up alerts to trigger email notifications.
Additionally, I am developing a health check process that will indicate the status of the replication process at any given time.
SQL Server Replication Alerts
This article shows how to capture SQL events during the replication process and email them to the replication's administrator. These captured SQL events can be one of the following:
To get started:
1. Create two databases for testing:
2) For simplicity, I have established a Publication where the distribution, publisher, and subscriber all reside on the same database server.
In a common configuration, the distributor, the publisher, and the subscriber databases can be situated on separate servers.
In the images below, you can see how I am adding a subscription's server and database from the Azure Cloud (the same can be done on the Publisher's side):
By doing this, the "msdb" database on the distribution server becomes the location that records all the events necessary for monitoring the replication.
3) Ensure that you have configured SQL Server to send emails using an SMTP server.
4) The events for replication that we want to create alerts for are stored in the "sysreplicationalerts" table in the "msdb" database.
The events recorded in the "alert_error_txt" column have values as below:
Replication-Replication Distribution Subsystem: agent DESKTOP-63L988E\SQLSE-AdventureWorksLT2019-PublicationSource-DESKTOP-63L988E\SQLSE-5 failed.
The process could not bulk copy into table '"SalesLT"."Address"'.
Replication-Replication Distribution Subsystem: agent DESKTOP-63L988E\SQLSE-AdventureWorksLT2019-PublicationSource-DESKTOP-63L988E\SQLSE-4 failed.
The process could not connect to Subscriber 'DESKTOP-63L988E\SQLSERVER2022FRE'.
Replication-Replication Distribution Subsystem: agent DESKTOP-63L988E\SQLSE-AdventureWorksLT2019-PublicationSource-DESKTOP-63L988E\SQLSE-5 succeeded.
The process was successfully stopped.
5) I have created a trigger on the sysreplicationalerts table to pick up the top first newest record and send it in an email:
CREATE TRIGGER SendTheEmails
ON msdb..sysreplicationalerts
AFTER insert
AS
BEGIN
declare @errorMessage varchar(max)
set @errorMessage = (SELECT TOP 1 alert_error_text from msdb..sysreplicationalerts order by alert_id desc)
EXEC msdb.dbo.sp_send_dbmail @profile_name='Eugen Mail',
@recipients='[email protected]',
@subject='Replication errors',
@body=@errorMessage
END
The email received it is displayed below:
6) When the replication it is successful, you can see the tables and the transactions delivered to the replica as seen below:
7) When the replication encountered an error, you can see the errors displayed as below:
8) To create an error during replication, do the following:
Open the publication's properties and change the "Action if name is in use" to "Keep existing object unchanged".
The "Keep existing object unchanged" option is generated errors wherever the data in the snapshot already existed in the replica database.
9) At the same time, an event recorded into the sysreplicationalerts table can be generated by stopping:
? the SQL Job that is running the replication or
? the replication's agents via the "Launch Monitor Replication":
10) Alerts can be configured as well via the SQL Jobs as seen below:
11) More information about creating alert's scripts based on the "sysreplicationalerts" table can be found here:
Other references:
Coming next: SQL Server Replication Health Check using Azure Durable Functions