Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
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 '12 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 '12 at 19:24
    
I think that answers my question, the validation is not automatic, therefore no event is thrown. Thanks. –  Shane Jan 31 '12 at 22:22
add comment

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

share|improve this answer
    
That's a good metric to watch as well. Thanks for that tip. –  Shane Jan 31 '12 at 22:21
    
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". –  Eric Higgins Feb 3 '12 at 22:23
add comment

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).

share|improve this answer
    
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 '12 at 18:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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