January 15, 2013 at 11:01 pm
Comments posted to this topic are about the item Message Queues in Software
January 16, 2013 at 5:38 am
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.
5ilverFox
Consulting DBA / Developer
South Africa
January 16, 2013 at 6:11 am
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!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
January 16, 2013 at 6:45 am
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.
January 16, 2013 at 7:03 am
It would be nice to have more familiarity with the tools to troubleshoot service broker issues.
January 16, 2013 at 9:27 am
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!).
January 16, 2013 at 9:47 am
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.
Not all gray hairs are Dinosaurs!
January 16, 2013 at 10:16 am
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):
1)define the queue
create queue MyQueue (@var1 int, @var2 int) /*here's my interface, too*/
2)define a listener as a sproc
create queueListener My Listener
@var1 int,
@var2 int
as
/*do some stuff*/
3)use the queue like an insert
insert into MyQueue values(1, 2)
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.
January 16, 2013 at 10:56 am
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.
January 16, 2013 at 11:20 am
Steve Jones - SSC Editor (1/16/2013)
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.
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.
Not all gray hairs are Dinosaurs!
January 16, 2013 at 12:49 pm
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. ...:-D"
January 17, 2013 at 1:26 am
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.
January 17, 2013 at 8:14 am
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.
January 17, 2013 at 9:20 am
Steve Jones - SSC Editor (1/17/2013)
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!
January 17, 2013 at 1:33 pm
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.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply