Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Message Queues in Software Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 12:49 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:05 PM
Points: 1,334, Visits: 3,068
jimbobmcgee (1/16/2013)
tl;dr - I can't see the point in Service Broker; it doesn't appear to do what it says on the tin and is too convoluted to use...
_________________
On the surface, it looks like an ideal way to get SQL Server to 'annouce' to your application layer that something has happened (rather than the application layer polling the database every so often to look for changes). I have admittedly not delved far into its workings but, when I did, I wrote it off as a non starter. I'm sure others have done the same.

For me, the problems are: obscurity, complexity, coupling and a lack of userland documentation.

Service Broker is a bit too hidden-away for most of my developers to even think about. This obscurity means that it is not a first choice solution for any problem. Not saying it should matter but, realistically, how useful is Management Studio for promoting/configuring/managing Service Broker. I can see an tree node for a Service Broker Endpoint (but can't do anything with it -- an empty Reports option and a Refresh option is not Management in anyone's Studio). Admittedly, the SSMS-side of things is much better in 2008/R2 but, if this feature was expected to be so useful in 2005, its tools should have been there from the get-go.

Secondly, the complexity. XML is not fun to work with. XML is especially not fun to work with in SQL Server. Sure, anyone can write a basic XML document; I'm sure most of us can also put something together with a nice set-based FOR XML statement and some of us can even get that data back out with the functions built into the XML datatype but that still leaves a lot of people (good, upstanding, professionals in their field) whose eyes just glaze over at the very sight of it. Besides, good XML (with schemas and transforms and validation and such) is a pain in the rear that even the most capable of us usually want to avoid.

Even for all its XML-ness (surely originally designed as a common 'language' for interoptability in data transfer), it still feels like the only thing that can talk to a Service Broker is...another Service Broker. So you have SQL Server talking to another SQL Server. Great, but we can already do that well-enough with Linked Servers -- at least we get to talk SQL to those (even if badly-written queries can lead to vast swathes of data going back and forward across the wire).

Despite all the talk about decoupling and being able to provide interfaces for another process to communicate with, it all still feels so ostensibly coupled. If it is supposed to be a decoupling messaging framework, surely there should be some example out there of how you can listen for these messages from your own appplications (or are you just expected to throw WCF/RabbitMQ/some other messaging system into a SQLCLR if you want to do that?).

There also doesn't appear to be any usable abstraction to work with this stuff in a way that allows portability to another major platform. I know, deep down, that is the point (i.e. to tie you to SQL Server) but most other features can be abstracted away in your app layer -- you don't usually have to rewrite the entire data access layer to switch from SQL Server to Oracle, but you would have to reimplement Service Broker in your new target platform (any real parallels to Oracle Queues?).

Finally, there just doesn't seem to be that much practical information out there about why you should use it. The Microsoft documentation is all 'this is how you do it' (and can be terse, at best), the evangelist blog writers are all 'this is how you do it' (and pretty much just blog about their particular implementation) but noone is really talking about what problems it can overcome and why you might want to consider it.

In the face of that, the howtos all cover the same sort of ground: read data from your table, transform it into a message in a given format and send it somewhere vs. listen for a message in a given format, read the data from it and store/process it. And that seems like an awful lot of work when you could just replicate the data between two servers and have a SQL Agent job poll the replicated table for new rows.

Ironically, when my developers have implemented solutions that would benefit from some sort of messaging, they've ultimately ended up storing those 'messages' in tables, and polling their own service apps. Not saying that it was right, just saying it was so. They understand them, have been using them for some time, and don't necessarily require any glorified string manipulation to get anything usable out of them.

At best, I can see it allow some decoupling between disparate SQL Server databases, whereby you can transform your data, stored in your table layout to a given common format, and they can transform their data, stored in their table layout to the same common format and both can just get along.

Maybe that is the point of Service Broker (and I've only just this second got it!).


I definitely agree JimBob. However, I think there are some clear reasons why this is an underused technology and difficult for most people to use.. First off, it's an invisible subsystem. There’s no visible management (GUI) for the Service Broker inside of SSMS. Plus, its a tedious process building a complete Service Broker application that involves the creating a lot of different objects like messages, contracts, queues, conversations, and routes that aren’t really familiar to most database people. Plus, there is no application wizard builder for it. So, it is built by T-SQL only to my knowledge, making it not an easy thing to build. Another thing you must take into consideration too is: Awareness: Many people that don't use it, and I know many, don't really know its value. So, as a result these things tends to keep this SQL Server subsystem under the radar for most DBA's and organizations. I know many DBA's that still don't know what Service Broker even is, or for that matter, what it does.


"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1408043
Posted Thursday, January 17, 2013 1:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 1:30 AM
Points: 2,898, Visits: 1,795
I'm another one who agrees with JimBob.

I got excited about external activiation but this is service broker talking to an external app, not an external app talking to service broker.

There is already considerable antipathy towards databases in the developer community. The answers in the application layer now what's the question?!??!?

By the time you've set up service broker and got it running they've deployed their RabbitMQ solution and it works.

Factor in a number of 3rd party platforms containing their own ORM and even the idea of calling a stored proc is anathema to the dev teams.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #1408208
Posted Thursday, January 17, 2013 8:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:19 AM
Points: 33,165, Visits: 15,297
David.Poole (1/17/2013)
I'm another one who agrees with JimBob.

I got excited about external activiation but this is service broker talking to an external app, not an external app talking to service broker.

There is already considerable antipathy towards databases in the developer community. The answers in the application layer now what's the question?!??!?

By the time you've set up service broker and got it running they've deployed their RabbitMQ solution and it works.

Factor in a number of 3rd party platforms containing their own ORM and even the idea of calling a stored proc is anathema to the dev teams.


I'm not advocating Service Broker over other queue mechanisms. I think the idea of queueing in architecture has merit.

I agree SB is in need of some tooling and ergonomic design to make it easier to use.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1408446
Posted Thursday, January 17, 2013 9:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 7, 2014 3:34 PM
Points: 2,282, Visits: 1,341
Steve Jones - SSC Editor (1/17/2013)
[quote][b]I'm not advocating Service Broker over other queue mechanisms.


Steve,

From the article and the discussion I took away that you are in favor of the SB technology and whatever architecture is appropriate for the particular IT solution should be investigated, and potentially used. That said, it is prudent of the development teams when they see certain needs within a project that they look at SB technology and determine first if something is available that will work for them. If so use it, it not consider building from scratch or clone/use one you may already have. SB is a proven technology and it is expanding as SOA expands. As we look further to the cloud it may and should be used much more often.

Sorry if this puts words in your mouth. Sometimes I get out of line.

M.



Not all gray hairs are Dinosaurs!
Post #1408493
Posted Thursday, January 17, 2013 1:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:37 AM
Points: 28, Visits: 214
I was excited when I heard SQL was going to get a Queue. Then I saw the implementation and all I could say was "Typical". I would rather use MSMQ.
Post #1408602
Posted Thursday, January 17, 2013 2:52 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 1,432, Visits: 3,227
jimbobmcgee (1/16/2013)

Secondly, the complexity. XML is not fun to work with. XML is especially not fun to work with in SQL Server. Sure, anyone can write a basic XML document; I'm sure most of us can also put something together with a nice set-based FOR XML statement and some of us can even get that data back out with the functions built into the XML datatype but that still leaves a lot of people (good, upstanding, professionals in their field) whose eyes just glaze over at the very sight of it. Besides, good XML (with schemas and transforms and validation and such) is a pain in the rear that even the most capable of us usually want to avoid.


I agree with the obtuseness of Service Broker. But there is no requirement to use XML in any implementation of Service Broker. The messages are just binary messages they can be anything you want them to me. But if you are passing a bunch of different parameters to a service via the actual message XML is a typical way... but it could just as well be a comma delimited string.

However my implementations have used service messages basically just to wake up a service and perhaps hand it an ID of a row in a request table. The request table contains the actual data to be processed in SQL data columns so we don;t have to mess with XML. I also put the conversation handle in there to facilitate better communications especially for multi-threaded services. This has worked out much better than trying to pass data back and forth in Messages.

Finally, there needs to be a one-way message queue implementation. The way service broker is set up it really expects an actual conversation where the client and service actually talk back and forth at least once. Many times all you need is to queue up a request and never expect a response. This is ugly to implement with service broker because by default they expect and end conversation message to be sent.

While it might not have broad appeal there are specific scenarios where this is truly the best and most efficient way to implement a mechanism where two asynchronous processes need to access an external service and it needs to be coordinated within a transaction involving table updates.




The probability of survival is inversely proportional to the angle of arrival.
Post #1408625
Posted Friday, January 18, 2013 12:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:19 AM
Points: 33,165, Visits: 15,297
Miles Neale (1/17/2013)
Steve Jones - SSC Editor (1/17/2013)
[quote][b]I'm not advocating Service Broker over other queue mechanisms.


Steve,

From the article and the discussion I took away that you are in favor of the SB technology and whatever architecture is appropriate for the particular IT solution should be investigated, and potentially used.
...


No worries. Looking back at the piece I can see it's ambiguous. I mention SSSB, and then say "I still think this is a great way to build applications, especially distributed ones, using queues instead of linked servers, ETL, etc."

I meant messaging and queues in general, but the "this" is ambiguous. I should have clarified and said messaging/SOA architectures instead.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1409060
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse