SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Service Broker demystified


SQL Server Service Broker demystified

Author
Message
kautuk
kautuk
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 198
Comments posted to this topic are about the item SQL Server Service Broker demystified
Deepak Kolapkar
Deepak Kolapkar
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 190
Hi,

As per my experience planning to go for SQL Server Service broker implementation is a novel thought, but in reality, at certain point where database sizes are very large, and involves a large volumes and heavy traffic... the service broker architecture implementation turns out to be nightmare.

Are there any practical examples, especially from Microsoft where this implementation has proved to be successful?
Lot more in details can be written explaining the pit falls... But would like to stop here till I see more response.

Thanks
kautuk
kautuk
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 198
I would definitely like to know the situations where service brokers turn into nightmares.

As far as huge databases are concerned, service broker is hardly influenced by the DB size. All that matters is the volume of data that is being transported. The best thing about service brokers is their asynchronous nature. So they always have a chance to process the requests at some point of time without affecting other processes. On the other hand if you go for the same communications with triggers (or something similar) you will find yourself at the waiting end until the communication is done.

For an example, you can take one central auditing server which maintains records of all DDL operations across all of your other servers. This has been implemented by many organisations and prove much more reliable and better auditing mechanism than triggers.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27461 Visits: 13268
Nice article. Hope to see the rest of the series with more practical examples.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
kautuk
kautuk
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 198
Thanks for the feedback !!:-D
venoym
venoym
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1465 Visits: 2082
Very well written article, I hope to see more about service broker in the future. As to implementations of Service Broker, basically it's only as complicated as you want it to be. You've seen the basic implementation in this article, you can get really really fancy with retrying, automatic poison message handling, dynamic endpoint routing, and lots more. I've used service broker for an audit implementation and it's really easy to know what can go wrong where and what to do to fix it.
dioscoredes
dioscoredes
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 117
Here is a message queue example which we use. There is a need keep the reporting load off the day time production window where on-line has the priority.
A user requests a long running report during the day. The request is captured in a web app and results in the encoded request being stored in a message queue on a SQLServer. At 0100h an sp is fired up by a scheduler, reads the requests in the queue and executes the long running reports/extracts etc. All finished by 0800h and the users download their output from the website. This way we don't have to write polling sp's to look in a "to-be-actioned" table

(BTW the example in this thread was very interesting but didnt work as coded! Not sure why but comparing the code Dave Pinal's published technique, the deltas were in the mmessage type and contract options. (Not got time to get the bottom of it but many thanks for sparking up the interest. Article much appreciated)

Men who wish to know about the world must learn about it in its particular details. - Heraclitus
dioscoredes
dioscoredes
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 117
The guru's name should read - Pinal Dave. My humble apologies.

Men who wish to know about the world must learn about it in its particular details. - Heraclitus
brent.kremer
brent.kremer
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 181
Very good article.

As for examples I've used it for a central auditing system and a means to communicate with other db servers where I did not want to setup linked servers or SSIS packages. When working in a high security environment I like the ability to really lock down what can and cannot be sent to my servers.

Below is a great case study on how MySpace uses service broker.

http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000004532
Stephanie Giovannini
Stephanie Giovannini
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1525 Visits: 1538
I've used Service Broker in a single server setup in conjuntion with a .NET service application in two different scenarios.

In a manufacturing scenario, a machine-to-SQL interface ran a stored proc on the server that sent a message to the service broker. The interface kicked off the proc every time the machine reached a certain state based on its sensors. The service app ran a listener thread that received messages one at a time and performed the work, in this case, messages sent out the serial port to other machines. Since serial ports are slow, the work had to be queued. The SQL Server had other functions in this scenario--I wouldn't have used it just for queuing, but it certainly was handy for that.

I did something similar in a work distribution system. Messages containing work orders arrived via a web service that ran a procedure that sent a message. A .NET service ran a listener that matched available workers with work orders and it pushed the work via TCP to individual workstations where employees made phone calls to complete the assigned work.

In each case, the service app could have maintained a queue in memory, but SQL Server provided a robust and reliable queue and an interface for adding to the queue.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search