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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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.
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:
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).