August 27, 2010 at 2:43 am
can someone help me in setting up Alert e-mail in case replication fails.
I am using SQL server 2005 With SP2. I have configured transactional replication.
With Thanks,
Pankaj Modi.
August 27, 2010 at 10:28 am
I wrote an article published in the June 2010 SQL Server Magazine which includes how to do this. Here's an excerpt:
SQL Server can be configured to raise alerts when specific replication problems occur. When a Distributor is initially set up a default group of alerts for replication related events is created. To view the list of alerts, open SSMS and make a connection to the Distributor in Object Explorer, then expand the SQL Server Agent and Alerts nodes in the tree view. To view\configure an alert open the Alert properties window by double clicking on the alert or right click on the alert and choose the Properties option from the context menu. Alternatively, alerts can be configured in Replication Monitor by selecting a Publication in the left pane, viewing the Warnings tab in the right pane, and clicking on the Configure Alerts button. The alert properties window options for response actions, notification, etc. are the same as an alert for a SQL agent job.
There are three alerts that are of specific interest for transactional replication: Replication: agent failure, Replication: agent retry, and Replication Warning: Transactional replication latency (Threshold: latency). By default, only the latency threshold alerts are enabled (but are not configured to notify an operator). The thresholds for latency alerts are configured in the Warnings tab for a Publication in Replication Monitor. These thresholds will trigger an alert if exceeded and are also used by Replication Monitor to determine if an alert icon is displayed on the screen. In most cases the default values for latency alerts are sufficient, but as always you should review them to make sure they meet the SLAs\SLEs you are responsible for.
A typical alert response for replication is to send a notification (e.g. email) to a member of the DBA team. Since email alerts rely on Database Mail you’ll need to configure that first if you haven’t done so already. Also, to avoid getting inundated with alerts you’ll want to change the delay in between responses to 5 minutes or more. Finally, be sure to enable the alert on the General page of the alert properties window.
January 16, 2011 at 10:09 pm
Pankaj,
Few weeks back, I wrote an article for http://www.sql-server-performance.com on the similar issue, the same can be referred on the below link,
http://www.sql-server-performance.com/articles/dba/email_alerts_notification_p1.aspx
Please let me know if you face any issues.
With Thanks,
Satnam
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply