SQL Server

Async Lifestyle: Manage Your Tasks With Service Broker

Roger Wolter

Prerelease info in this article is subject to change. 

At a Glance:

  • Using Service Broker for batch processing
  • Database management on one or across many servers
  • Status and timer messages

Service Broker

SQL Server Agent

SQL Server 2005

Download the code for this article: ServiceBroker.exe (121KB)

Most of the articles written about SQL Server 2005 Service Broker talk about its capabilities as a messaging infrastructure and as a platform for building reliable database applications.

For this reason, most DBAs see Service Broker as a developer tool and yet another thing they have to configure, debug, and maintain. But the truth of the matter is that the asynchronous execution features of Service Broker can really make a DBA's life easier.

In this article I'll explain how to use Service Broker to implement a simple batch scheduling system that you might want to use to run tasks such as data warehouse maintenance and population or routine database maintenance. While I chose to implement a job execution system as an example, the concepts I'll discuss are useful for more limited task execution and scheduling as well. For example, you might use just the conversation timer part of the sample in order to implement periodic log backups or use activation to control parallelism in a data warehouse rebuild operation.

Of course, I'm not advocating the use of Service Broker to replace SQL Server™ Agent. In fact, the best use of the sample application in this article will probably be to run it as a job that is scheduled to be run by SQL Server Agent.

As I proceed, I'll assume you have a basic knowledge of the Service Broker. If not, before you continue you should read "A First Look at SQL Server 2005 Service Broker" or the introduction to Service Broker in SQL Server Books Online.

Service Broker Features

One of the most powerful features of Service Broker is the dialog, which represents a conversation between two end points. Messages in a dialog are guaranteed to be processed in the order in which they were put into the dialog, and any tasks will execute in the proper order as well. Because dialogs are persistent database objects, the order is preserved through any number of database restarts and transaction rollbacks.

Ordering is valuable for tasks that have dependencies or must be executed in a precise sequence. As each task completes, the next one starts just as soon as the transaction for the previous task commits. When a task fails, the execution logic can end the dialog so that the rest of the tasks don't continue with incomplete data. You shouldn't place tasks that aren't dependent on each other into the same dialog because dialogs are processed serially; unrelated tasks in the same dialog can't run in parallel.

Service Broker activation starts a configured stored procedure to process messages in a queue. If messages arrive in a queue faster than the stored procedure can process them, additional instances of the stored procedure are started until either enough stored procedures are started to keep up with the load or a configured maximum are started. If messages represent tasks to be executed, activation will ensure that they are run as they arrive in the queue and the limit on the maximum number of activated procedures limits the amount of parallelism available to the running tasks. By adjusting the number of activated procedures, you can control the size and number of resources dedicated to the tasks run by Service Broker.

A conversation timer is a simple mechanism that puts a message in a queue at a time of your choosing. Because a message arriving in a queue will initiate an activation procedure if one isn't already running, a timer expiring will reliably start a task. A Service Broker conversation timer is unique in that it is persistent and transactional. It will fire even if the database is restarted or down when the timer expires. Plus, if the task processing the timer message fails and rolls back, the timer will fire again.

Even though I started out by saying Service Broker was about more than just reliable messaging, Service Broker does include a high-performance, reliable transactional messaging system. If you use Service Broker for batch processing, you can spread the processing reliably over many systems by adding the routes required to make the Service Broker dialogs distributed. The persistent, reliable attributes of Service Broker messaging mean that the batch tasks will be reliably executed in spite of network and server failures.

Batch Processing

To demonstrate how DBAs can use the Service Broker features to perform batch processing tasks, I wrote a simple batch processing system. It is meant to be an example of how a batch system could be developed using Service Broker rather than a practical batch tool, but it contains some useful examples of what you can do with Service Broker. It probably doesn't make sense to put all this into a single application but it makes a better sample that way. Figure 1 shows how it works.

Figure 1 Batch Processing

Figure 1** Batch Processing **

A batch is a sequence of tasks, each of which is a SQL command. A Service Broker dialog conversation is created for each batch and the tasks in that batch are sent, in order, to the target service of the conversation. The queue for the target service has an activation procedure to run the tasks that is throttled by a MAX_QUEUE_READERS option. The result is that batches are processed independently, but with a controlled amount of resources allocated to them.

At this point, you should download the sample code that accompanies this article to make it easier to follow what's happening.

To simplify putting tasks in a queue for execution, the sample includes a table that holds information about the jobs to execute and a stored procedure that reads the rows in the table and puts an entry in the task queue for each row in the task table. The task table looks like Figure 2.

Figure 2 Task Table

CREATE TABLE TaskTable (
    BatchNumber bigint ,
    TaskNumber bigint  primary key(BatchNumber, TaskNumber),
    Command nvarchar(2000), 
    NoTransaction    int,
    StartTime    datetime null,
    CycleTime bigint null,
    ConversationHandle uniqueidentifier null,
    ProcessStatus    int null,
    EndTime    datetime null,
    ErrorXML XML null )

The batch and task numbers are used to define ordering and dependencies. Tasks with the same batch number are executed in the same Service Broker dialog in task number order. Tasks with no dependencies or ordering requirements are placed in separate batches. The command column contains the task to execute. The tasks are executed by passing the contents of the command field to the TSQL EXEC command. In this sample, the commands are fixed strings that must contain all required parameters. The NoTransaction column handles backup commands and any other SQL commands that can't run in a transaction. StartTime, CycleTime, and ConversationHandle are used for task scheduling and will be discussed later. The last three columns hold information about the last time the task ran.

The QueueTasksProc stored procedure reads the TaskTable and puts a message in the TaskQueue for each row in the table. The query shown in Figure 3 is used to read the TaskTable.

Figure 3 Reading TaskTable

(SELECT BatchNumber,TaskNumber,Command, NoTransaction 
    SELECT BatchNumber,TaskNumber,StartTime, CycleTime, ConversationHandle,
    FROM TaskTable Task WHERE Task.BatchNumber = A.BatchNumber 
    AND Task.TaskNumber = A.TaskNumber FOR XML AUTO, ELEMENTS, TYPE)
FROM TaskTable A ORDER BY BatchNumber,TaskNumber

Notice the FOR XML subquery that creates an XML document for the message body. Service Broker messages are often sent as XML because the XML features in SQL Server 2005 make it easy to create and parse XML documents.

The rest of the stored procedure reads through the resultset of this query and sends a message to the TaskQueue for each row. Whenever the BatchNumber changes, a new dialog is started so that each batch ends up in its own dialog.

If a row contains a StartTime value, instead of queuing the job immediately, a conversation timer is started with a timeout equal to the number of seconds until the StartTime value. The dialog handle for the conversation timer is put into the TaskTable so that when the timer fires, the dialog handle can be used to find out which task corresponds to the timer that expired.

Process Tasks

ProcessTasksProc is the procedure that runs to process messages in the TaskQueue. The core of this procedure is a standard Service Broker receive loop that receives messages one at a time and processes them based on the message type. The EndConversation and Error message types are handled by standard Service Broker routines. The TaskRequest message type starts SQL tasks. The tasks are started in a try/catch block so that you can capture any error messages for logging. The task execution block looks like this:

BEGIN TRY
   EXEC (@Command)
END TRY
BEGIN CATCH
   SELECT @ErrorNumber = error_number(), 
          @ErrorText = error_message()
END CATCH

The error_number and error_message are captured and used to build a response message which is sent back on the same conversation that the task arrived on (see Figure 4). Note the use of the XQuery modify statement to build the XML message body.

Figure 4 Capture Error Info

Set @ResponseDoc = N'<TaskStatus/>'
SET @ResponseDoc.modify(
'insert (<BatchNumber>{ sql:variable("@BatchNumber") }</BatchNumber>, 
    <TaskNumber>{ sql:variable("@TaskNumber") }</TaskNumber>,
    <ErrorNumber>{ sql:variable("@ErrorNumber") }</ErrorNumber>,
    <ErrorText>{ sql:variable("@ErrorText") }</ErrorText>,
    <Time>{ sql:variable("@Time") }</Time>
    ) as last into (/TaskStatus)[1]'); 

SEND ON CONVERSATION @dialog 
    MESSAGE TYPE [TaskStatusResponse] (@ResponseDoc)

If the message type is EndBatch, then I'm finished with this dialog and can safely end it, as shown here:

IF (@message_type_name = 'EndBatch')  
BEGIN
    END CONVERSATION @dialog
END    

The ProcessTaskProc is registered as an activation procedure for the TaskQueue:

ALTER QUEUE [TaskQueue]
    WITH ACTIVATION (
        STATUS = ON,
        PROCEDURE_NAME = ProcessTasksProc,
        MAX_QUEUE_READERS = 4,
        EXECUTE AS SELF);

Notice that MAX_QUEUE_READERS is set to 4. This means that a maximum of four tasks will be executed in parallel. Adjust this parameter to control the number of SQL Server resources allocated to batch processing. The sample code includes a stored procedure called SetActivatedProcs that can be used to change this parameter.

Processing Responses

Response messages are sent back to the TaskStatusQueue when a task completes. The response message contains the task completion time and the error status. This response message isn't strictly necessary. A simpler implementation would be to have the ProcessTaskProc log the results of the task and not return a response. I included the response message in the sample to illustrate how you could use it to collect all responses from a distributed batch system in a central queue for logging and analysis. In a truly distributed system, the response message would have to include the location where the task executed and more detailed statistics, but the sample just collects a few simple data values.

When a response message is received by the ProcessTaskStatusProc, the procedure parses the XML in the message body and updates the appropriate row in the TaskTable, as you see in Figure 5.

Figure 5 Update Row from Message

UPDATE [TaskTable] SET ProcessStatus =
  @message_body.value('(/TaskStatus/ErrorNumber)[1]', 'bigint'),
  EndTime =
  @message_body.value('(/TaskStatus/Time/text())[1]','nvarchar(100)'),
  ErrorXML = @message_body
  WHERE TaskNumber = 
  @message_body.value('(/TaskStatus/TaskNumber/text())[1]',  'bigint')
  AND    BatchNumber =
  @message_body.value('(/TaskStatus/BatchNumber/text())[1]', 'bigint')

Processing Timer Messages

To simplify the sample, the conversation timers use the same queue as the status messages so the timer messages are processed by the ProcessTaskStatusProc also. The ProcessTaskStatusProc handles timer messages by looking up the conversation handle that the message arrived on in the TaskTable and queuing a task in the TaskQueue. This part of the logic is the same as the QueueTasksProc logic for queuing tasks.

If the task has a cycle time specified, another conversation timer is started to kick off the next repetition after the cycle time. Cycle time is used for periodic jobs like log backups. Since the query reads CycleTime from the TaskTable for every repetition, you can update the table and change the period on the fly. If no cycle time is specified, the batch is terminated, as shown in Figure 6.

Figure 6 Using Cycle Time

SELECT @BatchNumber = BatchNumber,@TaskNumber = TaskNumber,
   @CycleTime = CycleTime, @TaskMessage = (SELECT
   BatchNumber,TaskNumber,Command, NoTransaction
   FROM TaskTable Task WHERE Task.BatchNumber = A.BatchNumber AND 
   Task.TaskNumber = A.TaskNumber FOR XML AUTO, ELEMENTS, TYPE) 
   FROM TaskTable A WHERE A.ConversationHandle = @dialog;
-- Queue a message to start the job
SEND ON CONVERSATION @dialog 
    MESSAGE TYPE [TaskRequest] (@TaskMessage);
if (@CycleTime is not null)
    BEGIN
        -- Start a timer for the next cycle
        BEGIN CONVERSATION TIMER ( @dialog ) TIMEOUT = @CycleTime;
    END
    ELSE BEGIN
        SEND ON CONVERSATION @dialog 
            MESSAGE TYPE [EndBatch] (' ')
    END

Building and Testing the Sample

Execute the BatchProcessingSample.sql script using your favorite query execution tool: Management Studio or sqlcmd. This will create a TaskTable with a few sample tasks, the Service Broker metadata, and the following stored procedures, which work as their names imply: QueueTasksProc, ProcessTasksProc, ProcessTaskStatusProc, SetActivatedProcs, CleanupTaskQueue, and CleanupStatusQueue.

If everything is created successfully, you can queue up the test tasks and run the sample with:

EXEC QueueTasksProc

Give the tasks a couple of minutes to complete, then run the following query to see the results:

SELECT * from TaskTable

As you can see, the sample batch application isn't a fully featured system but it should give you enough of an introduction to how Service Broker can be used to control asynchronous jobs to get you started on your own projects. If you want to know how to get this functionality on more than one server, read the sidebar "Managing Tasks on Multiple Servers."

Managing Tasks on Multiple Servers

If you decide to use the Service Broker for batch task management, you will soon want to manage tasks on all the SQL Server installations on your network. The transparent distributed nature of Service Broker dialogs make this pretty straightforward to do. You will have to make some configuration changes for the dialogs to work remotely and change the QueueTasksProc to be aware of multiple TaskRequestServices, but the task execution and status parts of the job shouldn't have to change.

To enable a dialog between two SQL Server instances you will have to create Service Broker endpoints in both instances using the CREATE ENDPOINT command. You will also have to create routes in the databases that the dialog connects using the CREATE ROUTE command. For example, if the TaskRequestService lives on a server called WORKER and the TaskStatusResponseService lives on a server called MASTER, then the TasksDB on MASTER will need a route for the TaskRequestService on WORKER and the TasksDB on WORKER will need a route for the TaskStatusResponseService on MASTER. Be sure that these routes include the broker_identifier because you will need to use it to specify which of the many TaskRequestServices you want to send a task to.

In order to make the QueueTasksProc handle multiple remote task services, you can add a broker_instance column to the tasks table and then modify QueueTasksProc to include the broker_instance as part of the TO SERVICE in the Begin Dialog for each batch. You may also want to add WITH ENCRYPTION = OFF to the BEGIN DIALOG if you don't need to encrypt the dialog messages.

Roger Wolter is a Program Manager on the Microsoft SQL Server team responsible for the SQL Server 2005 Service Broker. He has over 25 years of industry experience—the last 6 with Microsoft. He can be contacted at rwolter@microsoft.com.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.