9

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.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Novox
  • 774
  • 2
  • 7
  • 24

1 Answers1

8

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Since I posted that question, ironically enough, I found your article "[Using tables as Queues](http://rusanu.com/2010/03/26/using-tables-as-queues/)". Since I wouldn't be using the Activation feature of Service Broker anyway, I think I'll be able to cobble together a table-queue based solution instead (for all the reasons you mentioned under "Why not use built-in Queues?"). Thanks! – Novox May 13 '14 at 18:47
  • But a queue is not a bus. If you dont need transport and remote message delivery then the problem is much simpler. As long as you understand that you will never scale out of your one instance hosting the queue-table, is fine. – Remus Rusanu May 13 '14 at 19:20
  • One SQL instance hosting the queue table? Would I not be able to use replication for multiple instances of SQL? – Novox May 13 '14 at 20:19