THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

Forcing an External Activation with Service Broker

In these last days I’ve been working quite a lot with Service Broker, a technology I’m really happy to work with, since it can give a lot of satisfaction. The scale-out solution one can easily build is simply astonishing. I’m helping a company to build a very scalable and – yet almost inexpensive – invoicing system that has to be able to scale out using commodity hardware.

To offload the work from the main server to satellite “compute nodes” (yes, I’ve borrowed this term from PDW) we’re using Service Broker and the External Activator application available in the SQL Server Feature Pack. For those who are not used to work with SSB, the External Activation is a feature that allows you to intercept the arrival of a message in a queue right from your application code. (Look for “Event-Based Activation”)

In order to make life even more easier, Microsoft released the External Activation application that saves you even from writing even this code.

The External Activator application can be configured to execute your own application so that each time a message – an invoice in my case – arrives in the target queue, the invoking application is executed and the invoice is calculated.

The very nice feature of External Activator is that it can automatically execute as many configured application in order to process as many messages as your system can handle.  This also a lot of create a scale-out solution, leaving to the developer only a fraction of the problems that usually came with asynchronous programming. Developers are also shielded from Service Broker since everything can be encapsulated in Stored Procedures, so that – for them – developing such scale-out asynchronous solution is not much more complex than just executing a bunch of Stored Procedures.

Now, if everything works correctly, you don’t have to bother of anything else. You put messages in the queue and your application, invoked by the External Activator, process them. But what happen if for some reason your application fails to process the messages. For examples, it crashes? The message is safe in the queue so you just need to process it again. But your application is invoked by the External Activator application, so now the question is, how do you wake up that app?

Service Broker will engage the activation process only if certain conditions are met:

But how we can invoke the activation process manually, without having to wait for another message to arrive (the arrival of a new message is a condition that can fire the activation process)?

The “trick” is to do manually with the activation process does: sending a system message to a queue in charge of handling External Activation messages:

declare @conversationHandle uniqueidentifier;

declare @n xml = N'
  <PostTime>' + CONVERT(CHAR(24),GETDATE(),126) + '</PostTime>

begin dialog conversation
from service   
to service     
on contract    
    encryption = off,
    lifetime = 6000

send on conversation
message type
    [] (@n)
end conversation @conversationHandle;

That’s it! Put the code in a Stored Procedure and you can add to your application a button that says “Force Queue Processing” (or something similar) in order to start the activation process whenever you need it (which should not occur too frequently but it may happen).


I know that the “fire-and-forget” (ending the conversation without waiting for an answer) technique is not a best practice, but in this case I don’t see how it can hurts so I decided to stay very close to the KISS principle [Smile]

Published Monday, February 7, 2011 10:23 AM by Davide Mauri
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



mark said:

Nice post, just out of interest what would happen in the above example if the server had just hung and came back to life 30 minutes after force ending the conversation, what would be the result?

December 29, 2011 1:12 PM

Sprinjee said:

I've setup the broker components and my messages arrive in my queue. However my activation application is not fired.

I cannot see any messages in the eventlog

How could I get about finding the cause.

I've checked

- Service is running (under an administrator account)

- Entries in the config file are correct

March 22, 2012 11:45 AM

Jasmin said:

Did you create "EVENT NOTIFICATION " on your target queue?

April 9, 2013 12:06 AM

Leave a Comment


About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog


Privacy Statement