SQLServerCentral / Editorials / SQLServerCentral.com / Message Queues in Software / Latest Posts

RE: Message Queues in Software

Steve Jones - SSC Editor — Fri, 18 Jan 2013 12:29:35 GMT

[quote][b]Miles Neale (1/17/2013)[/b][hr][quote][b]Steve Jones - SSC Editor (1/17/2013)[/b][hr][quote][b]I'm not advocating Service Broker over other queue mechanisms. [/quote]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. ...[/quote]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.

RE: Message Queues in Software

sturner — Thu, 17 Jan 2013 14:52:06 GMT

[quote][b]jimbobmcgee (1/16/2013)[/b][hr]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 [i]most[/i] of us can also put something together with a nice set-based FOR XML statement and [i]some[/i] 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, [i]good[/i] 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.[/quote]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.

RE: Message Queues in Software

ddriver — Thu, 17 Jan 2013 13:33:24 GMT

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.

RE: Message Queues in Software

Miles Neale — Thu, 17 Jan 2013 09:20:01 GMT

[quote][b]Steve Jones - SSC Editor (1/17/2013)[/b][hr][quote][b]I'm not advocating Service Broker over other queue mechanisms. [/quote]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.

RE: Message Queues in Software

Steve Jones - SSC Editor — Thu, 17 Jan 2013 08:14:17 GMT

[quote][b]David.Poole (1/17/2013)[/b][hr]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.[/quote]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.

RE: Message Queues in Software

David.Poole — Thu, 17 Jan 2013 01:26:55 GMT

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.

RE: Message Queues in Software

TravisDBA — Wed, 16 Jan 2013 12:49:14 GMT

[quote][b]jimbobmcgee (1/16/2013)[/b][hr]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 [i]do[/i] 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 [i]most[/i] of us can also put something together with a nice set-based FOR XML statement and [i]some[/i] 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, [i]good[/i] 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 [i]SQL[/i] 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 [i]entire[/i] 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 [i]practical[/i] 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 [i]what[/i] problems it can overcome and [i]why[/i] 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 [i]that[/i] 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 [i]have[/i] 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 [i]right[/i], 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 [i]some[/i] decoupling between disparate SQL Server databases, whereby [i]you[/i] can transform [i]your[/i] data, stored in [i]your[/i] table layout to a given common format, and [i]they[/i] can transform [i]their[/i] data, stored in [i]their[/i] table layout to the same common format and [i]both[/i] can just get along. Maybe [i]that[/i] is the point of Service Broker (and I've only just this second got it!).[/quote]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. :-D

RE: Message Queues in Software

Miles Neale — Wed, 16 Jan 2013 11:20:05 GMT

[quote][b]Steve Jones - SSC Editor (1/16/2013)[/b][hr]The XML part is definitely challenging for beginners. It's the same with Extended Events and a number of other systems introduced in R2/2012. I don't think it's that hard, but it's certainly off-putting. A little work to return a table of some sort would seem to me, to be a good direction for the SQL Server team.[/quote]I would agree that it is challenging for beginners. It is a different paradigm and requires some creativity and imagination about data. By taking an XML document and converting it to a in memory dataset you can gain access to the data without much problem. You just have to change your perspective. It is challenging and does take some time and effort. But you can wade through it and make it work.M.

RE: Message Queues in Software

Steve Jones - SSC Editor — Wed, 16 Jan 2013 10:56:11 GMT

The XML part is definitely challenging for beginners. It's the same with Extended Events and a number of other systems introduced in R2/2012. I don't think it's that hard, but it's certainly off-putting. A little work to return a table of some sort would seem to me, to be a good direction for the SQL Server team.In terms of explaining this as similar to linked servers, I think it's much more reliable and stronger. Linked Servers suffer from maintaining a connection and being able to reliably ensure that you can query through the LS. A queue works differently, and it provides guaranteed delivery, once the connectivity is up. This means it can work in disconnected scenarios.It's really an architectural re-thinking of how you build an application.

RE: Message Queues in Software

oscar.leeper — Wed, 16 Jan 2013 10:16:49 GMT

I agree with what you have here, Miles. When I looked into the concept of using queueing for a problem I wanted to solve, it sounded like a perfect fit. Researching the implementation details left me feeling like the effort and complexity weren't worth the trouble in my case. This seems to be one of those places where a little something of the Microsoft touch is missing that would have made this incredibly useful to me. Maybe in the sea of additional capabilities queues seem to have I'm missing something, but I would have expected a basic sproc interface to get me up and running in a few minutes with minimal code, leaving all the tuning and tweaking up to those with more time invested (such as future me after I've come to know and love them):[code="plain"]1)define the queuecreate queue MyQueue (@var1 int, @var2 int) /*here's my interface, too*/2)define a listener as a sproccreate queueListener My Listener@var1 int,@var2 intas/*do some stuff*/3)use the queue like an insertinsert into MyQueue values(1, 2)[/code]As someone who works in SQL every day, I'm now able to take advantage of a queued architecture using just two concepts (ddl create and insert) I already know how to use. Give me that and I'm on board.

RE: Message Queues in Software

Miles Neale — Wed, 16 Jan 2013 09:46:59 GMT

The idea is great. The implementation has been challenging. We have explored the decoupling and writing almost everything ourselves and for the most part that has worked without much work and complete control by our developers. I have written and been involved with writing a number of asynchronous processes over the past decade and am well versed in web services and asynchronous batch processes that run on triggers or timers. They are very handy.One or more posters also mentioned that there is the XML issue to deal with. And I agree that it is an issue. However, with a little research and experimentation you can find some very rich tools for working XML into a friend and not a problematic foe. Consuming web service output is not as bad as the wrap some give it. XML is different but it is not that hard. Creation of appropriate schema for large data collections over a large consortium of users is much more of a challenge. But once the schema is decided the use of it is not that bad. That all said, the use of service brokers is good, and if you use something like MSMQ or write a similar process yourself it can give you a great advantage in how your processes perform.M.

RE: Message Queues in Software

jimbobmcgee — Wed, 16 Jan 2013 09:27:37 GMT

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 [i]do[/i] 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 [i]most[/i] of us can also put something together with a nice set-based FOR XML statement and [i]some[/i] 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, [i]good[/i] 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 [i]SQL[/i] 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 [i]entire[/i] 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 [i]practical[/i] 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 [i]what[/i] problems it can overcome and [i]why[/i] 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 [i]that[/i] 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 [i]have[/i] 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 [i]right[/i], 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 [i]some[/i] decoupling between disparate SQL Server databases, whereby [i]you[/i] can transform [i]your[/i] data, stored in [i]your[/i] table layout to a given common format, and [i]they[/i] can transform [i]their[/i] data, stored in [i]their[/i] table layout to the same common format and [i]both[/i] can just get along. Maybe [i]that[/i] is the point of Service Broker (and I've only just this second got it!).

RE: Message Queues in Software

Robert.Sterbal — Wed, 16 Jan 2013 07:03:03 GMT

It would be nice to have more familiarity with the tools to troubleshoot service broker issues.

RE: Message Queues in Software

Brandt Smith — Wed, 16 Jan 2013 06:45:01 GMT

I used queues extensively a long...long...time ago when I developed manufacturing plant floor applications using PASCAL on a CTOS :smooooth: (Convergent Technologies Operating System) platform...we used the Queue Manager built into the platform that drove all the printers...worked great! We could create workflows and de-couple the slower processes to make sure they performed well on these speedy 80186 cpus...CTOS was great for that - but then again - it was purely message based and distributed to begin with! I've been eyeing Service Broker for some time now...and may use it in the future...I've been using SharePoint lists as a sort of queue for not-so-realtime applications (SSIS) to grab the data and bubble it up to the right people thru SharePoint...makes a good way to collect data from the an extranet where the users cannot write into the SQL db - but can write to SharePoint lists.

RE: Message Queues in Software

Gary Varga — Wed, 16 Jan 2013 06:11:54 GMT

Coming from more of a programming background I have utilized message queues many times. Often MSMQ but also various other technologies including Service Broker. As always, great when applied appropriately. Asynchronous, distributed system design is often overlooked in this day of asynchronous programming!!!

RE: Message Queues in Software

Japie Botma — Wed, 16 Jan 2013 05:38:53 GMT

Your link "10 uses for a message queue" should have read: "10 reasons to use message queues". I also would have liked a few examples.

Message Queues in Software

Steve Jones - SSC Editor — Tue, 15 Jan 2013 23:01:50 GMT

Comments posted to this topic are about the item [B]Message Queues in Software[/B]