How can SQL Server email an error message when Replication publisher fails to connect to a subscriber system -

There are several remote locations where we set up SQL Server 2005 replication.

2) Customer's inappropriate shutdown,

3) Changes in domain, sometimes the publisher fails to repeat due to several reasons such as

1) Network problems,

password,

4) Changes in SQL password,

5) Failure to switch to client system

Is there a way that we can send SQL Server to an administrator when an email happens when this happens?

Thanks, Chuck

The way I usually handle it, by modifying the SQL Agent job (s) Which are responsible for starting / running replication agents (depending on your replication topology, you have different types of potentially different locations). Just after the "Run Agent" phase that after the execution of / when that stage completes / fails, add the job phase to the appropriate agent job (i.e. Log Reader Agent, Distribution Agent, Merge Agent, Qi Agent, etc.) Depending on whether or not you're using a continuous schedule).

For example, if I have a Transaction Un-Directional push publishing setup, then the delivery agent will be running in the distributor. If I am connected to the distributor and find the SQL agent job responsible for running the distribution agent for this publication, then I can modify the job and add a step to send an email to a particular group if the "Run Agent" Phase fail / complete. If I am using a continuous replica schedule, then I will add the step of the email when the "step agent" phase ends (as I want to inform that the agent stops for any reason). If I'm using a non-continuous schedule, instead I can only run the email step on the failure of the "Run Agent" step. You can also configure the "email" step to send e-mail, for a while Pause for, then try to restart the agent automatically (by configuring the steps for "Go to step 1 for success").

Here is a screen shot showing how the job steps look for a distribution agent, as I have configured the above framework:

In the picture above you will see that I step, called "Notification, Stop, Retry," which at any time prevents the agent (success or failure - This is intentional because I use continuous replication schedule I am doing and whenever Swimming agent who is not running for any reason) This step is basically sends an email to a specific group, one or wait for two minutes, then resume agent. You can add code to do anything, including logging, can only restart the number of fixed times in a fixed time slice, etc. It is easily scripted and repeatable for any number of agents, publications, etc. (I have scripts to ensure any NDAs are included in this type of topology, such configuration in EV replication agent - So this is just a matter of adding or adding performance to a release tool, depending on how you deploy in your environment).