SQL Service Broker Part 3: Activation

Service Activation

This post is third in a series on SQL Service Broker. In Part 1, we set up our service broker objects. In Part 2, we showed how we can integrate stored procedures with a .NET application. This application used a rudimentary loop structure to connect to the database and look for new messages.

while(!Done){ReceiveMessages();}

While this certainly works, we know this isn’t the best way to write an application. We can improve this with Service Broker Activation. Instead of a loop constantly pinging, our queue can kick off operations when new messages are received.

Let’s review what we’ve done. We (1) created a table to archive our messages. Yes, this is silly, but it gets the point across of what we’re trying to accomplish. We (2) created a stored procedure to do something with the queue message. Finally, we (3) updated our queue to use activation. When messages are received, the [OnHelloMessageReceived] stored procedure is going to be invoked.

Now that we have this feature available, we have lots of options as developers.

We can perform operations on the message from inside the stored procedure.

We can make direct updates to databases.

We can send replies from the stored procedure.

We can split a message to multiple queues.

We can call other programs, like .NET programs, from a stored procedure. This can be done with the SQL Server Agent.

Basically, if you can do it in a stored procedure, then you can do it from a queue handler.