Activating queue reader processes on demand
In a previous post, I talked about internal activation. But it may not always be feasible to write Service Broker programs as stored procedures. For example, the service program may take a long time to process a message or might require to make a call to a web-service in which case you don't want to eat up a thread from the SQL Server process. Or the program might need special permissions or be able to run as a different user. Or the program may be deployed on a separate machine. But how do you control the instantiation and termination of a stand-alone service programs?
One way to achieve this is through event notification. Service Broker provides an external activation mechanism that delivers a message to a specified service whenever a queue needs activation. Now you can implement this service which could monitor its queue for incoming notifications and launch the right application. The rules for triggering the external activation is similar to internal activation, that is, when a message is enqueued to an empty queue or when the readers aren’t keeping up with the rate of incoming messages, we send a notification.
Figure 1 shows how the external activation mechanism can be implemented using queue_activation event notification. First, create an event notification service that receives the notification messages. This service should include the PostEventNotification contract so that is can receive messages of type EventNotification. Next setup event notification as follows:
CREATE EVENT NOTIFICATION Notify_Activate
ON QUEUE TargetQueue
FOR QUEUE_ACTIVATION
TO SERVICE 'EventNotificationService'
Now whenever TargetQueue needs to be activated it sends an EventNotification message with the queue name to the event notification service. Create a service program which is always running and monitoring the NotifQueue. When it receives a message it can optionally (bounded by a max limit) start a new process with the service program for TargetService.
Mulitple services can be configured to deliver queue activation events to the same notification service so that there can be just one activator service program monitoring its queue for new activations and starting off corresponding processes as required.