Service Broker Activation in SQL Server 2005 Express Edition

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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles