December 14, 2011 at 10:05 pm
Comments posted to this topic are about the item SQL Server Service Broker demystified
December 14, 2011 at 10:39 pm
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
December 14, 2011 at 11:08 pm
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.
December 15, 2011 at 2:03 am
Nice article. Hope to see the rest of the series with more practical examples.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 15, 2011 at 4:13 am
Thanks for the feedback !!:-D
December 15, 2011 at 6:21 am
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.
December 15, 2011 at 6:24 am
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)
December 15, 2011 at 6:32 am
The guru's name should read - Pinal Dave. My humble apologies.
December 15, 2011 at 7:41 am
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
December 15, 2011 at 12:17 pm
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.
December 15, 2011 at 12:20 pm
double post
December 16, 2011 at 5:56 am
Brent,
Thanks for sharing the interesting case study...:-)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply