An Introduction to the Service Broker
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sSampath/anintroductiontotheservicebroker.asp
HTH,
Srinivas Sampath
Blog: http://blogs.sqlxml.org/srinivassampath -
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.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Nice article.
However, I've yet to see a "real world" example for Service Broker, anyone got any links?
--------------------
Colt 45 - the original point and click interface
-
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!
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
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.
HTH,
Srinivas Sampath
Blog: http://blogs.sqlxml.org/srinivassampath -
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.
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.
HTH,
Srinivas Sampath
Blog: http://blogs.sqlxml.org/srinivassampath -
First off, I apologize for not responding to this thread earlier. I did not have an account at the forum and recently created one.
Not sure if any commercial scale applications have yet been developed using Service Broker, but I remember Roger Walter writing a few articles about the same and also delivering a few webcasts.
Here is one article about some use cases: http://msdn.microsoft.com/sql/archive/default.aspx?pull=/library/en-us/dnsql90/html/sql2k5_SrvBrk.asp
HTH,
Srinivas Sampath
Blog: http://blogs.sqlxml.org/srinivassampath -
First off, I apologize for not responding to this thread earlier. I did not have an account at the forum and recently created one.
Service Broker messages are always sent to a service. Each service is associated with a queue and each queue can have an activation procedure that wakes up when messages arrive at the queue. You can also specify the maximum number of activation procedures that Service Broker can instantiate for the queue.
HTH,
Srinivas Sampath
Blog: http://blogs.sqlxml.org/srinivassampath -
First off, I apologize for not responding to this thread earlier. I did not have an account at the forum and recently created one.
Service Broker can basically be used to achieve asynchronous semantics in the database. In your case, the application tier receives messages from an external source and if you are not using transactional messaging in MSMQ, the performance of SSB and MSMQ is comparable. Also, in your case, if the application tier is load-balanced, you will have multiple Q's doing the job. I would not recommend replacing this solution with SSB because, if you need to do some pre-processing of the Q before executing the SP, then you cannot do it. Also, by using MSMQ in this case, you can have other applications that can also drain the Q.
SSB is largely for writing reliable, asynchronous, message oriented database applications.
HTH,
Srinivas Sampath
Blog: http://blogs.sqlxml.org/srinivassampath -
I test this script on JuneCTP2005 SQL2005, but
RECEIVE CONVERT(NVARCHAR(max), message_body) AS message
FROM ReceiverQueue
or
SELECT CONVERT(NVARCHAR(max), message_body) AS message
FROM ReceiverQueue
gives nothing
Must the Sender and Receiver be different computers ?
-
First check if the ReceiverQueue itself has any message. If not, check the sys.transmission_queue to see if any errors are logged. One other post that I made had a user with a similar problem and the sys.transmission_queue had a message indicating an error about a missing database master key. If you see a similar error, you can solve it using the method that I've outlined in: http://blogs.sqlxml.org/srinivassampath/archive/2005/06/19/3457.aspx
HTH,
Srinivas Sampath
Blog: http://blogs.sqlxml.org/srinivassampath
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply