Data Management

Use Service Broker Internal Activation in SQL Server 2005

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.

Get SQL tips in your inbox
TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

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.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

5 comments
kevin.mcginn
kevin.mcginn

I was intriguied by the possibilities available through SQL Server service broker in your example detailed in the article titled "Use Service Broker Internal Activation in SQL Server 2005" What I am a bit confused on is that the sender and receiver aspects of the process appear to be tightly coupled. What I want to expand this example so that the send is in one database and the receiver writing to the table is in another database. Is there a detailed example of this configuration available?

loken.bhatt
loken.bhatt

Hi I m selecting the sales queue message after execute the usp_SendSalesInfo procedure how can I know that which message is in the queue can u breifly let me know with code that how the service broker application help me in data caching. bye

deepsand
deepsand

Try the "Questions" forum; the "Discussion" forum is for matters of general discussion, not specific problems in search of a solution. Post problems such as this to the "Question" forum, rather than the "Discussion" forum. There are those who specifically seek out problems in need of a solution, and that's where they go to look for such. Additionally there are the benefits that: 1) The "Questions" forum provides for your feedback, by way of your being able to mark "helpful" responses as such. This does not necessarily mean that a given response contained the complete solution to your problem, but only that it served to guide you toward it. This is intended to serve as an aid to those who may in the future have a problem similar to yours, so that they might have a ready source of reference available, thereby perhaps obviating the need for them to repeat questions previously asked and answered. 2) The revised TR makes it quite difficult to find both "Discussions" and "Questions" that have not had a fairly recent post, owing to some functions comingling them in the listings. By keeping each type in their respective forums, it is easier for all to find what they are looking for.

smolka
smolka

CREATE MASTER KEY ENCRYPTION BY password = 'mypwd' SELECT * FROM SYS.TRANSMISSION_QUEUE

Editor's Picks