Service Broker Activation in SQL Server 2005 Express Edition

January 26, 2009

In the recent installments our series dedicated to the most prominent features of SQL Server 2005 Express Edition, we have been discussing Service Broker functionality, focusing in particular on methods of securing its conversations. In the examples demonstrating their implementation, we took a somewhat simplified approach, which relied on interactive invocation of individual T-SQL statements in order to carry out each step of a dialog between an initiator and its target. While such simplification, introduced for the purpose of clarity, still allowed us to properly illustrate the security-related topics we were interested in at the time, it prevented us from exploring fully automated, functionally equivalent solutions. We will address this shortcoming in this article by presenting a mechanism, known as activation, which eliminates the need for manual intervention into communication between Service Broker partners.

Activation involves associating a Service Broker queue with an arbitrary code (this can be either a stored procedure in the local database or a separate application, resulting in either internal or external activation). Its launch is triggered by an arrival of messages into the queue (or, more accurately, detection of their presence by a built-in process, known as queue monitor), ensuring their automatic processing (which is considerably more efficient than traditional techniques that required developing a custom polling procedure). In addition, in order to accommodate higher rates of incoming messages, Service Broker has the ability to initiate additional instances of the same stored procedure or external program, operating within the context of dedicated queue reader processes. Analogically, their number is reduced (down to zero, if appropriate) as the volume of messages waiting in the queue decreases. Activations and deactivations are handled by queue monitors (created automatically for each activated queue - as you can confirm by querying sys.dm_broker_queue_monitors dynamic management view), which, in turn, dispatch work to queue readers based on the number of messages. Note that this load distribution algorithm must comply with the rule enforcing that all messages from a given conversation group are processed by a single reader, which somewhat limit its scalability (in case of an external activation, it also depends on application response). Maximum number of instances of queue readers that can be created in such manner is determined by the value of the MAX_QUEUE_READERS parameter, configurable via CREATE QUEUE or ALTER QUEUE T-SQL statement (typically, the value of the MAX_QUEUE_READERS parameter is adjusted to match the number of processor cores on the computer hosting SQL Server installation). Activated stored procedures appear as individual rows in the sys.dm_broker_activated_tasks distributed management view for the duration of their execution.

As mentioned earlier, depending on the type of action triggered by messages arriving into a queue, activation can be categorized as either external or internal. In the case of the former, the implementation requires writing a custom code, which relies on SQL Server event notifications (alerting about QUEUE_ACTIVATION event) to initiate a desired application. The latter, on the other hand, leverages functionality built into Service Broker and is fairly straightforward to implement. We will demonstrate its characteristics by modifying the sample conversation we presented in one of earlier articles of this series, which illustrated a Service Broker communication with full dialog security. As you might recall, in order to retrieve messages from the target queue in that example, we have used T-SQL code that employed a combination of WAITFOR and RECEIVE TOP(1) statements. Its purpose was to pause the flow of execution until one or more messages appeared in a designated queue (referenced in the FROM clause), once they arrived, read the first of them (according to the ascending message_sequence_number order), and automatically remove it from the queue (unless its retention setting was turned on). The message content was returned as a result set, consisting of several values, including status, priority, queuing_order, conversation_group_id, conversation_handle, message_sequence_number, service_name, service_id, service_contract_name, service_contract_id, message_type_name, message_type_id, validation, and message_body. Following the successful message retrieval (which was determined by checking value of @@rowcount scalar function that returned the number of rows affected by the preceding statement), we continued in our example by calling INSERT to add selected fields into our custom tbMsgRecv table. In order to implement internal activation, we will simply combine all these tasks (along with corresponding variable declarations) into a single stored procedure.

-- on srvEnt01
USE dbSBEnt01
GO
CREATE PROCEDURE dbo.cspProcessqRecv
AS
DECLARE @convHandle UNIQUEIDENTIFIER;
DECLARE @msgTypeName SYSNAME;
DECLARE @status TINYINT;
DECLARE @srvName NVARCHAR(512);
DECLARE @srvConName NVARCHAR(256);
DECLARE @msgTypeValidation AS NCHAR(2);
DECLARE @msgBody NVARCHAR(50);
DECLARE @cmd AS NVARCHAR(50);
WAITFOR(RECEIVE TOP(1)
        @convHandle = conversation_handle,
        @msgTypeName =  message_type_name,
        @status = status,
        @srvName = service_name,
        @srvConName = service_contract_name,
        @msgTypeValidation = validation,
        @msgBody = CAST(message_body AS NVARCHAR(50))
        FROM qRecv), 
        TIMEOUT 1000;
IF(@@rowcount != 0)
  BEGIN
        INSERT INTO 
               tbMsgRecv(convHandle, msgTypeName, status, srvName, srvConName, msgTypeValidation, msgBody)
               VALUES(@convHandle, @msgTypeName, @status, @srvName, @srvConName, @msgTypeValidation, @msgbody)
        END CONVERSATION @convHandle;
  END
GO

Once the stored procedure is created, you can reconfigure existing queue qRecv using the ALTER QUEUE statement incorporating WITH ACTIVATION clause. Note that its syntax includes the stored procedure name (assigned via the PROCEDURE_NAME entry), value of the MAX_QUEUE_READERS parameter (set to 1 in our example), and security context in which the stored procedure will be running. In this example, we will be using udbSBEnt01 database user for this purpose. (For more information on this particular setup, refer to the article we referenced earlier), however, your choice should be dictated by the level of privileges required to successfully complete all tasks performed by your stored procedure. (Make sure to review the relevant SQL Server Books Online article in order to fully understand the implications of using EXECUTE AS statement). The STATUS parameter allows you to arbitrarily turn off and on the queue activation feature (it is possible to accomplish the same result by switching between 0 and a positive value of the MAX_QUEUE_READERS parameter).

-- on srvEnt01
USE dbSBEnt01
GO
ALTER QUEUE qRecv
        WITH ACTIVATION (
               STATUS = ON,
               PROCEDURE_NAME = dbo.cspProcessqRecv, 
               MAX_QUEUE_READERS = 1,
               EXECUTE AS 'udbSBEnt01'
        )
GO

The outcome of this step can be verified by examining values in a row representing our queue in the sys.service_queues catalog view (in particular, an entry in the activation_procedure column). Sending a sample message from our initiator service should result at this point, in its automatic processing as soon as it is delivered to the qRecv queue. You can easily confirm this by monitoring its content (alternatively, you can also query the tbMsgRecv table or check whether you have received an automatic response in the qSend queue).

In the next article of our series, we will look at additional topics related to Service Broker activation, including specifics regarding security context in which stored procedures invoked in the manner described above are executing.

» See All Articles by Columnist Marcin Policht