Chapter 14. SQL Server Service Broker

IN THIS CHAPTER

• An Introduction to Asynchronous Messaging

• Understanding Service Broker

• Service Broker Operations and Troubleshooting

• Service Broker Security Overview

Scalability, which refers to a system’s ability to maintain the performance as the load increases, is one of the key characteristics of an enterprise application. The increased load can be due to a larger number of users, a larger number of transactions, more complex queries, or a larger volume of data. SQL Server 2005 introduces several new features to aid you in building scalable and distributed database applications. Service Broker is one such new feature that allows you to build scalable, reliable, secure, asynchronous, message-based, loosely coupled, distributed database applications.

Scalable application architectures often implement techniques such as performing parts of processing asynchronously, queuing the tasks for deferred or batch processing, and breaking tight coupling to distribute the load and to scale out. For instance, when an order record is inserted, the order fulfillment tasks can be queued, performed asynchronously, or performed on a different machine to increase the overall throughput of the order-accepting application and to shorten the interactive response time. Service Broker, a new component in the SQL Server 2005 database engine, provides the infrastructure and services for building such asynchronous, queued, distributed database applications. New DDL and DML statements have been introduced to create queues, send messages, receive messages, and so on. In other words, Service Broker acts as a framework and extensions to Transact-SQL (T-SQL) that you use to create message-based applications. SQL Server 2005 itself makes use of Service Broker to support various new features, such as Database Mail (to asynchronously send emails), Query Notification (to asynchronously inform the client about change in the data), Event Notification (to asynchronously send DDL and trace event notifications), and so on.

This chapter introduces Service Broker concepts and explores ways to configure, administer, monitor, and troubleshoot Service Broker applications.

An Introduction to Asynchronous Messaging

Asynchronous messaging is not a new concept. Developers often use message queuing products such as Microsoft Message Queuing (MSMQ) to post a message to a queue and defer parts of processing to create scalable applications.

Asynchronous messaging offers the following benefits:

• Improved performance and shortened interactive response time—Responsiveness and overall throughput of the system is improved because much of the work is now done asynchronously, so the user doesn’t have to wait for it to complete before receiving a response. Queuing and asynchronous messaging also allow developers to design an application to scale when the load increases.

• Load distribution—An application can send a message in a queue, which can be asynchronously received and processed by another application on a different machine to complete the rest of the processing.

• Batch or deferred processing—An application can send a message in a queue, which can be later received by an end-of-day batch process to complete the rest of the processing.

• Scale out—An application can send a message, which can be received and processed at different servers.

• Parallelism—Asynchronous messaging can also provide opportunities for increased parallelism. For instance, if you need to check inventory and verify a customer’s credit, you can post messages to two queues. The queue messages can be processed asynchronously and in parallel to perform both tasks and to improve overall response time.

• Loose coupling—Messaging can be used to integrate applications. For instance, one application can post a message to a queue on which another application is listening. In addition, loose coupling (which involves breaking an application into sets of independent tasks) can also simplify deployment and upgrade scenarios.

At first it might seem simple to build your own asynchronous and queued messaging solution: Just post a message in a queue and then receive and parse the message to determine what processing needs to be done. However, it is not as simple as it seems. Asynchronous messaging frameworks such as MSMQ provide a lot of services to application developers, enabling them to focus on solving business problems. Messaging frameworks such as MSMQ have to guarantee the message delivery, ensure the message integrity, make sure that each message is received only once and received in the order sent, manage message routing and security, and handle situations where there are multiple queues and multiple readers or a single queue and multiple readers, and so on.

Asynchronous Messaging in a Database

Having an asynchronous messaging platform built into the database system offers following benefits:

• Integrated management, deployment, and operations—You do not have to worry about deploying, configuring, and administering two separate systems for data and messages. With SQL Server 2005, administration of messaging applications is part of the routine administration of the database. The high-availability solutions (such as clustering) protect the database and messages from failures. Familiar T-SQL and XQuery syntax can be used to query and find out things like total number of messages in a queue, the message text, what messages have not been delivered yet, and so on. Familiar trace events (from Profiler) can be used to monitor and debug the messages. The same connection can be used to manipulate data and to send/receive messages.

• Unified programming model—The same T-SQL language is used for data access and messaging. Database programmers find it convenient to use the same language (T-SQL) and tools to access data and to implement messaging.

• Transactional distributed processing without the need for a complex two-phase commit—Asynchronous messaging within a database can use message acknowledgements to provide reliable and transactional messaging without distributed (that is, two-phase commit) transactions.

• Performance advantages—By avoiding the use of external messaging system, which includes avoiding the two-phase commit requirement and optimizing ininstance messaging (the sender and receiver database are in the same instance), developers can get a significant performance advantage.

The asynchronous reliable messaging functionality is now built in to the SQL Server 2005 database engine in the form of Service Broker. You can use Service Broker technology in applications to send messages within the same database, between different databases on the same instance, between different instances on the same machine, or to a different server. Note that Service Broker is not based on MSMQ, and there are differences between the two technologies. Service Broker is compared with other messaging platforms later in this chapter, in the section “Service Broker and Other Messaging Technologies.”

Understanding Service Broker

Service Broker helps developers to create database applications that can scale up and scale out. Developers can write a T-SQL script that creates Service Broker message types, queues, services, and stored procedures to be activated; begins and ends dialog conversations; sends and receives messages; and configures Service Broker networking (routing) and security settings. Your job as a DBA is to review the scripts; configure required security principals, certificates, endpoints, and so on; and execute the scripts to install the Service Broker application. Once the Service Broker application is installed, most administrative tasks are part of the normal administration for the database. As discussed later in this chapter, SQL Server provides several catalog views, dynamic management views, Profiler trace events, and Performance Monitor counters that you can use to maintain and optimize Service Broker applications.

The following sections explain the concepts by providing a glossary of Service Broker terms followed by T-SQL syntax for performing some common Service Broker tasks. Finally, you’ll see a few scenarios where Service Broker can be used.

Service Broker Terminology

Service Broker applications consist of Service Broker objects such as messages, queues, services, and so on, and stored procedures and applications that use those objects. Messages, dialog conversations, and conversation groups are the basis of the conversation architecture for Service Broker. Message types, contracts, services, and queues are the basis of the service architecture for Service Broker. Routes, endpoints, and remote service bindings define the networking and security architecture for Service Broker. The following sections explain all these terms and how they play a role in the Service Broker application.

Messages

Messages are the information exchanged between applications that use Service Broker. Messages refer to blocks of data moved around a Service Broker application. Each message is a part of a conversation. Each message has a unique identity, as well as a sequence number within the conversation. The SEND T-SQL statement is used to send a message on an existing queue, and the RECEIVE T-SQL statement is used to retrieve one or more messages from a queue.

Service Broker uses two distinct categories of message:

• Sequenced message—A sequenced message is a message that must be delivered to an application exactly once, in order. All user-defined message types, end dialog messages, and error messages created by an application are sequenced messages.

• Unsequenced message—An unsequenced message is a message that can be processed immediately, regardless of the sequence in which the it arrives. Service Broker uses unsequenced messages for dedicated acknowledgement messages and error messages created by Service Broker.

Message Types

Message types define the content of messages. In other words, a message type is a definition of the format of a message. The CREATE MESSAGE TYPE DDL statement can be used to create a message type. A message type is stored in SQL Server, and it states what a message looks like.

The participants in a Service Broker conversation must agree on the name and content of each message. A message type object defines a name for a message type and defines the type of data that the message contains. If multiple databases participate in a Service Broker conversation, an identical message type has to be created in each database. Message type is used to validate incoming messages. If an incoming message does not conform to the message type specified for that conversation, Service Broker discards the invalid message and returns an error message to the service that sent the message.

Queues

Queues are database objects used for storing messages. These named database objects hold the messages in the order in which they were received while the messages await the processing. Like a table, a queue contains rows. Each Service Broker message is a row in a queue. Each queue row contains information such as message type, sequence number, conversation, service, validation, and contract details.

The CREATE QUEUE DDL statement can be used to create a queue. The SEND and RECEIVE statements (and not the INSERT, UPDATE, DELETE, or TRUNCATE statements) are used to manipulate queues. While creating a queue, you can associate a stored procedure by using the ACTIVATION clause. SQL Server runs, or activates, this stored procedure when there are messages in the queue to be processed. To catch up with the messages in the queue, Service Broker can activate multiple instances of the associated stored procedure. You use the MAX_QUEUE_READERS option specified with the CREATE QUEUE or ALTER QUEUE statement to specify the maximum number of stored procedure instances that Service Broker starts for this queue.

Services

A service is a named entity that is used to deliver messages to the correct queue within a database, to route messages, to enforce a contract for a conversation, and to determine the remote security for a new conversation. Services are endpoints for conversations.

You use the CREATE SERVICE DDL statement to create a new service. Each service is associated with a single queue. If you want a service to be a target in a dialog, you must specify one or more contracts for which this service may be a target.

Contracts

A contract, which is created by using the CREATE CONTRACT DDL statement, is an agreement that defines the message types used in a Service Broker conversation and also determines which side of the conversation can send messages of that type. Each Service Broker conversation follows a contract. In summary, a contract specifies the direction and type of messages in a given conversation.

Service Program

AA service program is normally a stored procedure—but can be an external program—that processes Service Broker messages. The stored procedure associated with a queue that uses the ACTIVATION clause is an example of a service program.

Dialog Conversation

A dialog conversation, or dialog, is a reliable, persistent stream of messages Abetween two services. It is a conversation between two services that guarantees exactly once-in-order delivery of messages.

A dialog conversation has two participants: an initiator that begins the conversation using the BEGIN DIALOG CONVERSATION T-SQL statement and ends the conversation using END CONVERSION statement; and a target that accepts a conversation begun by the initiator. A dialog incorporates automatic message receipt acknowledgement to ensure reliable delivery. Note that an instance that forwards a message does not acknowledge the message to the sender. Only the final destination acknowledges the message. If the sender does not receive an acknowledgement from the destination after a period of time, the sender retries the message.

Service Broker saves each outgoing message in the transmission queue until the message is acknowledged by the remote service. In cases where both sides of the conversation are in the same instance, Service Broker may optimize message delivery by placing the message directly on the destination queue. Where possible, acknowledgement messages are included as part of return messages for the dialog.

Service Broker never ends a dialog automatically. Applications are responsible for indicating when they are done with a dialog by explicitly ending the dialog. The dialog remains in the database until an application explicitly ends the conversation by calling END CONVERSATION.

Messages exchanged with services on different SQL Server instances are by default encrypted. Messages exchanged with services in the same SQL Server instance are never encrypted.

Conversation Groups

Service Broker internally creates a conversation group to group together related conversations and to provide exactly once-in-order access to messages that are related to a specific business task. SQL Server manages the lifetime of the conversation group. Each time an application sends or receives a message, SQL Server locks the conversation group, preventing another program from updating the same state data at the same time. Application developers can use conversation groups for state management and to orchestrate conversations. The sys.conversation_groups system catalog view can be used to obtain a list of active conversation groups.

Routes

A route specifies the location of the Service Broker service and the database that contains the service. Service Broker uses routes to deliver messages. By default, each database contains a route named AutoCreatedLocal which specifies that services with no other route defined are delivered within the current instance. Routes provide an abstraction over instances of SQL Server used in Service Broker communication, so that instances can be moved without changing any of the service programs. The basic components of a route are the service name, a broker instance identifier, and a network address.

When a dialog conversation is started using BEGIN DIALOG CONVERSATION, SQL Server uses the specified service name and broker instance identifier to determine the route for the conversation. After the target acknowledges the first message, all subsequent messages on that conversation are routed to the same database.

You can use the sys.routes catalog view to view a list of routes present in the current database. You can use the CREATE ROUTE DDL statement to create a new Service Broker route.

Service Broker message forwarding tracks the number of times a message has been forwarded to protect against endless routing loops.

In summary, a route maps a service name to a physical network address. It is possible to define multiple routes with the same service name for load balancing.

Service Broker Endpoints

As explained in Chapter 7, “SQL Server 2005 Security,” an endpoint can be thought of as an entry point into a SQL Server instance. Service Broker communication outside the SQL Server instance requires a Service Broker endpoint to be created by using the CREATE ENDPOINT T-SQL statement. By default, an instance of SQL Server does not contain a Service Broker endpoint.

TCP is the only allowed protocol for Service Broker. In order for two SQL Server instances to exchange Service Broker messages, each instance must be able to send TCP/IP traffic to the port that the other instance uses for Service Broker communication. By convention, Service Broker uses port 4022 for broker-to-broker communication. You can specify a different port by using the LISTENER_PORT clause with the CREATE ENDPOINT statement. A Service Broker endpoint listens on a specific TCP port number and provides options for transport security and message forwarding. You can use the sys.service_broker_endpoints catalog view to see a list of Service Broker endpoints.

Remote Service Binding

Service Broker dialog security is based on certificates. A certificate is used to verify the identity of a remote database and to identify the local database principal for the operation. In other words, a certificate is used to establish the credentials of a remote database and then map operations from the remote database to a local user. The permissions for the local user apply to any operation on behalf of the remote service. The certificate is shared between databases. No other information for the user is shared. A remote service binding, which you can create by using the CREATE REMOTE SERVICE BINDING T-SQL statement, establishes a relationship between a local database user, the certificate for the user, and the name of a remote service. Service Broker uses the remote service binding to provide dialog security for conversations that target the remote service. A remote service binding is necessary only for initiating services that communicate with target services outside the SQL Server instance.

Poison Messages

If a service program such as an activated stored procedure rolls back the transaction that contains a RECEIVE statement because it cannot successfully process a message, that message is called a poison message. For instance, let’s say that an application sends a message to withdraw a part from inventory in response to an order entry action. If the order is changed while the inventory message is being processed, the service program cannot successfully process the inventory change message because the new order does not contain the original part information. With these poison messages, the service program has to roll back the transaction because it cannot successfully process the message. If this happens five times, Service Broker disables all the queues from which the transaction received messages and raises the Broker:Queue Disabled trace event. Application developers can programmatically detect and handle poison messages, and administrators can create alerts on the Broker:Queue Disabled trace event.

Activation

Service Broker applications can optionally leverage a technique called activation that activates or starts a stored procedure (internal activation) or produces a SQL Server event that an external application can respond to (external activation), whenever there is a message in a Service Broker queue. You can use the sys.dm_broker_activated_tasks dynamic management view to see a list of stored procedures activated by Service Broker.

Message Forwarding

Service Broker message forwarding allows an instance of SQL Server to accept messages from an outside instance and send those messages to a different instance. It can be used in scenarios such as to provide connectivity between servers in different trust domains, to simplify administration by creating a single centralized instance that holds the routing information for a domain, to distribute work among several instances, and so on. You can use the CREATE/ALTER ENDPOINT statement to configure Service Broker message forwarding.

Figure 14.1 shows the basic Service Broker architecture and is a pictorial representation of terms explained in this section.

Figure 14.1. The Service Broker architecture consists of a physical layer (messages traverse between queues over TCP/IP in binary format), a metadata layer (services, contracts, and message types), and an application layer (initiator application and target application; target may optionally use activation).

Common Service Broker Tasks

The following sections show the T-SQL syntax for performing some common Service Broker tasks.

Enabling and Disabling Service Broker

SQL Server provides a database-level property to specify whether a database is enabled for Service Broker communication. When you create a new database, by default Service Broker is enabled for the new database. The is_broker_enabled flag is set to value 1 in the sys.databases catalog view to indicate that a database can send and receive Service Broker messages.

The DISABLE_BROKER option with the ALTER DATABASE statement deactivates Service Broker, and the ENABLE_BROKER option activates Service Broker in the database. When Service Broker is not active in the database, messages remain in the transmission queue. The sys.transmission_queue catalog view contains a row for each message in the transmission queue. As soon the Service Broker is activated, messages are delivered to the database.

Here is an example of enabling and disabling Service Broker in a database and using the sys.databases catalog to verify whether Service Broker is active in a database:

USE [master];
GO
ALTER DATABASE [AdventureWorks] SET DISABLE_BROKER;
GO
SELECT is_broker_enabled FROM sys.databases
   WHERE name = 'AdventureWorks';
GO
ALTER DATABASE [AdventureWorks] SET ENABLE_BROKER;
GO
SELECT is_broker_enabled FROM sys.databases
   WHERE name = 'AdventureWorks';
GO

Creating a Service Broker Endpoint

As mentioned previously, Service Broker communication outside a SQL Server instance requires a Service Broker endpoint to be created, by using the CREATE ENDPOINT T-SQL statement, as in this example:

USE [master];
GO
CREATE ENDPOINT testEndpoint
   STATE = STARTED
   AS TCP (LISTENER_PORT = 4022)
   FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS);
GO
SELECT * FROM sys.service_broker_endpoints;
GO
ALTER ENDPOINT testEndPoint
   FOR SERVICE_BROKER (MESSAGE_FORWARDING = ENABLED);
GO
SELECT * FROM sys.service_broker_endpoints;
GO
DROP ENDPOINT testEndPoint;
GO

These statements create a Service Broker endpoint, use the sys.service_broker_endpoints catalog view to see metadata of the endpoint just created, enable message forwarding on the endpoint by using ALTER ENDPOINT, and finally drop the endpoint, preventing messages from arriving in the instance.

Creating a Message Type

You can use the CREATE MESSAGE TYPE DDL statement to create a Service Broker object that defines the format of messages. Here’s an example:

USE [AdventureWorks];
GO
CREATE MESSAGE TYPE SBSampleMessageType
   VALIDATION = WELL_FORMED_XML;
GO
SELECT * FROM sys.service_message_types
   WHERE name = 'SBSampleMessageType';
GO
DROP MESSAGE TYPE SBSampleMessageType;
GO

These statements create a message type indicating that messages can contain well-formed XML documents.

Creating a Contract

You can use the CREATE CONTRACT DDL statement to create a Service Broker object that defines the direction and type of message in a Service Broker conversation. Here’s an example:

USE [AdventureWorks];
GO
CREATE MESSAGE TYPE SBSampleMessageType
   VALIDATION = WELL_FORMED_XML;
GO
CREATE CONTRACT SBSampleContract
   (SBSampleMessageType SENT BY ANY);
GO
SELECT * FROM sys.service_contracts
   WHERE name = 'SBSampleContract';
GO
DROP CONTRACT SBSampleContract;
GO
DROP MESSAGE TYPE SBSampleMessageType;
GO

These T-SQL statements create a contract specifying that conversations can send well-formed XML documents and that both the initiator and target can send the message.

Creating a Queue

You can use the CREATE QUEUE DDL statement to create a new queue in the database to store Service Broker messages. While creating a queue, you can specify a name of a stored procedure that will be activated whenever there is a message in this queue. Here’s an example:

USE [AdventureWorks];
GO
CREATE QUEUE dbo.SBSampleQueue
   WITH STATUS = ON,
        RETENTION = OFF,
   ACTIVATION (
      PROCEDURE_NAME = AdventureWorks.dbo.sproc_SBLengthyProcessing,
      MAX_QUEUE_READERS = 5,

      EXECUTE AS OWNER)
   ON [DEFAULT];
GO
SELECT * FROM sys.service_queues;
GO
DROP QUEUE dbo.SBSampleQueue;
GO

These statements create a queue and specify a stored procedure to be activated to process the messages in the queue. The CREATE QUEUE statement fails if the specified stored procedure does not exist.

Creating a Service

You can use the CREATE SERVICE DDL statement to create a service. Here’s an example:

USE [AdventureWorks];
GO
CREATE SERVICE
   SBSampleService ON QUEUE SBSampleQueue
   (SBSampleContract);
GO
SELECT * FROM sys.services;
GO
DROP SERVICE SBSampleService;
GO

These statements create a service, associate it with a queue named SBSampleQueue, and specify that conversations must follow the agreement defined by the contract named SBSampleContract. Both the contract and queue must exist for the CREATE SERVICE statement to succeed.

Starting a Dialog Conversation

You can use the BEGIN DIALOG CONVERSATION statement to begin a dialog from one service to another service. Here’s an example:

DECLARE @dlgHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @dlgHandle
   FROM SERVICE SBSampleService
   TO SERVICE 'SBSampleService'
   ON CONTRACT SBSampleContract;

The dialog handle is then used to send the messages on this conversation. With BEGIN DIALOG CONVERSATION you can optionally specify the lifetime (in seconds) of the dialog, turn off encryption, and relate the dialog to an existing conversation or conversation group.

Sending and Receiving Messages

You use the SEND statement to post a message to a Service Broker queue. Here’s an example:

SEND ON CONVERSATION @dlgHandle
   MESSAGE TYPE SBSampleMessageType (@InsertedData);

You use the RECEIVE statement, whose syntax is similar to SELECT statement, to retrieve the message from the queue, as in this example:

RECEIVE TOP (1) * FROM dbo.SBSampleQueue;

The RECEIVE statement is commonly used with the WAITFOR statement, as shown here:

WAITFOR (RECEIVE CAST(message_body AS XML) AS msgText
   FROM dbo.SBSampleQueue
   INTO @SBMessage), TIMEOUT 60000;

This statement blocks a thread until a message is available in a queue or until 60 seconds has elapsed. If a message is already present in the queue, the statement returns immediately, copying the message into the specified table variable (in this case, @SBMessage).

Ending Dialog Conversation

You use the END CONVERSATION statement to end an active conversation. Here’s an example:

END CONVERSATION @dlgHandle;

Backing Up and Restoring Service Broker Applications

Because the Service Broker objects reside in the database in which the service runs, backing up the database takes care of the Service Broker components. The msdb database contains routes for incoming messages, and the master database contains the Service Broker endpoints and transport security configuration settings. These two system databases have to be backed up in case the Service Broker application communicates with other instances of SQL Server.

Service Broker Scenarios

Service Broker provides all the infrastructure and services required to reliably and securely queue and move the messages in a database, across databases, across instances, or across multiple servers. You can use some new T-SQL constructs to send and receive messages; everything else is handled by the Service Broker subsystem inside the SQL Server 2005 database engine.

SQL Server 2005 introduces new T-SQL DDL statements such as CREATE SERVICE and CREATE QUEUE and new DML statements such as BEGIN DIALOG CONVERSATION, SEND, RECEIVE, and END CONVERSATION that you can use to implement a Service Broker application. Any program that can run T-SQL statements can use Service Broker. A Service Broker application can be implemented as a program running outside SQL Server or as a stored procedure written in T-SQL or a .NET language.

The following sections look at a few typical scenarios where Service Broker can be used.

Asynchronous Triggers

Traditional triggers are executed synchronously. For instance, if a table has an associated INSERT trigger, then an INSERT operation on that table does not finish until the trigger completes. You can use Service Broker to change this. You can cause a trigger to simply post a message in a Service Broker queue and return. This message can be asynchronously received by a Service Broker application, such as a stored procedure, which can then asynchronously perform the tasks originally executed as part of the trigger.

Let’s look at a traditional, synchronous trigger. Then let’s use Service Broker to implement an asynchronous trigger. In the following example, when a row is inserted into a sample table, the trigger calls a stored procedure, which performs some lengthy processing:

SET NOCOUNT ON
GO

USE [AdventureWorks];
GO

IF OBJECT_ID('dbo.tblSBSample') IS NOT NULL
   DROP TABLE dbo.tblSBSample;
GO
CREATE TABLE dbo.tblSBSample
  (id INT NOT NULL IDENTITY(1,1) PRIMARY KEY);
GO

IF OBJECT_ID('dbo.sproc_LengthyProcessing') IS NOT NULL
   DROP PROCEDURE dbo.sproc_LengthyProcessing;
GO

CREATE PROCEDURE dbo.sproc_LengthyProcessing
AS
BEGIN
   SELECT GETDATE();
   DECLARE @var INT;
   SET @var = 1;

   WHILE @var < 9999999
   BEGIN

      SET @var = @var + 1;
   END
   SELECT GETDATE();
END
GO
IF OBJECT_ID('dbo.trgSBSampleSync') IS NOT NULL
   DROP TRIGGER dbo.trgSBSampleSync;
GO
CREATE TRIGGER dbo.trgSBSampleSync ON dbo.tblSBSample FOR INSERT AS
BEGIN
   PRINT 'Synchronous trigger started at:'
   EXEC sproc_LengthyProcessing;
   PRINT 'Synchronous trigger ended.'
END
GO

--Test the synchronous trigger by inserting a row
INSERT INTO dbo.tblSBSample DEFAULT VALUES;
GO

DROP TRIGGER dbo.trgSBSampleSync;
GO

This script (SynchTrigger.sql) creates a table, a stored procedure, and an INSERT trigger that simply invokes the stored procedure. An INSERT statement toward the end tests the trigger. You should run this script and notice how much time it takes to run the INSERT statement. When executed on a Pentium 4 3GHz machine, the INSERT statement (trigger) takes about 12 seconds to finish.

Let’s now implement an asynchronous trigger that will simply post a message to an existing Service Broker queue and return (which means the INSERT statement should return quickly):

SET NOCOUNT ON
GO

USE [AdventureWorks];
GO

IF OBJECT_ID('dbo.tblSBSample') IS NOT NULL
   DROP TABLE dbo.tblSBSample;
GO
CREATE TABLE dbo.tblSBSample
  (id INT NOT NULL IDENTITY(1,1) PRIMARY KEY);
GO

IF OBJECT_ID('dbo.tblSBLog') IS NOT NULL
   DROP TABLE dbo.tblSBLog;
GO
CREATE TABLE dbo.tblSBLog
  (LogText XML);
GO

IF OBJECT_ID('dbo.sproc_SBLengthyProcessing') IS NOT NULL
   DROP PROCEDURE dbo.sproc_SBLengthyProcessing;
GO
CREATE PROCEDURE dbo.sproc_SBLengthyProcessing
AS
BEGIN
   DECLARE @SBMessage TABLE (msgText XML);

   WAITFOR (RECEIVE CAST(message_body AS XML) AS msgText
      FROM dbo.SBSampleQueue
      INTO @SBMessage), TIMEOUT 60000;

   INSERT INTO dbo.tblSBLog
      SELECT * FROM @SBMessage WHERE msgText IS NOT NULL;

   DECLARE @var INT;
   SET @var = 1;

   WHILE @var < 9999999
   BEGIN
      SET @var = @var + 1;
   END
END
GO

This script begins by creating a sample table on which an INSERT trigger will be created, another table to hold some logging information, and a stored procedure. This stored procedure will be activated whenever there is a message in the Service Broker queue. The stored procedure performs the same processing as in the previous script (which shows a synchronous trigger), except that it retrieves the message from the queue and saves it into a log table at the beginning.

Let’s set up the Service Broker objects to send messages:

IF EXISTS(SELECT * FROM sys.services WHERE name = 'SBSampleService')
    DROP SERVICE SBSampleService;
GO

IF EXISTS(SELECT * FROM sys.service_contracts WHERE name = 'SBSampleContract')
    DROP CONTRACT SBSampleContract;
GO
IF EXISTS(SELECT * FROM sys.service_message_types
              WHERE name = 'SBSampleMessageType')
    DROP MESSAGE TYPE SBSampleMessageType;
GO
IF OBJECT_ID('dbo.SBSampleQueue') IS NOT NULL AND
   EXISTS(SELECT * FROM sys.service_queues WHERE name = 'SBSampleQueue')
  DROP QUEUE dbo.SBSampleQueue;
GO

CREATE MESSAGE TYPE SBSampleMessageType
   VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT SBSampleContract
   (SBSampleMessageType SENT BY ANY);
GO

CREATE QUEUE dbo.SBSampleQueue
   WITH STATUS = ON,
        RETENTION = OFF,
   ACTIVATION (
      PROCEDURE_NAME = AdventureWorks.dbo.sproc_SBLengthyProcessing,
      MAX_QUEUE_READERS = 5,
      EXECUTE AS OWNER)
   ON [DEFAULT];
GO
CREATE SERVICE
   SBSampleService ON QUEUE SBSampleQueue
   (SBSampleContract);
GO

These statements create a message type, a contract, a queue, and a service. The queue is associated with a stored procedure to be activated to process the messages.

Here is the trigger code that begins the dialog conversation, posts a message containing rows from an inserted virtual table as XML to the queue, and ends the conversation:

IF OBJECT_ID('dbo.trgSBSampleAsync') IS NOT NULL
   DROP TRIGGER dbo.trgSBSampleAsync;
GO
CREATE TRIGGER dbo.trgSBSampleAsync ON dbo.tblSBSample FOR INSERT AS
BEGIN
   PRINT 'Asynchronous trigger started at:'

SELECT GETDATE();

DECLARE @InsertedData XML

IF EXISTS(SELECT * FROM inserted)
BEGIN
   BEGIN TRANSACTION

   BEGIN TRY
     SELECT @InsertedData = (SELECT * FROM inserted FOR XML AUTO);

     DECLARE @dlgHandle UNIQUEIDENTIFIER

     BEGIN DIALOG CONVERSATION @dlgHandle
        FROM SERVICE SBSampleService
        TO SERVICE 'SBSampleService'
        ON CONTRACT SBSampleContract;

     SEND ON CONVERSATION @dlgHandle
        MESSAGE TYPE SBSampleMessageType (@InsertedData);

     --SELECT CAST(message_body as XML) FROM dbo.SBSampleQueue;

     SELECT * FROM sys.conversation_endpoints
        WHERE conversation_handle = @dlgHandle;

     IF EXISTS(SELECT * FROM sys.conversation_endpoints
        WHERE conversation_handle = @dlgHandle AND state = 'ER')
        BEGIN
           RAISERROR('Service Broker dialog in error state.', 18, 127);
        END
     ELSE
        BEGIN
           END CONVERSATION @dlgHandle;
           COMMIT TRAN;
        END
   END TRY
   BEGIN CATCH
      ROLLBACK TRANSACTION;
      DECLARE @error VARCHAR(max);
      SET @error = ERROR_MESSAGE();
      RAISERROR(@error, 18, 127);
   END CATCH
END

   PRINT 'Asynchronous trigger ended.'
   SELECT GETDATE()
END
GO

--Test the async trigger by inserting a row
INSERT INTO dbo.tblSBSample DEFAULT VALUES;
GO

The INSERT statement toward the end of this script tests the trigger. As soon as a message is posted to the queue, the stored procedure dbo.sproc_LengthyProcessing is activated, which retrieves the message from the queue, saves it into the log table dbo.tblSBLog, and does the lengthy processing as before. As you can see, this time the trigger—and hence the INSERT statement—returns immediately.

Parallel Processing

You can use Service Broker to improve overall response time and user experience by performing tasks in parallel. Consider a scenario where a user screen displays information that is retrieved from multiple databases. One option is to sequentially access each database, one after the other. This can significantly increase the response time. The other option is to post a message to a queue in each database and, as response becomes available, fill the screen with the information retrieved from the database.

Batch Processing

Applications can use Service Broker to take advantage of queuing, reliable messaging, and parallel processing to perform large-scale batch processing. The application stores information about data to be processed in a Service Broker queue, and then a program either periodically or off-hours reads from the queue and processes the data. Service Broker can also be used to offload batch processing to a computer other than the computer from which the request originates.

Increasing Availability by Distributing Server-Side Processing

Consider a scenario where order entry and order fulfillment are performed on two different computers. When an order is entered, a message is posted to a queue, which is routed to the order fulfillment computer. With this distributed OLTP system architecture, even if the order fulfillment computer is offline, the application can continue to accept orders. The messages will be held in the transmission queue until the order fulfillment server becomes available. In addition to increasing availability, this architecture facilitates scaling out, load distribution, and easy management and upgrading.

Data Collection

Service Broker security, reliability, and asynchronous messaging can be leveraged to implement applications that collect data from a large set of sources. For instance, a sales application with multiple sales offices can use Service Broker to send transaction information to a central data store.

Service Broker and Other Messaging Technologies

The following sections compare Service Broker with current messaging technologies, including MSMQ and BizTalk Server, and a future messaging technology code-named Indigo.

MSMQ

You can use MSMQ to create high-performance applications by leveraging MSMQ’s guaranteed message delivery, efficient routing, security, and priority-based asynchronous and synchronous messaging.

Service Broker is built in to the SQL Server 2005 database engine and does not involve any additional installation requirements, whereas MSMQ has to be separately installed. Service Broker is not just for messaging; rather, it is a platform for building asynchronous database applications.

MSMQ supports a variety of messaging styles; Service Broker supports only transactional messaging. MSMQ supports messaging between machines running Windows, and Service Broker supports messaging between machines running SQL Server. With Service Broker, the initiator and target recipient of the message have to both be SQL Server. MSMQ offers an HTTP SOAP transport variant and can interoperate with other messaging products, such as IBM WebSphere MQ, but Service Broker works only on a proprietary binary protocol between SQL Server instances.

BizTalk Server

If an application requires transferring XML documents or binary messages between SQL Server instances, it can use Service Broker. However, if it has additional requirements, such as orchestrating workflow, managing state, converting message types, changing the message body; if it works on a variety of transports; or if it needs to integrate with other data sources, such as Exchange Server or Web services, the better option in such situations would be to use BizTalk Server.

Windows Communication Foundation

Windows Communication Foundation is the next-generation platform for building distributed application based on a service-oriented architecture. In addition to queued messaging, it supports distributed transactions, web services interoperability, .NET-to-.NET communication, and more. It is an extension to .NET Framework 2.0 and is scheduled for release in 2006. Unlike Service Broker, Windows Communication Foundation does not provide built-in asynchronous connectivity of SQL Server applications. However, Service Broker can be used to implement a custom channel to provide reliable messaging.

Service Broker Operations and Troubleshooting

This section provides a list of system catalog views, dynamic management views, Profiler trace events, and Performance Monitor counters that you can use to monitor and tune a Service Broker application.

Table 15.1 lists Service Broker catalog views.

Table 15.1. Service Broker Catalog Views

Table 15.2 lists dynamic management views (DMVs) that are useful for monitoring Service Broker activity.

Table 15.2. Service Broker DMVs

Table 15.3 lists Profiler trace events that are useful for monitoring Service Broker activity.

Table 15.3. Service Broker Profiler Trace Events

Table 15.4 lists Performance Monitor counters that are useful for monitoring Service Broker activity. For a default instance, the Performance Monitor object name begins with SQLServer:, and for a named instance, the Performance Monitor object name begins with MSSQL$<instance_name>:.

Table 15.4. Service Broker Performance Monitor Counters

You can use the Object Explorer in SQL Server Management Studio to view message types, contracts, queues, services, routes, and remote service binding details. This information is available under the Service Broker folder for each database in the Object Explorer tree.

Troubleshooting Tips

You can use the catalog views and DMVs listed in Tables 15.1 and 15.2 to verify Service Broker configuration and execution. SQL Server writes Service Broker log and error messages to SQL Server error log files and the Windows event log. You can look in error log files and the event viewer for any errors that are interfering with Service Broker communication. For instance, if the database master key is missing, SQL Server logs the following message to the SQL Server error log:

Service Broker needs to access the master key in the database 'AdventureWorks'.
    Error code:25. The master key has to exist and the service master key
    encryption is required.

Here are some additional techniques you can use for troubleshooting Service Broker applications:

• You can debug an activated stored procedure by writing PRINT statements in it. Service Broker writes the output of the PRINT statement into the SQL Server error log file (ERRORLOG).

• If a stored procedure is not activated, you can use the sys.service_queues catalog view to review fields such as is_activation_enabled, activation_procedure, is_receive_enabled, execute_as_principal_id, and so on. You need to confirm that the security principal has EXECUTE permission on the stored procedure. You can review the SQL Server error log for any additional information.

• If messages remain in the transmission queue (sys.transmission_queue), you can use the is_broker_enabled column of the sys.databases catalog view to check whether Service Broker is enabled for the database. You should also check the transmission_status column in the sys.transmission_queue catalog view to see error text that describes the last error that occurred while trying to deliver messages for a specific dialog. If messages are exchanged across instances, you need to ensure that Service Broker is enabled in the msdb system database and that routes are configured correctly in this database.

Service Broker Security Overview

Service Broker messages may carry valuable business information. Therefore, it is important to ensure that their integrity is preserved, that messages are received from authenticated services, and that messages are sent to designated services. The Service Broker infrastructure ensures that only authorized databases send and receive the messages and that the message integrity is preserved.

Service Broker provides security at two levels:

• Dialog—Dialog security encrypts the messages, verifies the identities of participants, provides remote authorization, and performs message integrity checking. Therefore, dialog security helps protect data against inspection or modification in transit. Service Broker provides two types of dialog security: full security and anonymous security. Full security prevents the initiating service from sending messages to an untrusted database and protects the target service from receiving messages from an untrusted database. Service Broker encrypts messages transmitted over the network when the conversation uses full security. Anonymous security protects the initiating service against sending messages to an untrusted database. Service Broker encrypts messages transmitted over the network when the conversation uses anonymous security. Anonymous security identifies the target service to the initiating service, but it does not identify the initiating service to the target service. Full security requires the initiator to have a certificate and remote service binding with ANONYMOUS = OFF.

• Transport—Transport security prevents unauthorized databases from sending Service Broker messages to databases in the local instance. When transport security is enabled, authorization is required to connect to the Service Broker endpoint. Whether the network connection uses transport security depends on the AUTHENTICATION option for the broker endpoint and whether both databases contain a certificate for master.dbo.

Applications that send messages between SQL Server instances may use transport security, dialog security, or both. By default, all dialog conversations use dialog security. When you begin a dialog, you can explicitly allow the dialog to proceed without dialog security by including the ENCRYPTION = OFF clause on the BEGIN DIALOG CONVERSATION statement. However, if a remote service binding exists for the service that the conversation targets, the dialog uses security even when ENCRYPTION = OFF. For a dialog that uses security, Service Broker encrypts all messages sent outside a SQL Server instance. Messages that remain within a SQL Server instance are never encrypted.

Service Broker remote security, where more than one SQL Server instance participates in the dialog, is based on certificates. SQL Server uses certificates to verify the identity of a remote database and to identify the local database principal for the operation. You can create certificates by using the CREATE CERTIFICATE T-SQL statement. Service Broker uses the remote service bindings in the database that begins the conversation to determine the security for the conversation. Service Broker therefore uses the service name and, optionally, the contract name to determine the security for the service.

In addition to Service Broker dialog and transport security, SQL Server permissions are required to run Service Broker statements such as SEND, RECEIVE, CONNECT, and so on. The GRANT statement can be used to allow permissions on a Service Broker contract, message type, remote binding, route, or service.

Summary

Service Broker is one of the top developer features introduced in SQL Server 2005. It is a platform for building asynchronous database applications. It provides the infrastructure required for creating queued, asynchronous, reliable, and secure messaging applications. Application architectures based on Service Broker can scale up or scale out with minimal or no changes to the application code.

This chapter starts with an introduction to asynchronous messaging concepts and the benefits of integrating messaging into the database engine. The rest of the chapter discusses the Service Broker architecture, implementation, operation, troubleshooting, and security details.

Chapter 15, “SQL Server 2005 Support for 64-Bit Processors,” discusses SQL Server 2005’s support for the 64-bit processor architecture.