Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can you/should you use SQL Server Service Broker with .NET applications?

Tags:

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?
like image 380
Jonathan Allen Avatar asked Sep 21 '09 20:09

Jonathan Allen


People also ask

What is SQL Server Service Broker used for?

Service Broker provides queuing and reliable messaging for SQL Server. Service Broker is used both for applications that use a single SQL Server instance and applications that distribute work across multiple instances. Within a single SQL Server instance, Service Broker provides a robust asynchronous programming model.

Are service brokers deprecated?

A few additions were made in 2008 and since then only relatively minor additions have made their way into the product. This tends to lead some people to the (incorrect) conclusion that Service Broker is becoming deprecated – this is most definitely not true.

Does Azure SQL support service broker?

The Service Broker component of Azure SQL Managed Instance allows you to compose your applications from independent, self-contained services, by providing native support for reliable and secure message exchange between the databases attached to the services.

What does a service broker do?

Service Broker helps developers build asynchronous, loosely coupled applications in which independent components work together to accomplish a task.


2 Answers

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.

like image 131
Remus Rusanu Avatar answered Sep 19 '22 16:09

Remus Rusanu


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).

like image 34
RickNZ Avatar answered Sep 20 '22 16:09

RickNZ