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?