Great article.
Any thoughts on this as a MSMQ replacement? especially with the release of MSMQ 3.0. I have yet to find a solid roadmap for MSMQ, this may be the next "big step" for MS.
Nice article.
However, I've yet to see a "real world" example for Service Broker, anyone got any links?
Thanks for this article. I am curious- how would you continually read the queue- Would you do so with an endless loop or a timer or something?
Thanks,Nate
Any options for some sort of event driven queue - ie, when a message is received, SQL will call a stored proc? Kind of like a trigger I suppose.
Sounds very much like MSMQ - I suppose it does make sense to store it in the DB. I'm yet to find the time to actually play with SQL 2005 Of the hundreds of articles I have read about it, this is the first to talk about the service broker - nice to know it exists!
One of the biggest limitation of Service Broker, when contrasted with MSMQ, is that Service Broker in its existing form in Sql 2005 works only when the two End Points are in Sql Server 2005, whereas MSMQ is wide open to any application having capability to write to the queues not just database applications.
I will clear my last point using the following example. I have an application (written in C++ many years ago), which receives real-time info from a vendor. Currently, I write the info to MSMQ so that no matter how fast info comes, I do not loose it, so I just put it on MSMQ queue. Next, I have another application, in C#, which picks up the message from MSMQ and executes a Stored Procedure on SQL Server, which writes to a table, which has triggers. So, this C# app can continue to process these messages at its own pace.
If Service Broker would have allowed interaction from external apps, I would let my C++ app to write directly to a Service Broker queue. Subsequently, the internal logic within the Service Broker would take the message from the Service Broker queue and process them in the same manner as explained above (execute SP, trigger etc.). This way I can get rid of my C# application and rely solely on Sql Service Broker queueing.
Can the above task/process be achieved by using just Service Broker and bypassing MSMQ completely?
On a different note, I know I can enhance my application by removing triggers and posting messages to Service Broker queue and then process them one by one by reading them from the queue, and processing them further as explained above. This will scale my application better.
But my more imperative need is to have as few in-between applications as possible (like the C# app) and let Service Broker do the work what my app is doing in C#.
Any suggestions, improvements which I can achieve in my approach?
Thanks
Srinivas Sampath,
I was very interested in your article as I am looking at Service Broker closely for my own personal research for an article. Where did you get your diagrams? Are they original? If not, well, you know the circumstances. If they are orignial, I would like to reference them in my next article. Of course, I will put your name on them.
Thanks a bunch!
First off, I apologize for my late response to this thread. I did not have an account with SQL Server central and have just now created one.
To answer your question, the diagrams were created by me (just the brain wave that you get ). Sure you can use them.
Interesting question. I did think about this when I originally read about SSB and people do ask me this question when I deliver sessions on SSB. SSB sure comes close to MSMQ in several ways, but the first thing that I would consider before using SSB is: Do I want to do asynchronous operations in the database? If so, then SSB is for you. Second, SSB requires that both ends of your communication are SQL Server 2005. If this is the case, then again SSB is for you. Third, if you do require transactional message processing (and the above two points hold good), then SSB is for you, since transactional semantics are built in.
