In Praise of Service Broker

  • Phil Factor

    SSCoach

    Points: 19823

    Comments posted to this topic are about the item In Praise of Service Broker

    Best wishes,
    Phil Factor
    Simple Talk

  • darkitekt

    SSC Enthusiast

    Points: 169

    I couldn't agree more with the message of this editorial. I have recently implemented a very near real-time (measured in seconds) set of data marts using Service Broker and have used it to abstract large batch processing in an asynchronous fashion. Service Broker is essential to that system and has allowed the architecture to be very flexible.

  • GregLow

    SSC Veteran

    Points: 251

    Also the content that Roger Wolter wrote here: http://www.amazon.com/Inside-Microsoft-Programming-Developer-Reference/dp/0735626022

    And at least two podcasts here: http://www.sqldownunder.com/Podcasts (Niels Berglund and Tom Moreau)

    Locally we run one day workshops on Service Broker every six months as regularly scheduled classes. Surprised it doesn't seem to happen elsewhere.

    Regards

    Greg

  • David Cameron (not the PM)

    SSC Enthusiast

    Points: 131

    I can't agree with the editorial. I've worked extensively with service broker and have not be impressed by the experience.

    1. Poorly architected. The overall structure of service broker is more complex than it needs to be. Every time I've gone back to look at it, it feels as though there are too many elements. Queues, Endpoints, Services and Routes? Choose 2 or 3 from that list. Contracts and Message Types make sense.

    2. Hampered by language. The kind of code you need to write to handle service broker is by it's nature more procedural. This is simply poorly suited to TSQL. The end result is a lot of awkward boilerplate code.

    Overall this feels poorly suited to the database layer. It belongs far more in the client layer. And there are plenty of good technologies

    This is before we get into lower level discussions about poisoned queues, cryptic messages, complex setup and general lack of visibility.

    It is possible to build some quite neat things using service broker. In some limited cases it makes excellent sense. For me this sits in the same class as cursors. The default rule is: don't use them. The rule for experts is: maybe.

  • Jo Pattyn

    SSC-Dedicated

    Points: 31258

    I haven't been investigating service broker because:

    Our team already uses Windows Message Queuing -> What does service broker does better?

    Microsoft's tendency to drop features (was really bad in the silverlight era)

    Probably adds load to sql-server: increased licensing costs

    Added complexity to database

    A nice real world example

    How do you throttle resources for service broker?

  • David Cameron (not the PM)

    SSC Enthusiast

    Points: 131

    Our team already uses Windows Message Queuing -> What does service broker does better?

    No significant benefits. In general far more opaque when it comes watching things converse and messages in transit.

    Microsoft's tendency to drop features (was really bad in the silverlight era)

    I could have missed it but service broker has not substantially changed since 2005. The tooling is slightly better. I have no inside knowledge but this might get deprecated in the future.

    Probably adds load to sql-server: increased licensing costs

    On that front, SB is fine. Standard edition covers it all.

    Added complexity to database

    This is a killer. And it adds complexity with less tooling and language to be able to handle it. You're better off handling messaging outside the database. You're going to end up with chains of triggers firing service broker messages calling procs and no real visibility of the whole process.

    A nice real world example

    The nicest real world example I seen this used for is to get a trigger to fire a SB message back to the database to perform an expensive process asynchronously. For example calculating the size of an image that has been inserted into a database, then populating the row just inserted into.

  • cmille19

    SSCertifiable

    Points: 5920

    As someone who spent nearly a year with one support case dealing with missing records from dropped messages, the problem Service Broker is much bigger than education.

    From a support perspective, there's only a few people in MS support who understand Service Broker--Do you really want to build critical business functionality which has little support?

    Related to support issue, sometimes there's wisdom in following the mainstream. Since most customers do not use Service Broker--This inevitably leads to more potential bugs you're more likely to encounter because less people are using a particular feature or configuration. I've learned this lesson in my career early with SQL Server running Itanium platform, there were unique issues with this platform which were not encountered on x64 or x86. You don't want to be the 1%, 5% or for that matter 10% of customers running a particular configuration.

    Microsoft should have done more to build core features on Service Broker i.e. eat their dog food. If you look at Oracle Streams (or successor GoldenGate for Oracle), both are based on Advanced Queuing which is basically Oracle's equivalent to Service Broker. There's a big potential here with SQL Server replication which is hopelessly stuck in an architecture right out of the 1990's.

    In looking at any technology for providing business functionality we need to ask three questions: How well is the technology supported? What percentage of customers are using the technology? Is the technology used by the vendor to provide other products or features?

    In my case, we decided to rip out Service Broker and replace with middle tier functionality.

  • Gary Varga

    SSC Guru

    Points: 82166

    The problem that I have come across with Service Broker (SB) is that there are other Microsoft technologies that provide distributed processing without the same licensing needs. If you distribute SB then you have additional OS and SQL Server licences. If you use BizTalk you have a richer system with similar licensing costs or if you use bespoke Microsoft Messaging (was MSMQ) processing then you have less.

    The other options also appear to have stronger commitments of long term support from Microsoft.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • bcecil1

    SSC-Addicted

    Points: 408

    Our hesitation was simply the number of worker threads it could potentially spawn. We have a system with 1000's of databases. Our original intent was to have SB run on each one and fire asynchronous processes, but we feared we would tax CPU resources too much.

  • Gary Varga

    SSC Guru

    Points: 82166

    bcecil1 (10/20/2014)


    Our hesitation was simply the number of worker threads it could potentially spawn. We have a system with 1000's of databases. Our original intent was to have SB run on each one and fire asynchronous processes, but we feared we would tax CPU resources too much.

    Was that "fear" out of someone's experience, measurement, whitepaper, etc?

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Peter Schott

    SSCrazy Eights

    Points: 9598

    We eventually got it working successfully for one project, but the complexity to troubleshoot when something wasn't configured is pretty bad. Worse, if you configure something incorrectly you can see all of your resources going to the Service Broker service because conversations and messages aren't being handled/closed/cleaned up properly.

    The concept is enticing, but I'm hard pressed to think why I'd want to use this instead of MSMQ or RabbitMQ to do much of the same thing. Yes, I can implement it all in SQL Server, but is that the best thing to do? In our case, it actually did prove to be a better choice, but if you've got your code outside of SQL handling messages using something other than Service Broker is probably the best choice.

    I also wish MS had decided to implement more on top of it to shake it out a little more. Better tooling would go a long way toward adoption. As it stands right now, I dread seeing it disappear only after something crucial is built that uses it.

  • LSCIV

    Old Hand

    Points: 329

    This is really the first article I've seen where someone is praising Service Broker (SB). We tried SB when it was released in 2005 and it was a disaster. Couldn't handle high-latency links or handle multiple endpoints (ie multiple datacenters) so we dumped it and moved on. MS has since added multicast in SQL 2012 (http://msdn.microsoft.com/en-us/library/bb522893.aspx) which sounds like it would address our last issue but they're about 7 years too late. The pace of development in the SB area is really at a snail's pace. From 2012 to 2014 there were no changes at all. Similar with the move from 2008 to 2008 R2, no changes there either. I like SQL Server, but I don't see my team touching SB anytime soon for the reasons below:

    - Slow development cycle, new functionality seems to arrive in 3-4yr cycles.

    - licensing is cost-prohibitive for larger environments. I don't want to license the entire suite of SQL software for just asynchronous processing.

    - As mentioned by others on this forum, sounds like tooling is still lacking 9 years later.

    I'm sure there are others but those 3 cons alone are enough to drive me away.

  • bcecil1

    SSC-Addicted

    Points: 408

    I did some lab work with 100 databases and service broker running on each one. The load we were putting on it was a little overwhelming. It wasn't that it was difficult to work with or troubleshoot, was just one more level of indirection and overhead we weren't sure we needed.

  • Some Guy from Seattle

    SSC Enthusiast

    Points: 136

    We also have 1000's of DBs and have seen no problem with excessive worker threads or CPU impact.

    One of the things we like about Service Broker is the ability to make message queuing transactional without incurring the overhead of DTC or similar mechanisms. It has been very low impact on our system.

    It would be a shame if Microsoft deprecates it.

  • EdSwiedler

    SSC Enthusiast

    Points: 190

    Before using Service Broker (SB), we had over a dozen Windows Services on another machine scanning tables for data to be processed. Between each scan, they would sleep for a few seconds. Once SB was implemented, these same services just hang a receive on their queues. Furthermore, rather than using the SB reader number to throttle the system, we use the thread pool on the processing server. This offloads all of the CPU cycles, or nearly so, to another server, or servers.

    True, there are other methods that could be used, but we found that SB was the most secure as far as the delivering of messages. It also is the easiest to implement. I would, however, like to see a package that would provide for the monitoring of the entire SB system.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply