SQL Server Service Broker - Internal Activation

Introduction

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) which 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 its different components are and how they are related to each other. Then I talked about writing SSBS applications in three different scenarios:

·         Initiator and Target in same database

·         Initiator in one database and Target in another database on same instance

·         Initiator and Target in separate databases on separate SQL Server instances

In all these examples we manually send and retrieve the messages from the queue and process it. Do we really need to manually retrieve and process the message every time 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 you to create activation stored procedures, which are called automatically (by Service Broker - Queue Monitor) whenever a message arrives in the queue (very much like a trigger but not exactly the same). Not only this, you can specify the number of instances of the stored procedure to be created to process messages from different conversation groups in parallel if the queue is overloaded with the arrival of lots of messages.

SQL Server Service Broker allows you to set 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 of this stored procedure might be created depending on your setting) to be called allowing you to scale up your distributed application. In the case of external activation, Service Broker sends notification (QUEUE_ACTIVATION event) to an external application/program outside of SQL Server to read the message from the queue and process it. This allows you to actually scale out your distributed application. External activation puts processing logic from outside SQL Server into a separate process, which gives better performance and scalability.

In this article, I will be talking about setting up internal activation and writing a stored procedure, which will be called on activation. In the next article, I will be talking about external activation in detail.

Specifying the Internal Activation

When we create a queue, we can specify the activation settings for the queue. The general syntax of creating the queue is given below:

CREATE QUEUE <Queue_name>

[ WITH

[ STATUS = { ON | OFF } [ , ] ]

[ RETENTION = { ON | OFF } [ , ] ]

[ ACTIVATION (

[ STATUS = { ON | OFF } , ]

PROCEDURE_NAME = <Activation_stored_procedure> ,

MAX_QUEUE_READERS = #No_Of_max_readers_Of_Queue ,

EXECUTE AS { SELF | 'user_name' | OWNER }

) ]

[ POISON_MESSAGE_HANDLING (

[ STATUS = { ON | OFF } , ])

]

[ ON { <filegroup> | [ DEFAULT ] } ]

 

Activation removes the overhead of the processing module to keep on polling the queue for incoming messages. It allows you to create a stored procedure and make it an activator (with PROCEDURE_NAME clause), which is activated when a message arrives in the queue, to process it. Service Broker might create another instance of a stored procedure automatically to process the message the moment it arrives in the queue, for detail see the MSDN article, Understanding When Activation Occurs. You can even specify the number of simultaneous instances of stored procedures (with MAX_QUEUE_READERS clause) that can be created by Service Broker to process messages of different conversation groups allowing you to achieve parallel processing. Please note, messages of a particular conversation group are processed by only one instance of the stored procedure, which means parallelism is available across conversation groups and not across messages within a conversation group.

The EXECUTE AS clause is used to specify the user account credential under which the activation stored procedure runs. If it is OWNER it means the activation procedure will run under the credential of the owner of the queue whereas SELF means the activation procedure will run under the credential of current user or the user executing the CREATE QUEUE statement. You can also specify a different user_name under whose credentials you want the activation procedure to run. To learn more about EXECUTE AS see Context Switching or Database Impersonation using EXEC AS.

By default Service broker handles poison messages (POISON_MESSAGE_HANDLING), which means it disables the queue when some offending messages cause a transaction to rollback five times. Starting with SQL Server 2008 R2 you can disable this default behavior if you have setup custom poison message handling mechanism in place.

In the example below, I am creating a target queue and specifying an activation procedure (Prc_RequestQueueProcessor), with a maximum 10 instances of this stored procedure to be created for parallel processing messages from 10 different conversation groups. Next I am disabling default poison message handling and hence no queue will be disabled even if the transaction rollbacks for more than five times. The activation procedure retrieves the message from the target queue, processes it and sends the response back on the same dialog handle and ends the conversation from the target side.

Creating Queue with activation stored procedure

USE SSBSTargetDB; GO --Create a queue which is an internal physical table to hold --the messages passed to the service, by default it will be --created in default file group, if you want to create it in --another file group you need to specify the ON clause with --this statement. You can use SELECT statement to query this --queue or special table but you can not use other DML statement --like INSERT, UPDATE and DELETE. You need to use SEND and RECEIVE --commands to send messages to queue and receive from it CREATE QUEUE dbo.SSBSLearningTargetQueue WITH STATUS = ON, ACTIVATION ( STATUS = ON, PROCEDURE_NAME = Prc_RequestQueueProcessor, MAX_QUEUE_READERS = 10, EXECUTE AS SELF ), POISON_MESSAGE_HANDLING ( STATUS = OFF ); GO

 

Writing Queue Activation Stored Procedure

Please note the stored procedure, which you specified as an activation stored procedure of the queue while creating it, must exist before creating the queue or else you will get a Msg 2812, Level 16, State 64, Line 3 Could not find stored procedure 'Prc_RequestQueueProcessor' exception.

Writing activation stored procedures is not very different than the code that we used to retrieve the message from the queue; we use the same code to retrieve the message but this time in loop and we terminate this loop when the message is not retrieved. This procedure is called/activated by Service Broker Queue Monitor whenever a message arrives in the queue. During execution or run time, this stored procedure retrieves messages for processing from the queue in a loop, as long as it gets messages in the queue. In other words, the loop terminates (execution of the stored procedure stops) when all messages are processed or messages are exhausted or no more messages are available in the queue.

 

Writing Queue Activation Stored Procedure

USE SSBSTargetDB; GO CREATE PROCEDURE [dbo].[Prc_RequestQueueProcessor] AS BEGIN BEGIN TRY DECLARE @SSBSTargetDialogHandle UNIQUEIDENTIFIER; DECLARE @RecvdRequestMessage XML; DECLARE @RecvdRequestMessageTypeName sysname; SET NOCOUNT ON; WHILE (1=1) BEGIN BEGIN TRANSACTION; --WAITFOR command is used to wait for messages to arrive --on the queue, TIMEOUT is specified in miliseconds WAITFOR ( RECEIVE TOP(1) @SSBSTargetDialogHandle = conversation_handle, @RecvdRequestMessage = CONVERT(XML, message_body), @RecvdRequestMessageTypeName = message_type_name FROM dbo.SSBSLearningTargetQueue ), TIMEOUT 1000;   IF (@@ROWCOUNT = 0) BEGIN IF (@@TRANCOUNT > 0 ) ROLLBACK TRANSACTION; BREAK; END   IF @RecvdRequestMessageTypeName = N'//SSBSLearning/ProductStockStatusCheckRequest' BEGIN DECLARE @ReplyMessage NVARCHAR(max); DECLARE @Quantity smallint SELECT @Quantity = Quantity FROM AdventureWorks.Production.ProductInventory WHERE ProductID = @RecvdRequestMessage.value('(/Request/ProductID)[1]', 'int') AND LocationID = @RecvdRequestMessage.value('(/Request/LocationID)[1]', 'int') SELECT @ReplyMessage = N'<Reply> <Quantity>' + CONVERT(VARCHAR(10), @Quantity) + '</Quantity> </Reply>'; --To send message back to sender you again use SEND command and specify the dialog --handle which you got above while retrieving the message from the queue SEND ON CONVERSATION @SSBSTargetDialogHandle MESSAGE TYPE [//SSBSLearning/ProductStockStatusCheckResponse] (@ReplyMessage); --To end a dialog you use END CONVERSATION command, here the dialog --is being ended by Target, and then a message of -- http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog message type --will be sent to Initiator END CONVERSATION @SSBSTargetDialogHandle; END ELSE IF @RecvdRequestMessageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN --If the conversation is ended by the Initiator then --it also needs to be ended from Target as well END CONVERSATION @SSBSTargetDialogHandle; END ELSE IF @RecvdRequestMessageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' BEGIN --If there is any error message on the queue from the conversation --this conversation needs to be ended END CONVERSATION @SSBSTargetDialogHandle; END COMMIT TRANSACTION; END END TRY BEGIN CATCH IF (@@TRANCOUNT > 0 ) ROLLBACK TRANSACTION;   DECLARE @ErrMessage VARCHAR(MAX) SET @ErrMessage = 'ErrorNumber - ' + CONVERT(VARCHAR(100), ERROR_NUMBER()) + ' : ErrorMessage - ' + ERROR_MESSAGE() --RAISERROR(@ErrMessage, 16, 1) END CATCH END

 

Similar to the way we created an activation stored procedure to process request messages arriving in the target queue, we can also create an activation stored procedure to process the response messages arriving in the sender queue.

If for any reason you want to disable the activation on a queue, you can use either of these statements below.

ALTER QUEUE <QueueName>

WITH ACTIVATION

(

STATUS = OFF

);

 

ALTER QUEUE <QueueName>

WITH ACTIVATION

(

MAX_QUEUE_READERS = 0

);

 

Conclusion

In this article, I talked about setting up internal activation and writing a stored procedure, which was called on activation. In the next article, I will discuss external activation in detail.

Resources

MSDN SQL Server Service Broker
MSDN Service Broker Activation

Security Context of Service Broker Internal Activation

 

See all articles by Arshad Ali