22

I have many operations in the database that need to trigger application code. Currently I am using database polling, but I hear that SQL Server Service Broker can give me MSMQ-like functionality.

  1. Can I listen to SQL Server Service Broker queues from .NET applications running on a different machine?
  2. If so, should I do it?
  3. If not, what would you recommend?
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
Jonathan Allen
  • 68,373
  • 70
  • 259
  • 447
  • Why don't you mark an answer if it helped, or put a bounty if you want more answers ? (since it almost 5 years old, you probably don't struggle with it anymore ...) – Noctis May 15 '14 at 06:05

3 Answers3

22

SSB (SQL Service Broker) has a feature named Activation that enabled a stored procedure to be attached to a queue. SQL Server will run this procedure internally when there are messages to consume in a queue. The queue attached procedure can be a CLR procedure, enabling for managed code business logic modules to run (C#, VB.Net etC).

An alternative to an internal activated stored procedure is to have an external client 'listen' on a queue with a WAITFOR(RECEIVE ... ) statement. This syntax is special for SSB and does a non-pooling block until there are messages to receive. Applications then consume the received messages as an ordinary T-SQL result set (like a SELECT). There is also a sample of an External Activator for Service Broker that leverages the event notification mechanism to know when to start an application for consuming messages from a queue.

If you want to see a sample of T-SQL code that leverages SSB internal Activation check out Asynchronous procedure execution.

8kb
  • 10,956
  • 7
  • 38
  • 50
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • I'm really looking for its suitability for sending messsages to outside applications running on seperate machines from the database. – Jonathan Allen Sep 23 '09 at 14:30
  • SSB can only send messages to an instance of SQL. You can use use SQL Express editions on for each separate machine. – Remus Rusanu Sep 23 '09 at 15:16
  • 3
    I know it's 8 years later but the above comment isn't true. Service broker will work just find with external queue readers. It even works well with TPL and EF. – Matthew Whited Sep 05 '17 at 17:33
17

To answer your questions:

Can I listen to SQL Server Service Broker queues from .NET applications running on a different machine?

Yes.

If so, should I do it?

If not, what would you recommend?

You might consider using SqlDependency. It uses Service Broker behind the scenes, but not explicitly.

You can register a SqlDependency object with a SELECT query or a stored procedure. If another command changes the data that was returned from the query, then an event will fire. You can register an event handler, and run whatever code you like at that time. Or, you can use SqlCacheDependency, which will just remove the associated object from the Cache when the event fires.

You can also use Service Broker directly. However, in that case you will need to send and receive your own messages, as with MSMQ.

In load-balanced environments, SqlDependency is good for cases where code needs to run on every web server (such as flushing the cache). Service Broker messages are better for code than should only run once -- such as sending an email.

In case it helps, I cover both systems in detail with examples in my book (Ultra-Fast ASP.NET).

Community
  • 1
  • 1
RickNZ
  • 18,448
  • 3
  • 51
  • 66
2

A easy to use queue library for SQL Service Broker based on rhino-queues

http://github.com/CoreyKaylor/servicebroker-queues

Denis Bakharev
  • 959
  • 1
  • 9
  • 7