Setting Up Alerts For Replication

We have occasional problems with transactional replication between 2 databases, and the problem is we dont notice right away. Usually the customer notices a few days after replication has stopped, and thats no good. We want to be notified as soon as there is any problem related to replication so we can go in and figure it out.

Ive been playing with the alerts, and it LOOKS like I should be able to do what I want, but after several hours I still cannot make any of the alerts work.

In my test environment, I'm forcing the following error, in order to try and test an alert: error 20598 "The row was not found at the Subscriber when applying the replicated command."

This seems to be one of our more common errors, and also its really easy to produce, so it seemed like a good candidate for testing the alert.

First off, I noticed that the agent profile was set to "Continue on data consistancy errors", which ignores error 20598. So I changed the profile to "Default", which doesnt skip any errors.

Next I set up a simple Job that simply writes a string to a file. I tested the job by calling it manually and it works. Once i can prove that this job is actually being called, I will change it to email me and the team.

Then I created a new alert in the publisher database which watches for error code 20598. Its response is to call the job I mentioned above. So when the error occurs, a string should be written to that file.

Then I forced the 20598 error to occur. I deleted a row in the subscriber DB, and then changed the corresponding row in the publisher. This causes the error to occur. In my replication monitor, I see new entries saying "Skipped 1 error(s) when applying transactions at the Subscriber." I'm not sure why it says skipped, but I know that these entries are the 20598 error that I'm trying to produce.

Now, there was nothing written to my text file, and I can see from the job history that my job was never called. If I look at the history of my test alert, it says "never occured".

I forced the 20598 error to occur, and I have an alert that is watching for the 20598 error, but it never fires.

I also created an alert in the subscriber database, watching for the same error. This alert was not called either.