THE SQL Server Blog Spot on the Web

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

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

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.

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

Published Monday, February 07, 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

Comments

 

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

(required) 
(required) 
Submit

About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement