October 18, 2014 at 11:00 am
Comments posted to this topic are about the item In Praise of Service Broker
Best wishes,
Phil Factor
October 18, 2014 at 1:15 pm
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.
October 18, 2014 at 4:03 pm
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
October 19, 2014 at 3:10 am
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.
October 19, 2014 at 3:17 am
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?
October 19, 2014 at 4:17 am
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.
October 19, 2014 at 7:28 am
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.
October 20, 2014 at 4:48 am
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!!!
October 20, 2014 at 9:03 am
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.
October 20, 2014 at 9:37 am
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!!!
October 20, 2014 at 12:20 pm
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.
October 20, 2014 at 2:38 pm
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.
October 20, 2014 at 2:40 pm
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.
October 20, 2014 at 4:35 pm
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.
October 21, 2014 at 4:44 am
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 18 total)
You must be logged in to reply to this topic. Login to reply