Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Asynchronous Procesing with Service Broker

By Gary Strange,

Introduction

When I first heard of the mysterious service broker I was intrigued to find out what it was and what it could do. So I did some reading up and discovered it can play a part in building a range of enterprise solutions. One feature that generated a great deal of interest for me was the asynchronous stored procedures. When writing asynchronous architecture in the past I’ve had to use application languages to orchestrate asynchronous database operations. But now apparently I can write them at the database level.

This isn’t precisely true. Service broker enables you to call stored procedures asynchronously but not write asynchronous stored procedures. This document will hopefully get you familiarised with service broker’s components and understand some of its capabilities.

My case is what I would call a "lab case" and is not intended to represent any sort of production solution.

After reading this document you will have basic understanding of what service broker is, what basic components make up a service broker solution and be able to write your own asynchronous database processes.

Ingredients List

Here is a list of all the components discussed in this document. All of which are available in the accompanying code (except the SQL server platform of course). I used SQL Server 2005 or above (I’m developing against SQL 2008 R2 standard, so you might need to revise the code other platforms)

  • One source database
  • Service broker enabled on your source database.
  • Two message types
  • One message contract
  • Two queues
  • Two services
  • One log table
  • One errors table

Switch on Service Broker

SQL server is locked down when you get it out of the box. So remove that bubble wrap and enable service broker on the database you’ll use for this lab.


ALTER DATABASE TestDB
 SET enable_broker

Message Types

You will need two message types. You could consider your message types to be two people conversing at a counter like a landlord & patron or a hardware shop keeper & a customer. They both have their own dialect in the conversation so we need to define a message type for each of them. For this example I’m simply specifying that I want the messages to be xml types. You can explore the additional options at http://msdn.microsoft.com/en-us/library/ms187744.aspx

For production systems you should consider using XML schemas to validate your XML form.


-- Message used to speak shopkeeper (service)
CREATE MESSAGE TYPE
[//HardwareStore/RequestMessage]
VALIDATION = WELL_FORMED_XML
 -- Message used to speak to customer (service)
CREATE MESSAGE TYPE
[//HardwareStore/ReplyMessage]
 VALIDATION = WELL_FORMED_XML

Contract

A contract can be compared to the definition of a conversation between two people with a specific agenda. If we come back to a previous example of a hardware shop keeper & customer we will have specific requests like "do you have a hammer", "do you have some nails" and the store owner would reply "yes I have 3 hammers", "yes I have a fine selection of nails". In this example we can see that the customer initiates the conversation with a request and the shop keeper, the target of their request, replies.


CREATE CONTRACT [//HardwareStore/ShopKeeper_Customer_Protocal]
(
      [//HardwareStore/RequestMessage] SENT BY INITIATOR,
      [//HardwareStore/ReplyMessage] SENT BY TARGET
);

Queue

A queue does exactly what it says on the tin. If you don’t know what a queue is then I would suggest reading up on stacks and queues before reading on. A queue’s representation in SQL Server can be closely compared to a table. For example "select * from queue" will show you the contents of a queue in tabular form. Now imagine our customer comes into the shop and rattles off 100 items to be requested from the shop keeper. There is no way the shop keeper can check his inventory, return replies to customer and keep up with this incredibly demanding customer's requests.

So the clever shop keeper writes a list (queue) as the customer is rattling of 100 items and relays items on the list to his team in the back of the shop, who in turn check the inventory and return availability counts to the shop keeper. So the shop keeper tries to get his own back on the demanding customer and doesn’t wait for him to finish his 100 item request but starts returning the 100 inventory counts to the customer as they come in from back of shop. As the customer is unable to remember all the returning inventory counts, his must write the down (queue).


 -- The customer maintains a list of responses from the shopkeeper(service)
CREATE QUEUE CustomersResponseToAskList
 -- The shop keeper has a list of requests from the customer
CREATE QUEUE CustomerRequestList

Service

Services define a service broker solution’s end points. They’re the mechanism for sending and receiving messages to and from the queues. They enforce the contract (conversations) that will be conducted between queues and they are the main point of interaction for your calling code. In the shop keeper & customer paradigm we should consider the shop keeper & customer as individual services. The customer has direct access to the list (queue) of items he want to receive information about. The shop keeper has direct access to the list (queue) of requested items he needs to gather information about. Both the shop keeper and the customer have a understanding of how to conduct themselves within the shop environment (contract) and this understanding leads to a polite conversation with the correct protocol (message types).


-- The customer knows about its list and knows how to converse with the shop keeper.
CREATE SERVICE
[//HardwareStore/Customer]
ON QUEUE CustomersResponseToAskList
(
      [//HardwareStore/ShopKeeper_Customer_Protocal]
);
 -- The shop keeper knows about his list and the way he and the customer will converse.
CREATE SERVICE
[//HardwareStore/ShopKeeper]
ON QUEUE CustomerRequestList
(
      [//HardwareStore/ShopKeeper_Customer_Protocal]
);

Activation, the Show Stopper

When we talk about asynchronous queries "Activation" is the show stopper. It lets us do some very interesting things and I would encourage reading up on its capabilities. For the purpose of this exercise we will be using simple internal activation.

So far we know what the characteristics and behaviour of the customer and shop keeper is all about, but we haven’t really understood what his backroom staff are doing. Well we know that they have a predefined task that they can repeat consistently time and time again. The result of the task, the inventory counts, is passed backed to the shop keeper. I would say they sound a lot like stored procedures and as there are many replicating the same task I would say there are many instances of the same stored procedure.

So how does our shop keeper (service) communicate with the back room staff (stored procedure instances)? Simply by taking an item off the list (popping the queue) and instructing each member of staff (stored procedure) to carry out their duties and fetch inventory counts. But it is too much for the shop keeper satisfy all the requests from customers, manage the list and keep all his staff busy. So he employs a back office manager (Activation Stored Procedure), who manages the operation for him, relieving the shop keeper of the additional responsibilities.


-- Employ the staff manager to get those workers working.
-- See full details regarding parameters at msdn
ALTER QUEUE CustomerRequestList
WITH ACTIVATION
(
   -- Yes we want activation to start straight away.
   STATUS = ON,
   -- We want to target a local procedure delegate.
   PROCEDURE_NAME = StaffManager,
   -- We want to process current requests, 20 is enough for this example.
   MAX_QUEUE_READERS = 20,
   /* I've not covered security in my article as it should have it deserves its own independent article.*/
   -- Here I have simply created a SQL login with dbo privileges to execute the delegate sp.
   EXECUTE AS 'devlogin'
);

Two Part Activation Stored Procedure

I have split the activation behaviour into two parts: a pop stored procedure & a processor stored procedure. The pop sp manages retrieval of messages from the shop keeper request list (queue) and the processor sp translates the message into something that can be executed using SQLXML. After execution the pop sp then passes a reply (message) back to the customer (service) which completes the request (conversation). You could consider the pop stored procedure as the staff manager who well manages the workers (processor stored procedure).

CREATE PROCEDURE StaffManager
AS
BEGIN
  DECLARE @ReceivedRequestDlgHandle UNIQUEIDENTIFIER;
  DECLARE @ReceivedRequestMessage XML;
  DECLARE @ReceivedRequestMessageType SYSNAME;
  WHILE (1=1)
      BEGIN
        BEGIN TRANSACTION
        WAITFOR -- Pop the request queue and retrieve the request
         (
          RECEIVE TOP(1)
            @ReceivedRequestDlgHandle = conversation_handle,
            @ReceivedRequestMessage = message_body,
            @ReceivedRequestMessageType = message_type_name
            FROM CustomerRequestList
          ), TIMEOUT 5000;
          -- We will wait for 5 seconds for a message to appear on
          -- the queue.
          -- it more efficient to cycle this sp then to create a
          -- brand new instance.
          IF (@@ROWCOUNT = 0)
            BEGIN
              ROLLBACK
              BREAK
            END
          IF @ReceivedRequestMessageType =
                   N'//HardwareStore/RequestMessage'
            BEGIN
              DECLARE @ReturnMessage XML;
              DECLARE @ReplyMessage XML;
              BEGIN TRY
                -- Instruct worker to execute the request.
                EXEC Worker @ReceivedRequestMessage,
                            @ReplyMessageOUTPUT
              END TRY
              BEGIN CATCH
                -- Record errors.
                INSERT INTO ExecutionErrors(
                            ExecutionRequest,
                            ErrorMessage )
                 SELECT @ReceivedRequestMessage,
                        ERROR_MESSAGE()
              END CATCH;
              -- Send reply with results.
              SET @ReplyMessage = (
                SELECT @ReceivedRequestMessage, 
				       @ReplyMessage FOR XML PATH('ExecutionComplete'));
              SEND ON CONVERSATION @ReceivedRequestDlgHandle
              MESSAGE TYPE [//HardwareStore/ReplyMessage]
                  ( @ReplyMessage );
            END
            ELSE IF @ReceivedRequestMessageType = 
			      N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
             OR @ReceivedRequestMessageType = 
			      N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
            BEGIN
              END CONVERSATION @ReceivedRequestDlgHandle
            END
            COMMIT
       END
END

Plus the worker stored procedure...

-- The worker sp understands the request format, extracts the execution information and executes the request.
CREATE PROCEDURE Worker
      @RequestMessage XML,
      @ReturnMessage XML OUTPUT
AS
BEGIN
  DECLARE @ParamDefinition NVARCHAR(4000)
  SET @ParamDefinition = STUFF((
  SELECT
    ','  AS[text()],
    collection.param.value('@parametername','sysname') AS[text()],
            ' = ' AS[text()],
    CASE WHEN
        collection.param.value('@dbtype','sysname') LIKE '%char%'
      OR
        collection.param.value('@dbtype','sysname') = 'sysname'
     THEN '''' + collection.param.value('@value','nvarchar(4000)')
           + ''''
     ELSE
        collection.param.value('@value','nvarchar(4000)')
    END AS [text()], 
    SPACE(1)AS[text()],
    NULLIF( LOWER(collection.param.value('@direction','sysname')), 'input')AS[text()]
   FROM @RequestMessage.nodes(
           '/sqlobject/sqlcommand/sqlparametercollection/sqlparameter')
            AS collection(param)
   FOR XML PATH(''),TYPE).value('.','nvarchar(4000)'),1,1,'')

   DECLARE @Query NVARCHAR(4000)
   SET @Query =N'EXEC '
            + @RequestMessage.value('(/sqlobject/sqlcommand/@commandText)[1]', 'nvarchar(4000)')
            +SPACE(1)
            + @ParamDefinition
      -- Declare table to hold result. This is a somewhat crude
      -- method of processing a request/result.
      -- A more elegant solution is waiting to be discovered.
   DECLARE @InventoryCount TABLE (ItemVolume BIGINT)
   -- Process the request and save result.
   INSERT INTO @InventoryCount
   EXEC (@Query)
   -- Convert the result recordset to xml and pass it back to the
   -- caller (OUTPUT var).
   SET @ReturnMessage = (SELECT ItemVolume FROM @InventoryCount FOR XML PATH('WorkerReport'))
END

Worker Capabilities

The worker may be able to carry out many tasks in the hardware store. But for this lab session we will declare just one possible task. We will define the task of counting the inventory. Again we will use a stored procedure to encapsulate the repeatable process. As it would take a lot work to define an actual hardware store warehouse in SQL tables, I will simply use table counts to represent some mock hardware store inventory counts. The stored procedure will simply accept a table name and return its total row count.


-- Create a very simple sp for the purpose of demonstrating service broker.
CREATE PROCEDURE uspInventoryCounts
                @tableName SYSNAME
AS
BEGIN
  EXEC('select COUNT(*) from '+ @tableName)
END
GO

Making a Service Request

The customer needs to make request to the shop keeper, he has all the knowledge about how to communicate with the shop keeper so it just needs to be persisted in a repeatable process.


CREATE PROCEDURE CustomerRequest
      @RequestMessage XML
AS
BEGIN
  DECLARE @InitDlgHandle UNIQUEIDENTIFIER
  BEGIN TRANSACTION
  -- Create a conversation between the customer and shopkeeper
  -- using the appropriate protocol.
  BEGIN DIALOG @InitDlgHandle
            FROM  SERVICE[//HardwareStore/Customer]
            TO          SERVICE '//HardwareStore/ShopKeeper'
            ON          CONTRACT
                  [//HardwareStore/ShopKeeper_Customer_Protocal]
            WITH  ENCRYPTION =OFF;
      -- Start the conversation my sending.    
      SEND ON CONVERSATION @InitDlgHandle
      MESSAGE TYPE[//HardwareStore/RequestMessage]
             ( @RequestMessage );
  COMMIT TRANSACTION
END

Receiving Processed Replies

As we mentioned before the trades person (service) will keep a list (queue) of all the inventory results passed back from the shop keeper (service). The trades person now needs to process that list and decide if they want to just make a note of items that aren’t available, ring another supplier, make more enquires, etc. For the purpose of this exercise we will just make a permanent store (table) of results and end the conversation.


CREATE PROCEDURE CustomerLogging
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @ReceivedDlgHandle UNIQUEIDENTIFIER;
  DECLARE @ReceivedReplyMessage XML;
  DECLARE @ReceivedReplyMessageType SYSNAME;
  WHILE (1=1)
    BEGIN
      BEGIN TRANSACTION
      WAITFOR -- Pop the queue to get the next message for processing
       (
        RECEIVE TOP(1)
          @ReceivedDlgHandle = conversation_handle,
          @ReceivedReplyMessage = message_body,
          @ReceivedReplyMessageType = message_type_name
         FROM CustomersResponseToAskList
        ),TIMEOUT5000;
        IF (@@ROWCOUNT= 0)
          BEGIN
            ROLLBACK
            BREAK
          END
        IF @ReceivedReplyMessageType =
                  N'//HardwareStore/ReplyMessage'
          BEGIN
            INSERT INTO ExecutionLog( ExecutionRequest,
                                     ExecutionResult,
                                     MessageBody )
             SELECT @ReceivedReplyMessage.query('/ExecutionComplete/sqlobject'),
                    @ReceivedReplyMessage.query('/ExecutionComplete/WorkerReport'),
                    @ReceivedReplyMessage
            END CONVERSATION @ReceivedDlgHandle
          END
        -- TODO: add some unexpected behaviour code here.
        COMMIT
    END
END

Press Play

Now that we have everything in place and the scene is set we should get on and start processing some requests. The following script will execute the tasks involved in the life cycle of a request and return results. The request is represented by a xml document, which describes a stored procedure call to "uspInventoryCounts" and some parameters for that SP. The worker stored procedure we declared earlier knows how to shred this XML and execute the request.

For production systems you should consider using XML schemas to validate your request XML form.


-- Define customer request
DECLARE @RequestMessage XML
SELECT      @RequestMessage =
      CAST('<sqlobject>
           <sqlcommand commandText="uspInventoryCounts" commandtype="storeprocedure">
           <sqlparametercollection>
                   <sqlparameter parametername="@tableName" dbtype="sysname" direction="input" value="sys.objects"/>' +
                   '</sqlparametercollection>
             </sqlcommand>
             </sqlobject>'AS XML)
-- Make Customer request
EXEC CustomerRequest @RequestMessage
-- If run immediately after the request is made you will be able
-- to see your request on the shopkeeper queue just before it gets popped.
SELECT  *
FROM    CustomerRequestList
-- Check for problems and see if we have a response.
SELECT  *
FROM    ExecutionErrors
SELECT  *
FROM    CustomersResponseToAskList
-- Pop the response from the customer queue and persist the results in the log.
EXEC CustomerLogging
-- View the log
SELECT  *
FROM    ExecutionLog

You should see the following results after execution:

Execution Results from Service Broker processing

The first rowset is empty because the request was popped from the customer request queue before we could query the queue and see the request. The second rowset is also empty because no errors were generated. The third rowset shows the response message from the shopkeeper and the fourth rowset shows the customers log of the responses he/she got when asking the shopkeeper about inventory counts.

Multi Requests

If we got this far and one request has processed successfully, then it is time to build some confidence in our system and create many concurrent requests.

To accomplish this I have adapted the previous single customer request script to repeat its request behaviour a set number of times, in this case 10 times and return the same rowsets as before. I have iterated the master.sys.all_views object to retrieve system view names as parameters for "uspInventoryCounts".

-- Define customer request and make many requests about inventory counts
DECLARE @RequestMessage XML
DECLARE @i INT= 0
WHILE @i < 10
BEGIN
  SET @i = @i + 1
  SELECT      @RequestMessage =
       CAST('<sqlobject>
                   <sqlcommand commandText="uspInventoryCounts" commandtype="storeprocedure">
                     <sqlparametercollection>
                         <sqlparameter parametername="@tableName" dbtype="sysname" direction="input" value="' +
                        (SELECT fullname FROM (
                             SELECT
                                   ROW_NUMBER() OVER (ORDER BY name)
                                          ASn,
                          SCHEMA_NAME(schema_id) +'.'+ name AS fullname
                         FROM master.sys.all_views
                         ) x
                      WHERE n = @i) +
                       '"/>' +
                       '</sqlparametercollection>
                       </sqlcommand>
                      </sqlobject>' AS XML)
   EXEC CustomerRequest @RequestMessage
END

If this code is run immediately after the request is made you will be able to see your requests on the shopkeeper queue just before it gets popped.


SELECT  *
FROM    CustomerRequestList
You can also check for problems and see if we have a response with this.
SELECT  *
FROM    ExecutionErrors
-- Check the shopkeepers responses. If empty, up the number of requests to make the system busy and you will see rows on the queue here
SELECT  *
FROM    CustomersResponseToAskList
-- Log the responses
EXEC CustomerLogging
-- View the log
SELECT  *
FROM    ExecutionLog

You should see the following results after execution:

Results from multiple Service Broker requests

You should see 11 logged customer requests/response records if you ran this script immediately after the single request script.

Finally{};

Hopefully my article and example code has given you some exposure to a rich feature available in today’s Microsoft database offering. If this document inspires some ideas or helps with the general understanding of queue’s and SOA then I have achieved what I set out to do. I know I have only brushed over some topics here and I apologise for that, but there was just too much to detail in one article.

Please check out the full code as there are some helpful files and comments not mentioned in the article. I leave you with two tips that will help you with your own service broker development.

  1. Put all calls in try/catch and log to an errors table for everything you do.
  2. Check the SQL server logs.

If bad things are happening to your queue’s or services this is where you’ll find out about it.

Resources:

ServiceBrokerAsync.rar
Total article views: 5701 | Views in the last 30 days: 14
 
Related Articles
FORUM

SQL server Service Broker -- Reading same Message by multiple Users in the queue

SQL server Service Broker -- Reading same Message by multiple Users in the queue

ARTICLE

An AlwaysOn Bug with Service Broker Transmission Queue

Paul Brewer talks about an AlwaysOn problem affecting the Service Broker Transmission Queue.

FORUM

Service Broker queue design

Setting message precedence for message processing

ARTICLE

Message Queues in Software

Steve Jones talks about Service Broker and messaging and how these techniques can help you build a m...

FORUM

Custom Message Queue or Service Broker

GOAL: Setup a basic messaging process that emails an laert/msg to a specified user via DB Mail when ...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones