Use Service Broker Internal Activation in SQL Server 2005

by Tim Chapman | Feb 05, 2007 5:47:00 PM

Tags: Databases, Tim Chapman, Microsoft SQL Server, Microsoft SQL Server 2005, Service Broker, Internal Activation, Service Broker application, queue, stored procedure, SQL Server Newsletter

Takeaway: DBA Tim Chapman walks you through the process of building a small Service Broker application that uses Internal Activation to process messages when they are passed.

In previous articles, I covered the theory behind message-based systems and introduced the components of Service Broker, the new feature in SQL Server 2005 for building asynchronous messaging applications in the database engine. In this article, I walk you through the process of building a small Service Broker application that uses Internal Activation to process messages when they are passed.

Internal Activation

Before presenting the example, I want to mention Internal Activation in a Service Broker application. In many cases, it is desirable to handle messages as soon as they arrive on a queue. In a Service Broker application, you can assign a stored procedure to be executed as soon as a message reaches a queue. In a way, it acts like an asynchronous trigger on the queue.

A real advantage of using this technique is that you can configure your queue to fire more instances of the stored procedure if your queue is getting bogged down from receiving a ton of messages. I feel this is a really important function of a Service Broker application, so I'll examine how Internal Activation works in our example.

Example

This example shows how you can call a stored procedure that passes a Service Broker message, which in turn invokes a stored procedure to handle the message that the first stored procedure passes.

USE master

GO

IF EXISTS(SELECT * FROM sys.databases where name = 'SB')
      DROP DATABASE SB

GO

CREATE DATABASE SB

GO

ALTER DATABASE SB

SET ENABLE_BROKER

GO

Use the script above to create the database environment for the example. I must first enable the use of Service Broker in the database before I can use its functionality; the ENABLE_BROKER statement does this for me.

The following script creates the Sales table, which I will use throughout the example.

USE SB;

GO



CREATE TABLE Sales

(
      SaleID INT IDENTITY(1,1),
      SaleDate SMALLDATETIME,
      SaleAmount MONEY,
      ItemsSold INT

);

GO

In order to set up the Service Broker components for use, I create a MESSAGE TYPE and a CONTRACT. A MESSAGE TYPE object validates the content of a message; it allows for tighter control of what information gets sent in a conversation. A CONTRACT object designates the MESSAGE TYPE used and what direction messages can be sent in a conversation.

CREATE MESSAGE TYPE [RecordSale] VALIDATION = NONE;

CREATE CONTRACT [SalesContract] 

(
      [RecordSale] SENT BY INITIATOR

); 



GO

The application will pass a message from one Service Broker queue to another, so I need to set these up. First, I set up the SalesQueue, which will receive messages and activate a procedure to process them. (The SalesService processes the incoming messages and passes them to the SalesQueue.)

CREATE QUEUE [SalesQueue];

CREATE SERVICE [SalesService] ON QUEUE [SalesQueue]([SalesContract]);

GO

The script in Listing A creates the stored procedure that I use as my activation procedure. This procedure will be invoked every time a message reaches the SalesQueue queue.

In the procedure, I use the new TSQL construct RECEIVE to pull a message from the SalesQueue queue. (RECEIVE is very similar to a SELECT statement, except that RECEIVE takes the message from the queue.) If you need to actually view the messages that are in the queue without removing them, you can run a SELECT statement on the queue as if it were a table.

As I receive a message from the queue, I store field values from the queue in local variables. I am especially interested in the @Message variable, which will hold the XML document that I pass into the queue. Because the body of the message is stored as XML, I can use XQuery statements to pull data from the XML document. The data that I pull are the variables that I pass into the original procedure. Once I shred the XML data, I simply insert those values into the Sales table.

Since I plan on sending a message to the SalesService service, I need a service and queue to send the message from. The following statements create RecordSalesQueue and RecordSalesService, which are attached to the RecordSalesQueue.

CREATE QUEUE [RecordSalesQueue];

CREATE SERVICE [RecordSalesService] ON QUEUE [RecordSalesQueue];



GO

When you use Internal Activation with a Service Broker queue, you need to enable it and specify the stored procedure that you will be invoking. I didn't do this when I originally created the SalesQueue, so I need to do it now. It can be done with an ALTER QUEUE statement, as shown below.

ALTER QUEUE [SalesQueue] WITH ACTIVATION 

(
      STATUS = ON,
      MAX_QUEUE_READERS = 1,
      PROCEDURE_NAME = usp_RecordSaleMessage,
      EXECUTE AS OWNER

);

GO

The script in Listing B creates the procedure that I will use to enter sales information. I am going to stay simple, so I am only passing three parameters into the procedure (although it wouldn't be that big of a deal to pass many more if necessary).

You'll notice in this procedure that I am inserting the parameters passed into the procedure into a temp table. Then I query the temp table and place the resultset into an XML variable. This is a very easy way to format your data into XML, rather than building an XML string dynamically.

The BEGIN DIALOG CONVERSATION actually begins the process of passing a message to the SalesService from the RecordSalesService. This statement returns a conversation handle, which you can then use to send the message. The SEND ON CONVERSATION statement does the work of sending the XML message created from the SELECT statement from my temp table.

Everything is set up that I need to activate the procedure once a message reaches it. To see this work, execute the following stored procedure:

EXECUTE usp_SendSalesInfo '1/9/2005',30,90

Once you execute this procedure, run the following SELECT statement to see if a record was inserted into the Sales table.

SELECT * FROM Sales;

The advantage

You may look at the example above and think, "What's the point? Just add a record to the table instead." It is a valid question for this scenario. However, don't miss the idea behind the example.

The goal behind messaging applications is to enable you to send a message and go on about your business. In the example above, if the stored procedure usp_SendSalesInfo were called from a trigger, the insertion of the records would need to finish before the next activation of the trigger could occur. This could definitely lead to a system bottleneck if the trigger does a lot of processing. But, if you only use the stored procedure to send a message to a queue, the trigger processing work can be done very quickly and the work can be done in the background.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

Print/View all Posts Comments on this article

Not Getting the Sales Queue Data loken.bhatt@... | 06/14/07

Suggestion deepsand | 06/14/07

Step I smolka@... | 01/22/09

Step 0 deepsand | 03/28/09

RE: Use Service Broker Internal Activation in SQL Server 2005 kevin.mcginn@... | 11/05/08