SQL Server Service Broker demystified

  • Comments posted to this topic are about the item SQL Server Service Broker demystified

  • 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.


  • 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.

  • 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

  • Thanks for the feedback !!:-D

  • 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.

  • 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
  • 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
  • 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.


  • 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.

  • double post

  • 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