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.

Note

Regardless of the message type, SQL Server stores the content of the message as type varbinary(max). Therefore, a message can contain any data that can be converted to varbinary(max). The Service Broker message size is limited to 2GB.

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 procedurebut can be an external programthat 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.

Note

Acknowledgement messages are handled internally by the database engine, and they do not appear in a queue or visible to the application. Service Broker does not consider it an error for a remote service to become unreachable. When a remote service is unreachable, Service Broker holds messages for that service until the service becomes reachable or the dialog lifetime expires.

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.

Note

SQL Server Service Broker architecture defines two types of conversations: dialog and monolog. Dialog is a two-way, reliable, ordered exchange of messages, and monolog is a one-way, reliable, ordered publication of messages from one endpoint to any number of endpoints. However, monolog conversation is not available in SQL Server 2005, but is a planned feature for a future release. In this release, the terms dialog and conversation are synonymous. This is the reason you begin a dialog (by using BEGIN DIALOG CONVERSATION), but end a conversation (by using END CONVERSATION). Throughout this chapter, conversation refers to either a dialog or a monolog.

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.

Note

Service Broker checks the routes defined in the msdb database (msdb.sys.routes) for messages on conversations that originate in another instance, including messages to be forwarded. The routing table in the local database is used only for the outgoing messages that originate in the current instance.

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).

Note

SQL Server 2005 Express Edition supports Service Broker. If messages are sent within the same instance, there are no restrictions. However, if a message is sent to or from a remote instance, the remote instance or an instance in the route must be non-Express Edition. In other words, one of the instances in Server Broker cross-instance communication must be an edition other than Express Edition.

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

Caution

By default, restored or attached databases have Service Broker disabled. The exception to this is Database Mirroring, in which Service Broker is enabled after failover.

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.

Note

For XML messages, you can optionally specify the XML schema collection while creating the message type. When the message arrives at the target, Service Broker validates the XML message contents against the XML schema specified with the message type. If schema validation fails, an error message is sent back to the initiator. Note that XML validation imposes a fair amount of overhead.

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.

Note

Service Broker objects, including services, queues, message types, and contracts, may not be temporary objects. Their names can begin with #, but they are still permanent database objects.

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 triggerand hence the INSERT statementreturns 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.