Configuration Replication failure and retry alert - SQL Server by serverku

As I wrote about replication latency from publisher to distributor and distributor to subscriber for replication and an alert for replication latency, same as we need to have an alert in case of replication failure. I would like to review some of my earlier posts about replication before moving to this post which you may like,

1. Skip Log Reader error in SQL Server replication - How to
2. Review of some replication issues and workaround - SQL Server
3. Could not find the Distributor or the distribution database for the local server-Error while posts a tracer token in Replication

Let the discussion to move ahead and check as how can we set up an alert for in case of replication failure. So going with steps,

1. Create a job which having static code to fire an email as follows and do not need to schedule it,

DECLARE @Subject varchar(50), @body varchar(200)
SET @Subject = 'Replication Alert (Agent Failure)'
SET @body = 'Replication Alert (Agent Failure).<BR />Check the status of agent from replication monitor.'

EXEC msdb.dbo.sp_send_dbmail 
  @recipients = 'prajapatipareshm@gmail.com',
  @subject = @Subject, 
  @body = @body,
  @profile_name = '<Profile Name>',
  @body_format = 'HTML';
  

2. Go to Replication monitor from Replication tab under SQL Server instance.

3. Select the Publication for which you want to receive an alert for failure and move to Warning tab as follows,

4. Go Configure alert and select “Replication : agent failure” as mentioned in below image,

5. For agent property, click on configure button  –> Response tab and select the job which we created earlier in step 1 in Execute job check box,

This is the just steps to configure an alert for replication agent failure. We can also add a notification alert to job operators also, so whenever the replication agent fails, this configuration runs the job and it will fire an email. We can set up the same configuration for replication agent retry too.