SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An Introduction to the Service Broker


An Introduction to the Service Broker

Author
Message
Srinivas Sampath
Srinivas Sampath
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 1
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
ckempste
ckempste
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5351 Visits: 1

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"
philcart
philcart
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18721 Visits: 1441

Nice article.

However, I've yet to see a "real world" example for Service Broker, anyone got any links?



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
N Cook
N Cook
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 22

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


Ian Yates
Ian Yates
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9016 Visits: 445

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!





Adam Machanic
Adam Machanic
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5597 Visits: 735
Nate:

Look up WAITFOR in the SQL Server 2005 Books Online.

--
Adam Machanic
whoisactive
NvrMind
NvrMind
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 193

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





Dale Corey
Dale Corey
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3075 Visits: 4

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,

Dale

Author: An Introduction to SQL Server 2005 Management Studio
Srinivas Sampath
Srinivas Sampath
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 1

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
Srinivas Sampath
Srinivas Sampath
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 1

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search