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