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.

http://msdn.microsoft.com/en-us/library/ms171617.aspx (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.

http://blogs.msdn.com/b/sql_service_broker/archive/tags/external+activator/

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:

http://msdn.microsoft.com/en-us/library/ms171601.aspx

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'
<EVENT_INSTANCE>
  <EventType>QUEUE_ACTIVATION</EventType>
  <PostTime>' + CONVERT(CHAR(24),GETDATE(),126) + '</PostTime>
  <SPID>' + CAST(@@SPID AS VARCHAR(9)) + '</SPID>
  <ServerName>[your_server_name]</ServerName>
  <LoginName>[your_login_name]</LoginName>
  <UserName>[your_user_name]</UserName>
  <DatabaseName>[your_database_name]</DatabaseName>
  <SchemaName>[your_queue_schema_name]</SchemaName>
  <ObjectName>[your_queue_name]</ObjectName>
  <ObjectType>QUEUE</ObjectType>
</EVENT_INSTANCE>'

begin dialog conversation
    @conversationHandle
from service   
    [<your_initiator_service_name>]
to service     
    '<your_event_notification_service>'
on contract    
    [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
with
    encryption = off,
    lifetime = 6000
;

send on conversation
    @conversationHandle
message type
    [http://schemas.microsoft.com/SQL/Notifications/EventNotification] (@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).

PS

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 []