3

I've got Database Mail setup and tested fine. Alerts do work when I stop and start the replication agent job, but I cannot get it to throw any alerts when data moves from a publisher db to a subscriber db successfully or unsuccessfully. Ultimate goal is to notify me if something doesn't go right with a data replication.

1
  • you need to grab the error number which you are getting in replication. provide the same error number in the alert and create an alert for that error. once you create alert it will notify you if it occurs again.
    – gaurav
    Jan 11, 2020 at 12:26

3 Answers 3

3

You can set up SQL Server Agent Alerts to trap specific error numbers and respond to them by sending email notifications, or running jobs. In Management Studio, drill down to SQL Server Agent, Alerts. You might have a few defaults in there. If you don't have "Replication: agent success" and "Replication: agent failure", you can create them to watch for error numbers 14150 and 14151, respectively.

HOWEVER, since the agent success message isn't written to the SQL Server error log, you won't be able to have alerts on it by default. I just went through this trying to get email alerts for permission denied errors. You can use sp_altermessage to enable logging for a specific error message, but be careful that it isn't a frequent message, or you'll have some serious error log bloat on your hands. You don't need to do this to enable the agent failure message, which is logged by default.

EXEC sp_altermessage 14150, 'WITH_LOG', 'true'

Then in the "Response" page for the alert, you can choose which operators to notify. Set them up under SQL Server Agent, Operators if you don't have any defined.

3
  • The agent success and agent failure are events that fire when the replication stops and starts, correct? What about something that would tell me that I had new data come into my source db, but failed to make it over to the destination one? Is that "replication subscriber has passed the data validation" event? Error number: 20575. I have 14150 setup already and working fine. I have 20575 setup with logging with sp_altermessage as well
    – Shane
    Jan 31, 2012 at 18:54
  • It sounds like you're on the right track. Validation isn't carried out automatically. There are a couple ways to do it, such as from Management Studio, or a T-SQL script. This article has a few links to different ways to do it: technet.microsoft.com/en-us/library/ms152758.aspx
    – db2
    Jan 31, 2012 at 19:24
  • I think that answers my question, the validation is not automatic, therefore no event is thrown. Thanks.
    – Shane
    Jan 31, 2012 at 22:22
1

My favorite measurement for this is distributor latency.

If data is getting published but never hits the subscriber, the distributor will scream about the latency to deliver. Setting up this type of alert has also served me well in seeing other problems early (network probs for example).

To baseline it, set an alert for distributor latency and set the alert to fire @ 1 second of latency (this alert is in MS). Set your alert to fire no more frequently than every 15 minutes and watch it for a period of time. Then dial in the threshold to what is appropriate for your publication.

I'm not able to send a screenshot atm, but I did find this person's blog post with a script to set the type of alert I'm talking about:

http://sqlblog.com/blogs/linchi_shea/archive/2009/06/05/distribution-latency-in-transactional-replication-is-a-volume-surge-the-culprit.aspx

1
  • You'll know the data hasn't hit the subscriber if this alert fires. Apart from this you won't see any other manifestation of a problem with published data reaching (or not reaching) subscribers that doesn't come with an explicit error, such as "row not found @ subscriber". Feb 3, 2012 at 22:23
0

SSMS -> Connect to the subscriber -> SQL Server Agent -> Alerts -> Double Click "Replication: agent failure" -> Response -> Add your operator with the alert type(s) you wish. (I recommend security groups instead of individuals).

1
  • I have all that setup and I receive alerts when the repliation agent is stopped or started, but not one that tells me if the data did not sync correctly over to the destination db.
    – Shane
    Jan 31, 2012 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.