Force replication to fail to test alert

Question

Hi all

I've set up Transactional Replication between a couple of servers and have set up alerts (replication agent alerts enabled).  I am trying to determine the best way to force a failure.  Would simply stopping the sql replication agent be sufficient?  Are there any drawbacks?

thanks -

 will

---

- will

Answer 1

Hi Will,

I was able to simulate the Replication: agent failure alert by executing RAISERROR(14151, 10, 1).  This incremented the Number of occurrences on the History page of the alert properties dialog as well as set the Date of last alert and Date of last response.  The same can be done for other replication alerts.

---

Brandon Williams (blog | linkedin)

Answer 2

Hi Brandon -

Thanks for the reply and suggestion.  Would I just run that raiserror statement in ssms query window?

thanks

 - will

---

- will

Answer 3

Yes.

You might also want to consider setting up mail to alert you when a replication agent fails.  Hilary provides an example in this thread:  http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/abe51b4a-46df-4325-a00f-5be553c20a59/

---

Brandon Williams (blog | linkedin)

Answer 4

Another thing that can be done is a bit of custom monitoring.

One idea is you have a published table that exhibits a high number of inserts and contains a date and/or time column, the last record for both destination and source tables can be compared to get an idea for how in sync both are.  This can be scripted and ran on schedule or programmed into an application in some fashion to alert if anomalies occur.  This is overkill in some cases but ideal in mine.

---

Brandon Williams (blog | linkedin)

Answer 5

Hi Brandon -

Thanks for the help.

I doing some other research, it does seem that there is a group of folks that go the route of custom alert monitoring, instead of depending on the built-in sql alerts.  That sounds like it would require adding alerts or checking for the agent failure as part of the job steps.  But if it works consistently and catches all the failures/errors, it does seem like it is worth the effort.  Our initial plan was to do something similar to your last suggestion of comparing tables between servers.  You mentioned that it is "ideal" for your environment, did you try the built-in alerts and find that they didn't work?

thanks

 - will

---

- will

Answer 6

Hi again Brandon

Along the same lines as my original question, I am wondering about the best way to simulate a latency issue.  Where the replication is exceeding the latency threshold.  I have been able to use the RAISERROR WITH 14151 and 14161 (for latency) and i do get an email from the server.  This is great.  However, i'd feel better knowing if there was a way cause a failure/latency issue that could simulate what might happen in the real world instead of the RAISERROR.   This way, i'd know if we need to move to a custom alert/monitor methodology instead of relying on the built-in alerts.

if you have any thoughts, they would be welcome and appreciated.

thanks

- will

---

- will

Answer 7

Where I work now it is easier for us to do these sanity checks ourselves programmatically and make web service calls to our application error log which we review on a daily basis and act accordingly.  The only built-in alert we currently use is the subscription expiration alert to email if a subscription is about to expire.

The built-in alerts work good but remember to set these using sp_replmonitorchangepublicationthreshold as the GUI doesn't seem to set the @shouldalert properly.  This can be checked and verified using sp_replmonitorhelppublicationthresholds.

Then what you can do to test is set the publication threshold metric latency to something low like 1 second, perform a large update, and synchronize.  This should generate a Transactional replication latency alert if the latency threshold metric is reached and provide a good test.

---

Brandon Williams (blog | linkedin)

Answer 8

Hi Brandon -

Thanks for the additional information and suggestions.  The test for latency sounds like a good idea and one that i'll use.

Thanks for all the help.

 - will

---

- will