SQL Server Service Broker – External Activation

SQL Server Service Broker (SSBS) is a new architecture (introduced with SQL Server 2005 and enhanced further in SQL Server 2008 and SQL Server 2008 R2) that allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable and secure queuing/message based applications within the database engine itself.

In my previous couple of articles, I introduced you SQL Server Service Broker, what it is, how it works, what are its different components and how are they related to each other. And then I wrote about writing SSBS application in these three different scenarios when:

In all these examples, we manually send the messages and retrieve the messages from the queue and process it. Do we really need to manually retrieve and process the message every time when it arrives? Do we have to create a queue monitoring system to monitor for incoming messages on the queue? The simple answer is, NO.

SQL Server Service Broker allows for setting up two types of activation, Internal Activation or External Activation. To handle increased message traffic, in internal activation you specify a stored procedure (multiple instances might be created depending on your setting) to be called and this way you scale up your distributed application, whereas in case of external activation Service Broker sends notification (QUEUE_ACTIVATION event) to an external application/program outside SQL Server to read the message from the queue and process it. This way you actually scale out your distributed application. External activation allows putting heavy weight processing logic outside SQL Server in a separate process than SQL Server which gives better performance and scalability or might run under different credential than the SQL Server service account.

Internal activation would be the right choice if you have few small tasks/activities to do inside your activation stored procedures. But if it takes long, it blocks the processing thread of SQL Server (resources of SQL Server) for that much time and hence internal activation would not be a good candidate in these cases. In these scenarios you can use external activation, which allows you to process messages outside of the SQL Server process (may be on the same or different machine altogether) context.

In my last article I talked about setting up internal activation, writing a stored procedure which will be called upon on activation. In this article, I will be talking about external activation in detail.

How External Activation works

The idea behind external activation is you first create a notification queue/service (Service Broker objects). Then you create an Event Notification for QUEUE_ACTIVATION event (with CREATE EVENT NOTIFICATION command) which notifies the notification service whenever a message arrives in the target queue. In other words, when you send a message to your target service, the message is put inside the target queue, QUEUE_ACTIVATION event is fired and an event notification message is sent to notification queue (this notification message has enough information for your external activation application to reach your target queue that has messages to be processed). Next you write an application which acts like an external activator and could be any custom application like Windows applications, Windows services, and Console applications. This external activator waits for arrival of any notification messages to arrive on the notification queue and upon receiving such notification messages it either starts processing the messages (as it knows about target queue and message from the notification message it has received) or launch another application to start processing the messages.

Now the good part is, you don’t need to write this External Activation application, as Microsoft has provided an External Activator application as part of SQL Server 2008 feature pack and can be downloaded from here or here. In this article I will be talking about this Microsoft provided External Activator for external activation.

The External Activator (ssbeas.exe) is a windows service which by default gets installed in C:\Program Files\Service Broker folder. This windows service uses a configuration file (EAService.config file, which would by default be available at C:\Program Files\Service Broker\External Activator\Config folder) where you specify the details about notification service/queue to monitor for notification messages and which application to launch upon receiving such notification message to process the actual messages from the target queue.

Figure 1 – How External Activator works

In this image you can see that whenever a message arrives in your target queue, the QUEUE_ACTIVATION event is fired. SQL Server then generates a notification message and sends this to notification service/queue. External Activator keeps on looking for notification messages in the notification service/queue and upon receiving them, the external activator launches your custom application that contains the logic to process the message. The external activator can create multiple instances (up to the maximum number of instances you have specified) of your custom application if the current number of instances of your custom applications are not processing the messages as fast as they are arriving in the queue.

Getting started with External Activation

We will be specifying external activation for the same queue which we created in this article. So as I said above, for the first step, I will be creating a notification queue and a service that will be associated with this notification queue.

Please note I am using an in-built Service Broker contract (http://schemas.microsoft.com/SQL/Notifications/PostEventNotification) for notification messages to be posted to the notification service whenever a message arrives in the target queue.

Creating Notification Queue and Service

USE SSBSLearning;
GO
--Creating event notification queue
--which will hold notification messages
--raised by SSBSLearningTargetQueue and will be
--consumed by External Activator
CREATE QUEUE dbo.SSBSLearningNotificationQueue 
GO
--Creating event notification service
--which will be used to send notification messages
CREATE SERVICE SSBSLearningNotificationService
ON QUEUE dbo.SSBSLearningNotificationQueue
(
    [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
)
GO

For the second step, we need to create an event notification for the QUEUE_ACTIVATION event that triggers a notification message to the notification service (which we created above) whenever a message arrives in the target queue. Please note I have my notification service in the same database and hence I have used ‘current database.’ if you have your notification service in another database, then you need to replace ‘current database’ with the service_broker_guid of the database, which you can find in the sys.databases catalog view.

Creating Event Notification for QUEUE_ACTIVATION event

USE SSBSLearning;
GO
--Create an event notification for QUEUE_ACTIVATION event on 
--the queue SSBSLearningTargetQueue to notify service SSBSLearningNotificationService 
CREATE EVENT NOTIFICATION SSBSLearningENForSSBSLearningTargetQueue
ON QUEUE SSBSLearningTargetQueue
FOR QUEUE_ACTIVATION
TO SERVICE 'SSBSLearningNotificationService', 'current database'
GO

For the third step, we need to do two things (I am assuming you have already downloaded and installed the External Activator); in the first part of the third step, we need to change the credential under which the External Activator windows service will be running. This credential must have required permissions (the CONNECT permission to connect to the database, RECEIVE permission to receive the notification message from the notification queue, VIEW DEFINITION ON SERVICE permission for notification service access and REFERENCES ON SCHEMA permission for notification queue schema access) to read notification messages from the notification queue. To change it, go to RUN and type services.msc and click on OK. In the Services Management Console, select Service Broker External Activator service, right click on it, go to Log On tab and change the account and password under which this service will be running as shown below:

Figure 2 – Service Broker External Activator Windows Service

You can find more information about security under the Security Implications section of the ssbea.doc document that gets installed with External Activator application in \External Activator\bin\\ folder.

In the second part of the third step, you need to change the External Activator configuration file (EAService.config) to tell which notification service to monitor for notification messages and which application to launch on receiving such notification messages. This launched custom application will have logic to process the messages from the target queue. With the Concurrency element of this configuration file, you tell External Activator to create multiple (with min or max attributes) instances of custom applications if the current running number of instances are not able to process the messages as fast as they are arriving in the queue.

External Activator allows for enabling verbose logging; to enable verbose logging make sure you make these entries into your External Activator configuration file.

In this article I talked about how external or event-based activation of Service Broker works and what the different steps are for enabling external activation. Then we learned about External Activator, provided as part of the SQL Server 2008 feature pack, how to configure it and how to get it going.

Resources

MSDN – CREATE EVENT NOTIFICATION

Microsoft SQL Server 2008 Feature Pack, October 2008

MSDN – Service Broker Activation

MSDN – Event-Based Activation

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles