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