May 29, 2010 at 12:22 pm
Comments posted to this topic are about the item Getting the Message
Best wishes,
Phil Factor
May 29, 2010 at 7:41 pm
Maybe the reason that "Service Broker " isnt so popular is that no one much has heard of it ? (Like me - building smaller SQL databases for the last 10 years)
If its anything like most of Microsofts products its complex, hard to understand, has inane examples on their websites, confusing upgrade processes amd lots of "gotchas" that blow product development out of the water.
For example, if I have a question about SQL, I get 3 or 4 more relevant, better explained articles with real life examples with a search on Google, than SQL books online, Microsoft "help" pages and (tada) the UI for the product combined. Hey, thats why we subscribe to 3rd party SQL Forums.
Its not enough for a feature to be great, it has to be explained !
May 29, 2010 at 8:57 pm
I think you ask an interesting question because I have researched Service Broker as an applicable solution to issues I've faced on the job.
Why haven't we chosen it and looked at exploring other options?
Service Broker most certainly stands on top of solid core technology, is robust and transactional, and can handle our load - and we need the reliability, etc. but -
1. The configuration one needs to go through to even get it going - permissions, message contracts, queues, schema, etc. are too complicated (too many/too heavyweight).
2. There is no MSMQ "dump" to easily pass messages to other tiers - at least not natively and easily.
Our primary need has been to emit "events" from SQL related operations that could essentially be broadcast to other tiers/applications/components that are not SQL, and everything we end up working through goes back to polling the server.
May 29, 2010 at 10:15 pm
I could not agree more with rwatson's reply above. In additional, I'll add that, in my case, I've spent time understanding it with my DBAs. But there is an anti-MSFT, not-invented-here bias among my more influential *nix technology colleagues who roll their eyes at turn key 'dbms-specific', MSFT solutions like this.
May 30, 2010 at 2:22 am
Yes. I was wondering if the complexity of getting started with it, and the ridiculously bad training materials, was the reason. The product seems very solid and efficient. Adam Machanic did a lot to help improve things in 2007 with a really good couple of practical workbench articles on Simple-Talk http://www.simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench/ and http://www.simple-talk.com/sql/learn-sql-server/service-broker-advanced-basics-workbench/ I found these a great help.
Best wishes,
Phil Factor
May 30, 2010 at 2:47 am
I agree with rwatson and, partially, with futura.
However, we managed to use ServiceBroker to replace a job that repeated every minute to check if new data are available... It took quite a while to get it up and running, especially since it involved cross-server-communication.
If you compare the effort required to transfer incoming data from a "transfer server" to a second server and start a procedure to process the data on the target server it's done much easier with a job running frequently than with ServiceBroker. Not to mention the effort to troubleshoot.
So, since there are alternatives that are easier to manage, ServiceBroker might be the second best solution for most of the tasks (assuming the downsides of the "job-solution" like being "pseudo-real-time", running most of the time just to figure that there are no data and the like will be acceptable...).
@futura: You can start external apps when using External Activator (at least that what I've read, e.g. at blogs.msdn.com . I guess it's worth a try...
May 30, 2010 at 5:13 am
We are getting ready to go live with an (expected) large volume public facing site for the very large retail company where I work.
I had to select a queuing technology, and ended up with SQL Service broker, mainly because you can do point-in-time recovery along with your databases, which is very handy, and also because we have a clustered SQL Server, so we get clustered queues for free. And its ability to have conversations makes it very handy to have items in a conversation processed sequentially while other conversations are processed by other threads in my processing application.
We use it to queue logging messages, as well as delayed writes and all sorts of workflow related messages.
It is a complex product to implement, and there are very few resources on it, although with a bit of digging I did find enough info and tools to get my development done. And it is very solid.
My main gripe with the product is that it disables the queue after 5 consecutive rolled back reads. Also, the requirement to manually close conversations is onerous. It would be nice if conversations could be automatically closed as an option, when the last message in a conversation has been read.
May 30, 2010 at 8:18 am
Unfortunately, the External Activator is only in 2008. I am stuck with 2005 until our vendors support migration to 2008. We're running vendor LOB's and have to wait for their approval process. Always fun.
Thanks, I will keep an eye on it for the future.
May 31, 2010 at 8:35 am
I know that everyone "hates" (or at least frowns on the use of) GUIs, but I can't help but think that Service Broker acceptance would benefit from some sort of UI or wizard to help the novice through the initial stages of creating a queue. He could then look at the generated scripts to see what was going on and learn from them.
June 1, 2010 at 12:55 pm
I think the perceived complexity has hurt it, as is that it falls in the crack between developer and DBA. I suspect few DBA's recommend it, and few developers know it is there. I haven't seen - though there may be - good prescriptive guidance on when to using queuing in general, and when SB is a good fit. For me the win might be the ability to easily spin up multiple threads to process the queue without manually partitioning the workload.
A start would be to make it a lot more visible. A SB task in SSIS for example.
June 1, 2010 at 2:31 pm
I'm with some others. I researched it, set up some basics in 2005 and just found it to be way, way more cumbersome than it needs to be. I'm not completely sure what the best way to do it is, but the way it was in 2005 reminded me of clustering or replication in v6.5. Just too many moving parts.
June 1, 2010 at 2:56 pm
IMO MS did build a great product with wanted technology, but lost the battle before it had begun because of the lack of managebility aids !
Just considering you may have a 50GB database with only 30MB of accounted data blows ones socks out.
All the rest of space was use by a single queue, because the sproc handling the simple processing just took to long and couldn't match the load.
Did you ever try to purge a queue because you queue readers weren't able to cope with the load ? It surely isn't a truncate like operation.
(I know it shouldn't be considered normal, but you have to be able to intervene quickly to get your instance back on track and take your data loss.)
Yes , security is a pain, but it is very good ! Once you've set it up it is rock solid !
For once MS did what is most other respected rdbms vendors do by default. i.e. assume your DBMS engineers first take on the knowledge before trying to run the system, and take time and energy to test and write usage guidelines before implementing in prod.
The missing GUI is one example for this.
I hope to get to testing it with SQL2008R2 very soon.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 2, 2010 at 5:16 pm
Both points made so far--configuration complexity and lack of visibility--are valid.
I think one thing that may help a lot would be to get a good blog or article, possibly on msdn--some place developers/architects may read, not just DBAs, that outlines both MSMQ and Service Broker, explaining the relative strengths/weaknesses, with a sharp focus on helping people get a feel for when you might want to choose one over the other. This would give some visibility to Service Broker and also lead people into choosing it where most appropriate.
June 3, 2010 at 4:43 pm
We set this up for a process that handles "deleted" records in our system. We need to know if something was physically removed so it doesn't show up in another system and can be flagged as removed. It provides a solid solution to handle the deletes, pass them to the other system, process all affected tables, then report back that it's done.
However, I'll agree with others on manageability and setup. It's horrible to do for a first (second, third, fourth...) timer. I had to troubleshoot a new setup to figure out why nothing was processing and it took a day or two to go through everything and finally get it back up and running. Once it was running, we had no other issues, but it turns out I had set up something slightly off and messages weren't being handled properly. They kept cycling through the system until they reached the max value and then expired. They were never caught/handled at the time and we had tons of conversations that were unhandled and had to be removed. 🙁
I think it's a good piece of functionality, but the difficulty to set up and manage added to the lack of good information probably dissuades a lot of DBAs from recommending it or using it.
June 7, 2010 at 10:29 am
To implement queueing properly requires a complex system, which in turn has many parts. Microsoft relegated the creation and management of all those parts to T-SQL for the most part and caused an unnecessary impediment to the steep learning curve already inherent when learning queueing from the ground up. The programmers with MSMQ experience I know were eager to give SQL Broker a chance but were stymied by the complexity of having to build the system from script, unaided by a GUI.
As a long-time DBA who converted from the application programming side of the fence, I don't hesitate to write T-SQL where it saves me time over the GUI. However, I will use the GUI where it saves time over T-SQL. While you can setup and manage a portion of the SQL Broker portions in the GUI, Microsoft should have really invested time in a good wizard and/or management UI for it. It certainly deserves it, as it is a very solid feature and once a crew begins to think more loosely-coupled, opportunities abound for its use --- and would be more popular if it were easier to setup and manage.
It is solid and ready for prime-time, but only in regards to its features and reliability. Is it Microsoft's darling or is it their red-headed stepchild? They have not done it justice in promoting it, providing suitable learning examples, nor in completing its tool set. Yet it has great genetics and it could have a bright future.
David R Buckingham, MCSA, MCDBA
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply