August 25, 2010 at 9:57 am
I have service broker up and running accross 2 instances. If data is entered/updated on table A in instance A, it will send a message to the target queue and a stored procedure is used to read in the message and perform the update to table B on instance B.
Once service broker is up and running, does anyone know if there would be scenario where a message would be lost or unprocessed? I'm trying to determine if there is a downside to using service broker as a data replication method. Any insight would be appreciated.
August 25, 2010 at 1:28 pm
There are many facets to Service broker that you have to consider. It guarantees delivery of messages, but it will disable message queues if you have an activated procedure that does not process messages in a timely fashion or repeatedly fails. You can place message sending and receiving in transactions with the other message processing queries.
In my opinion it is very reliable if implemented properly.
The probability of survival is inversely proportional to the angle of arrival.
August 25, 2010 at 1:42 pm
I'd imagine the downsides would really depend on your needs. Here's a nice post I came across detailing some potential issues with SB usage: http://datageekgal.blogspot.com/2008/07/sql-server-service-broker-archival.html
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply