Search code examples
sql-serveresbservice-broker

SQL Service Broker as a generic Enterprise Message Bus for .net

I'm in need of an Enterprise Service Bus/Message Queueing solution for publisher/subscriber functionality. I know MANY exist... MSMQ, MS Series, RabbitMQ, NServiceBus, etc etc etc...

My one requirement is that in a shared hosting solution, the only dependency that I can guarantee will exist is SQL 2005 and later... this leads me directly to SQL Service Broker.

If it sounds like I'm trying to shoehorn ESB functionality into SSB... I suppose I am...

My question is: does anyone know of a .NET API or framework that sits on top of SQL Service Broker and already provides much of the plumbing?

If I were to use pure ADO.net, I could add items to the queues by calling a stored procedure, but then:

  1. Do to the nature of conversations, would I make one conversation per message?
  2. If so, do I lose sequential message processing?
  3. How do I receive messages (I know the receive syntax in t-SQL), do I call a stored procedure repeatedly in a message loop to try to get a message off the queue?
  4. Or would I WAITFORever? Keeping the connection open and executing the stored procedure forever?
  5. SQL Service Broker doesn't support monologue conversations, but I read they can be implemented...

It's these kind of questions that make me wish there existed a .net solution that already managed all of this.

Solution

  • There was an effort to package a WCF Transport Channel for SQL Server Service Broker but, afaik, is abandonware.

    But NServiceBus supports Service Broker as a transport, see Using NServiceBus and ServiceBroker.net and there are github projects like A simple wrapper API for SQL Service Broker and an ITransport plugin for NServiceBus. While not exactly mainstream, some support and community effort does exists.

    As an ESB I think you will have problems due to lack of true pub-sub and broadcast. SQL Server 2012 has the ability to SEND a message to multiple targets, see How to Multicast messages with SQL Server Service Broker, but you will still have to implement the pub-sub infrastructure (publishing topics, subscribers etc) from scratch. MySpace did that and was a major effort, see Scale out SQL Server by using Reliable Messaging. My observation reffers to the low level direct use of SSB, I have never used NServiceBus so I cannot tell how well does it abstracts/expose unicast/broadcast/multicast/pub-sub over SSB.

    As for your specific questions, I recommend reading Writing Service Broker Procedures and Reusing Conversations.