October 28, 2014 at 9:54 am
So far I see a lot of unfounded assumptions in this thread and little founded problems.
My firm has implemented a lot of services in our software based on service broker So I think I can make some founded claims on how well SB performs. Admitted, we have had some occasional issues, for example when due to an accidentally created endless loop millions of messages got queued, processed and re-queued. But our systems only got slower from even this maltreatment, Service Broker never broke or even lost a single message. At times it could even prove hard to get rid of messages that we wanted to get rid of ;-). We've processed huge volumes of data asynchronously through service broker that we could never have processed if we would have had to do so synchronously without hindering the end users of our systems. Another a big advantage of SB is that it all happens within the container of the SQL server databases: if you need to restore a database, you're sure that the entries in the SB queues are in-sync with the data in your database(s). There is no way you can do the same thing with MSMQ.
I've seen critics trying to compare SB with MSMQ as if both offer comparable functionality. They do not. SB is a guaranteed delivery system, capable of actively triggering processes upon receipt of a message. MSMQ is merely a passive transport method offering storage possibilities for unprocessed messages. Each has its own area of application and even so true, each has its own problems. As with all power tools you need to choose wisely when and how to apply Service Broker. When applied correctly it can be a very useful tool.
One big disadvantage of SB, that has been addressed in this thread, but may need some more clarification, is not due to SB itself. This the fact that error handling in T-SQL is very hard and in some situations even impossible (most notably: assigning an invalid xml document is considered a fatal error by the SQL engine that can not be caught in T-SQL). Because of this T-SQL should not be the language of choice when writing SB handling code. Yet, pretty much all examples and even the default method of activation is geared towards handlers written in T-SQL. So I agree that the biggest problem for SB to be applied widespread indeed lies within its documentation and examples. More examples and more documentation should be made available getting .Net developers to write auto activated SB handlers instead of the T-SQL handlers. Unless of course error handling in T-SQL is improved significantly in the near future.
October 28, 2014 at 1:42 pm
'Some Guy from Seattle',
...and indeed you had a service broker activation thread on each of these 1000's of databases?
October 28, 2014 at 1:47 pm
Another hesitation for SB for us was that there are a lot of levels to our business that are not 'development'. Prodops/Devops/Support/Implementations...lot's of overhead to train and code for at our young business age. Perhaps in a year or two we'll tackle it when we find we can't scale w/o something like SB, but for now seemed daunting for our group as a whole.
October 28, 2014 at 1:59 pm
I may not have given a complete description of how we use SB. We do have 1000's of DB's that may be queuing messages to SB. Typically there are a few hundred per server. This is done in TSQL and is typically transactional with other data modification actions.
We are not using activation sprocs to receive and process the messages. We have a handful of multi-threaded Windows Services running on different (non-SQL) machines that receive the messages from SB and take appropriate action. This has worked very well for us.
By and large we like Service Broker other than it is a PITA to diagnose problems when something goes wrong.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply