Chapter 6

Service Broker in SQL Server 2012

WHAT’S IN THIS CHAPTER

• Processing Data Asynchronously
• Configuring SQL Service Broker with TSQL
• Sending and Receiving Messages with SQL Service Broker

This chapter reviews the various objects that make up the SQL Server Service Broker. As you move through the chapter you look at the various object types and how to create the objects. Toward the end of the chapter you examine the T-SQL code to use the objects when sending and receiving messages within the SQL Server database, between databases on the same server, and on different servers.

ASYNCHRONOUS MESSAGING

Asynchronous data processing is a foreign concept to most data professionals. In traditional data processing everything is done synchronously. A command is run, and a result is stored or sent back to the end user, or whatever the command is supposed to do. With asynchronous messaging the command is sent and will be processed later when the SQL Server gets around to processing the command. This can’t be done with traditional SQL commands, and you can’t just flip a switch to put a database into some sort of asynchronous mode. Using asynchronous messaging with SQL Server Service Broker requires a specific setup and configuration.

There are a variety of scenarios where SQL Server Service Broker can fill the role. SQL Server Service Broker has been successfully deployed in applications where it handles Extract, Transform and Load (ETL) between an OLTP database and a data warehouse in real time, in banking applications where it handles nightly batch processing of transaction data, in social media handling friend requests on MySpace as well as a variety of other applications where the command needs to be completed, just not at the exact time that the command was issued.

SQL Service Broker Overview

SQL Server Service Broker provides an extremely flexible framework, which enables the creation of a variety of objects that can send messages within a database. The messages aren’t limited to being sent within the database, though. Messages can also be sent from database to database. The databases that the messages are sent between can exist on the same database instance, different instances within a company’s data center, or between two SQL Server instances across the Internet. Messages are sent within the context of a conversation, which this chapter discusses later.

When messages are sent, they can be received and processed on a schedule via the SQL Server Agent (or another job scheduler), on demand by a Windows application or service, or automatically when they are received by setting an activation stored procedure. Activation stored procedures are created on the queue and are triggered automatically when messages are received by the receiving queue. Activation procedures can be configured to run a single thread or multiple threads running in parallel with up to 62,767 parallel threads.

Messages can be sent in a single direction or bidirectionally as needed. Typically messages are sent in only one direction; however, there are situations in which after the initial message is processed you might want to send a confirmation message back to be processed on the sending side.

SQL Server Service Broker introduces three new commands that are used to send and receive messages, all of which are discussed in more detail later in this chapter. The first is the CREATE CONVERSATION DIALOG command that creates the conversation on which messages are then sent. The second is the SEND command that is used to send messages on the previously created conversation. The third is the RECEIVE command that is used to remove messages from the queue for processing.

The beauty of SQL Server Service Broker is that messages are processed once, and only once and in the order sent, provided the messages are sent within the same conversation. If messages are sent within different conversations, they may not be processed in the order they are sent.

You can use Service Broker to send messages of just about any size, from a blank message (not to be confused with a NULL message even though a blank message has a NULL value) to a message that fills the XML data type that caps at 1 billion bytes of Unicode data.

SQL Server Service Broker Versus Other Message Queues

A variety of message queuing technologies are available. Microsoft makes two: SQL Server Service Broker and Microsoft Message Queue (MSMQ). There are several third-party technologies that function similar to MSMQ that you can also use.

The big difference between SQL Server Service Broker and other queuing technologies is that SQL Server Service Broker stores the messages in queue within the database, and other queuing technologies store their data outside of the database. Because SQL Server Service Broker stores its messages within the database, this makes the queues transactionally consistent with the data that the queues back up and restore along with the database. The upside to this is that if the database is restored, the same messages will still be there when the database was backed up. When using a message queue such as MSMQ when the database is restored to an older point in time, any messages that were processed since the database was backed up are lost.

This doesn’t necessarily mean that SQL Server Service Broker is a superior queuing technology over other message queuing technologies though. There are places in which SQL Server Service Broker makes more sense as the solution and places in which a message queue outside of the database makes more sense. For instance, if you have two Windows services (or applications) that need to send messages to each other and don’t already have a need to access a database, then using SQL Server Service Broker would not be a good fit. This is true because it adds a dependency to SQL Server that doesn’t already exist because the SQL Server would require the database to be online for the applications to send messages to each other. This occurs because for the application to send messages into a SQL Server Service Broker queue, the database must be online and available. There is no way to send a message to the SQL Server Service Broker without directly logging into the database. In the case of a situation like this, a message queue such as MSMQ would be a better solution because MSMQ allows you to send messages without being logged into the database.

On the other hand, if you have a situation in which you have a Windows service (or application) that needs to send messages to a database, and the database needs to process that message directly using T-SQL (or even SQLCLR code) then the SQL Server Service Broker might be a good choice. The reason SQL Server Service Broker is the better option is because the application needs to log into the database to get the message to the database and the SQL Server Service Broker can only be accessed via T-SQL. It makes sense to store the messages in a queue that can be accessed by T-SQL directly without any third-party, extended stored procedures.

CONFIGURING SQL SERVER SERVICE BROKER

There is actually no user interface to configure the bulk of SQL Server Service Broker, a fact that has greatly slowed the adoption of SQL Server Service Broker. Because there is no user interface for most of the object configuration, the majority of the setup must be completed using T-SQL as is shown through this section. The only part of SQL Server Service Broker that you can configure using the SQL Server Management Studio user interface is when you enable SQL Server Service Broker for the specific database. All the various objects, such as the message types, contracts, queues, services, and routes must be configured via T-SQL.

Configuring SQL Server Service Broker can be complex because it uses a lot of new terms, which can make the entire process confusing. If you are unsure of a term’s definition initially, keep reading because the term will likely be defined in a few paragraphs.

Enabling

To enable or disable SQL Server Service Broker using SQL Server Management Studio, connect to the instance in question in the object explorer. Right-click the instance and select Properties, selecting the Options page; then under Other Options, scroll down to the Service Broker section, as shown in Figure 6-1. The Broker Enabled setting is the only setting that can be configured by selecting from either True or False.

FIGURE 6-1

You can also enable SQL Server Service Broker using T-SQL by using the ALTER DATABASE statement. There are two important flags when using this method. The first is the NEW_BROKER flag that configures SQL Server Service Broker for the first time. The second is the ENABLE_BROKER flag that turns SQL Server Service Broker on if it is disabled, as shown in the following code. If SQL Server Service Broker has never been enabled and the ENABLE_BROKER flag is used, it has the same effect as if the NEW_BROKER flag was used.

ALTER DATABASE sample_database
SET ENABLE_BROKER

You see if SQL Server Service Broker is enabled by looking at the values of the is_broker_enabled and service_broker_guid columns of the sys.databases system catalog view. The is_broker_enabled column is a bit field with a value of either 0 or 1. The service_broker_guid column contains a unique guid value that represents the specific instance of SQL Server Service Broker within the specific database. When the guid within the service_broker_guid column is all zeros, this indicates that the SQL Server Service Broker has never been enabled for that database before.

After SQL Server Service Broker has been enabled for a specific database, the SQL Server Service Broker objects can be created, and messages can begin being sent.

Message Types

The first object type that you need to create when configuring SQL Server Service Broker is a message type. Message types validate that the data within a message is the correct, expected format. You can use four different validation options:

• NONE: Any data can be placed within the body of the message that is sent. When no value is specified for the VALIDATION option, the default value of NONE is used.
• EMPTY: Only messages that are empty can be sent.
• WELL_FORMED_XML: Only messages consisting of well-formed XML documents can be sent.
• VALID_XML WITH SCHEMA COLLECTION: Only XML documents that fit with the specified XML schema can be used. The XML schema to use with the VALID_XML WITH SCHEMA COLLECTION option requires that the XML schema already exists within the database by using the CREATE XML SCHEMA COLLECTION command.

Beyond the validation option, the CREATE MESSAGE TYPE command has only two other options. The first is the name of the message type, which must fit within the standard SQL Server object naming rules. The second option is the AUTHORIZATION option, which sets the owner of the message type when it is created. When the person creating the message type is a member of the sysadmin fixed server role or the db_owner fixed database role, then the value specified for AUTHORIZATION can be any valid database user or role. When the person creating the message type is not a member of the sysadmin fixed server role or the db_owner fixed database role, then the value specified for AUTHORIZATION must be that user, or another user that the user in question has the rights to impersonate. If no value is specified for the AUTHORIZATION parameter, the message type belongs to the current user. The following code snippet shows the creation of a message type.

CREATE MESSAGE TYPE YourMessageType
AUTHORIZATION dbo
VALIDATION = WELL_FORMED_XML

Contracts

The second object type to create is a contract. Contracts define the message types that are used within a single conversation. Contracts, similar to message types, have a couple of parameters that need to be specified when using the CREATE CONTRACT statement. These include the following:

• The name of the contract must follow the standard SQL Server object naming rules.
• The AUTHORIZATION value must be a user or role that exists within the database.

The CREATE CONTRACT requires a specific list of message types that are bound to the contract, and each message type can only be used by a specific member of the conversation. There are three options available for the user who can use each message type:

• INITIATOR: The SQL Server Service Broker SERVICE (SERVICEs are discussed later in this chapter) who initiated the conversation.
• TARGET: The SQL Server Service Broker SERVICE who received the conversation.
• ANY: Enables both the TARGET and the INITIATOR to use the message type. Although the following code example shows a single Message Type being specified, multiple message types can be specified with a comma-separated list, as shown in the next code example.

  CREATE CONTRACT MyContract
  AUTHORIZATION dbo
  (YourMessageType SENT BY ANY)
  CREATE CONTRACT MyContract
  AUTHORIZATION dbo
  (YourMessageType SENT BY INITIATOR,
  AnotherMessageType SENT BY TARGET)

Queues

The third object types you can create are queues. Queues are where the messages within the SQL Server Service Broker are stored in the time period between when they are sent and when they are processed. Although the rest of the objects created are logical objects made up only of records in system tables, queues are physical objects that create physical tables under them that store the actual messages. Because queues are physical tables, one of the many options available to the person creating the queue is the file group that will contain the queue.

When creating the queue several other options can be specified. All these choices are optional with the exception of the name of the queue.

• The first option is the STATUS of the queue, which can be ON or OFF. When a queue is ON, it is available to receive messages, and messages can be received from the queue. When a queue has a STATUS of OFF and a stored procedure or other T-SQL code attempts to RECEIVE messages from the queue, the RECEIVE command returns an error message. The STATUS option defaults to ON.
• The second option is the RETENTION, which can be ON or OFF. When message retention is ON after messages are received, they are not removed from the queue; they are instead kept for auditing purposes. Although the messages cannot be received a second time, they can remain persisted on disk and can be viewed later by selecting them using the SELECT statement with the queue name in the FROM clause. When message retention is OFF, the messages will be removed from the queue as soon as they have been received.
  

  Although the retention option on the queues is great for auditing purposes, there is one downside: there is no way to easily purge some of the data from the queue. You can use the ALTER QUEUE command to change the RETENTION from ON to OFF, which would then purge all the data in the queue. However if you wanted to only purge some data, for instance, all data except for the last 90 days, there is no built-in way to do it. You would instead need to export the last 90 days’ worth of data into a table and then purge all the data.

• The third option is the POISON_MESSAGE_HANDLING, which can be ON or OFF. When poison message handling is enabled on a queue, which is the default, it causes the queue to automatically disable after five consecutive transaction rollbacks. When poison message handling is disabled, the message handling must be handled within the application.
• The fourth option is the activation stored procedure configuration, which is made up of four child settings:
  • STATUS: Under the ACTIVATION setting, STATUS is used to enable or disable the activation procedure. When ACTIVATION is disabled, it stops only new threads of the activated stored procedure from being spawned; already running threads are left running.
  • PROCEDURE_NAME: This parameter is the name of the stored procedure that should be activated.
  • MAX_QUEUE_READERS: The number of threads that should be spawned, each of which calls the activated stored procedure.
  • EXECUTE AS: This parameter specifies the username that the procedure should be run as. The values that can be specified are SELF, OWNER, or any valid user within the database.

The following code shows the various options that can be specified. Two queues should be used when sending messages within an application: one queue as the source queue, and one queue as the destination queue. This way, when conversations are closed, the acknowledgments that are sent automatically are not sent to the same queue that has the production work load in it. This becomes especially important for high load workloads because a large amount of time may be spent processing these acknowledgments instead of processing the production workload that needs to be done.

CREATE QUEUE YourQueue_Source
WITH STATUS=ON,
     RETENTION=OFF,
     ACTIVATION
         (STATUS=OFF,
          PROCEDURE_NAME=dbo.MySourceActivationProcedure,
          MAX_QUEUE_READERS=1,
          EXECUTE AS OWNER),
     POISION_MESSAGE_HANDLING=ON;

When creating a queue that has an activated stored procedure, you can configure the activated procedure when the queue is created, as shown in the previous code snippet. However, the stored procedure must exist before the queue can be created using this method. Because of this, the queue is often created without configuring the ACTIVATION settings. Instead, the stored procedure is created and the queue is altered using the ALTER QUEUE command to set the activation settings. The end result of creating a queue while enabling the activation settings would be the same if the queue was created without enabling the activation settings. In either case the queue would call the stored procedure when messages were received in the queue.

Services

Another object type you create is called a service. Although poorly named, SQL Server Service Broker Services play an important role. Service Broker Services are objects that are configured via the CREATE SERVICE statement in T-SQL. Services can specify which contracts (and therefore which message types) can be used when sending messages to a specific queue. When messages are sent, they are sent to a specific service that then delivers the message into a specific queue.

CREATE SERVICE has only a few parameters that can be set, which are shown in the following code.

• Object name: Like other objects, this setting follows the normal object-naming standards.
• AUTHORIZATION: this works just like the AUTHORIZATION parameter when creating a message type or contract.
• Queue name: The name of the queue to which the messages sent to the service will be delivered.
• Comma-separated list of contracts: This can be used when creating conversations sent to this service.

  CREATE SERVICE YourService_Source
  AUTHORIZATION dbo
  ON QUEUE dbo.YourQueue_Source
  (MyContract)
  GO

When creating a queue for each side of the conversation, you must also create a service for each side of the conversation. These service names are then specified when using the BEGIN DIALOG CONVERSATION statement, which is explained in more detail later in this chapter.

When selecting the name for your services, do not select the name ANY. Within SQL Server Service Broker the service name ANY is a reserved word that causes service broker priorities (found later in this chapter) to be applied to all services instead of to the specific service called ANY that is specified.

Routes

You can also create object types called routes. Routes control the database to which the messages should be routed. There is a default route created in every database called AutoCreatedLocal that can usually be left as-is. There are situations, however, where this default route needs to be removed. The primary reason why this default route needs to be removed is if multiple databases are on the same SQL Server instance that has services with the same name. If the default route is left in the database, the SQL Service Broker route will round robin the messages into the databases on the server that has the matching service name. For this reason you should either remove the default route or ensure unique service names across databases that may be unrelated to each other. When sending messages between databases, a route must exist in both databases referencing the remote database. Routes can be configured to redirect messages for a single service or for all services, depending on the needs.

Routes have a variety of parameters that you can configure via the CREATE ROUTE statement. These include the following:

• Name of the route: The name of the route follows the normal object naming rules.
• Name of the service: The name of the service to which the route should apply is another parameter. You can either specify the name of the specific service, or you can omit the service name from the CREATE ROUTE statement, which causes the route to apply to all services. When specifying the service name as part of the CREATE ROUTE statement, the service name is always case-sensitive, ignoring the databases collation setting. The reason for this is that the SQL Server does a binary compare of the route’s service setting and the service name within the database. Because uppercase and lowercase characters have different binary values, if a single character does not match, the route will not apply.
• BROKER_INSTANCE: The BROKER_INSTANCE is an optional parameter that tells the route to which database on the server to send the messages. The BROKER_INSTANCE value can be queried from the sys.databases catalog view on the instance that hosts the database to which the route is pointing. If the BROKER_INSTANCE value is not specified, the SQL Service Broker attempts to identify the destination database on the instance based on matching the destination service name with the service names in the databases on the remote instance.
• LIFETIME: This is also an optional parameter. The LIFETIME parameter tells the SQL Service Broker for how many seconds the route should be active. When the lifetime of the route has expired, the route will be ignored. If the LIFETIME is omitted or a value of NULL is specified, the route will never expire.
• ADDRESS: The ADDRESS parameter is a required parameter that tells the SQL Service Broker how to contact the remote database. This parameter can specify an IP address, a network name, or a fully qualified domain name followed by the TCP port number of the service broker endpoint that must be created on the remote instance in the format of TCP://ServerName:PortNumber. If the destination database is located on the same instance as the source database, then the ADDRESS parameter can be specified as LOCAL. If the parameter is specified as TRANSPORT, then the SQL Service Broker attempts to identify which remote instance to connect to based on the name of the service.
• MIRROR_ADDRESS: This optional parameter configures the route to support database mirroring if the destination database is configured for database mirroring. If the destination database is configured for database mirroring and the MIRROR_ADDRESS is not specified and the database were to failover to the mirror instance, the messages would not be delivered until the database failed back to the instance specified in the ADDRESS parameter. The value of the MIRROR_ADDRESS parameter should be specified in the same format at the ADDRESS parameter.

The following code snippet shows the use of the various parameters when using the CREATE ROUTE statement.

CREATE ROUTE ExpenseRoute
    WITH SERVICE_NAME = 'MyService',
    BROKER_INSTANCE = '53FA2363-BF93-4EB6-A32D-F672339E08ED',
    ADDRESS = 'TCP://sql2:1234',
    MIRROR_ADDRESS = 'TCP://sql4:4567' ;

Priorities

SQL Server Service Broker priorities assign priorities to conversations to force specific conversations to always be processed before lower priority conversations. This can be important in high load environments in which some messages need to be processed before others. The conversation priority is assigned by matching the name of the contract, the source service name, and the destination service name to what was configured in the Service Broker Priority.

Because you don’t specifically set a conversation’s priority when the conversation is created, it is wise to create multiple contracts, all of which use the same message types and are configured to be used for the specified services. Using priorities to create a high priority conversation and a lower priority conversation could be done by creating a contract with the name “ContractLow” and a second contract named “ContractHigh.” Then a priority could be named that triggers on the ContractHigh, which has a high priority level assigned.

Creating SQL Server Service Broker priorities is done via T-SQL using the CREATE BROKER PRIORITY statement. This statement accepts five different values, including the name of the priority. The next three values enable you to specify the name of a service, or you can specify the special value of ANY, which causes that priority to be applied to any conversation. The last parameter is the priority level that will be used for the conversations to which this priority will be applied. The priority can be any whole number inclusively between the numbers 1 and 10. Conversations that do not have a specific priority applied to them are assigned the priority of 5. The usage of the CREATE BROKER PRIORITY statement is shown in the following code snippet, in which a message sent to any service using the contract name MyHighPriority would be given the priority of 8 instead of the default of 5.

CREATE BROKER PRIORITY HighPriority
FOR CONVERSATION
SET ( CONTRACT_NAME = MyHighPriority ,
      LOCAL_SERVICE_NAME = ANY ,
      REMOTE_SERVICE_NAME = N'ANY' ,
      PRIORITY_LEVEL = 8
)

Conversation Groups

Conversation groups control the order that messages are processed when those messages are sent to different services. This is done by putting the conversations sent from the same service to different services into a single group. This in-order processing is done through a process called conversation locks, which ensures that messages within the conversations in the same conversation group are processed “exactly once in order” or EOIO. This conversation locking is done automatically whenever a message is sent or received on the conversation group.

By default each conversation is put into its own conversation group unless a conversation group is specified when the conversation is created. You can specify the conversation group into which the new conversation should be placed in two ways. You can do this by specifying the conversation group that should be used, or by specifying the handle of the conversation that the new conversation should be grouped with.

There is no specific command to create a new conversation group. When a new conversation is started, a new group is created automatically, and it is assigned a new GUID value as its identifier. To assign new conversations to a specific conversation group, a new GUID value simply needs to be assigned as the RELATED_CONVERSATION_GROUP parameter for the BEGIN DIALOG CONVERSATION statement, which is covered later in this chapter.

If you want to query the conversation group that the next message to be processed is a memory of, you can do this using the GET CONVERSATION GROUP statement. To use this statement, specify a variable that the next conversation group will be placed into, as well as the name of the queue to get the conversation group from, as shown in the following code.

DECLARE @conversation_group_id AS UNIQUEIDENTIFIER;
 
GET CONVERSATION GROUP @conversation_group_id
FROM YourQueue;

USING SQL SERVER SERVICE BROKER

Sending and receiving messages through SQL Server Service Broker is a basic task. Instead of using INSERT to put messages into the queue, like you would with a table, and SELECT to pull messages from the queue, you use the SEND statement to send messages and the RECEIVE statement to pull messages from the queue.

Sending Messages

You can send messages using the SEND command. The SEND command accepts only two parameters: the conversation ID and the body of the message, as shown in the following code snippet. You can get the conversation ID from the BEGIN DIALOG CONVERSATION command, as shown in the previous code snippet used to assign priorities.

DECLARE @message_body AS XML, @dialog_handle as UNIQUEIDENTIFIER
 
SET @message_body = (SELECT * 
     FROM sys.all_objects as object 
     FOR XML AUTO, root('root'))
 
BEGIN DIALOG CONVERSATION @dialog_handle
     FROM SERVICE [YourSourceService]
     TO SERVICE 'YourDestinationService'
     ON CONTRACT [YourContract];
 
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE YourMessageType
(@message_body)
GO

The BEGIN DIALOG CONVERSATION command accepts several parameters, some of which are required and some of which are optional. The first three parameters are required and are the source and destination services that you send the message and the contract from and to, respectively. Optionally, after the destination service name, you can specify the service broker GUID of the destination database or CURRENT DATABASE. The default value is CURRENT DATABASE which causes messages to be routed to the same database within which the user is running the code to send the message. The third parameter is the contract that defines the message types used to send the messages.

The rest of the parameters are all optional. The first two can be either the RELATED_CONVERSATION or the RELATED_CONVERSATION_GROUP that both specify a certain conversation group that relates the new conversation to another conversation. The RELATED_CONVERSATION accepts the conversation ID from another pre-existing conversation. The RELATED_CONVERSATION_GROUP accepts a specific conversation group ID that the new conversation would then be a member of.

The next parameter is the LIFETIME of the conversation, which specifies the amount of time that the conversation remains open. The LIFETIME is the number of seconds until the conversation closes automatically. The LIFETIME value is expressed as an integer data type with the default being the maximum value of the INT data type, which is 2^31-1 (2,147,483,647).

The last parameter is the ENCRYPTION parameter, which specifies whether the messages within the conversation should be encrypted while in transmission to another instance of SQL Server. This parameter accepts only ON or OFF, and encryption is ON by default. When sending messages between database instances, it is highly recommended that encryption be ON. When ENCRYPTION is ON and messages are sent within the same instance while the data isn’t actually encrypted, the database master key and the certificates needed for the encryption are required for the conversation to successfully begin and to send the message.

The code shown in the previous example is a great start, but it isn’t good for high-performance SQL Server Service Broker workloads. This is because the cost of creating a conversation for each message is expensive. When working with high-load systems that send hundreds of thousands or millions of messages per day, you want to reuse conversations sending multiple messages per conversation to reduce the overhead of sending messages. You can easily do this by logging the conversation handle (the value of the @dialog_handle value that is set in the BEGIN DIALOG CONVERSATION command) to a table so that it can be retrieved by future sessions. A table like this is shown in following code snippet.

CREATE TABLE dbo.SSB_Settings
([Source] sysname NOT NULL,
[Destination] sysname NOT NULL,
[Contract] sysname NOT NULL,
[dialog_handle] uniqueidentifier
CONSTRAINT PK_SSB_Setting PRIMARY KEY ([Source], [Destination], [Contract])

One key requirement for using multiple messages per conversation is that there needs to be a way for the sending side of the conversation to tell the receiving side of the conversation that there will be no more messages sent over that conversation. An easy way to do this is to have an additional message type within the database that is specifically used as a trigger on the destination side so that it knows when to end the conversation. In the following code, you can notice that a message type named EndOfConversation is used to trigger the remote side to close the conversation.

In a high-load environment, a stored procedure could be used to decide if a new conversation should be created, as well as storing the value as needed. Listing 6-1 shows the send_sequence value from the sys.conversation_endpoints dynamic management view is used to decide if it is time to end the conversation.

LISTING 6-1: Creating A Reusable Conversation.sql

CREATE PROCEDURE dbo.CreateConversation
     @Destination sysname,
     @Source sysname,
     @Contract sysname,
     @MessageType sysname,
     @MessageBody XML,
     @dialog_handle uniqueidentifier
AS
/*Get the conversation id.*/
SELECT @dialog_handle = dialog_handle
FROM dbo.SSB_Settings 
WHERE [Source] = @Source 
     AND [Destination] = @Destination 
     AND [Contract] = @Contract;
 
/*If there is no current handle, or the conversation has had 1000 messages 
sent on it, create a new conversation.*/
IF @dialog_handle IS NULL OR
     (SELECT send_sequence 
        FROM sys.conversation_endpoints 
        WHERE conversation_id = @dialog_handle) >= 1000
BEGIN
     BEGIN TRANSACTION
     /*If there is a conversation dialog handle signal the destination 
     code that the old conversation is dead.*/
     IF @dialog_handle IS NOT NULL
     BEGIN
          UPDATE dbo.SSB_Settings 
          SET dialog_handle = NULL
          WHERE [Source] = @Source
               AND [Destination] = @Destination 
               AND [Contract] = @Contract;
 
          SEND ON CONVERSATION @dialog_handle
          MESSAGE TYPE EndOfConversation;
 
     END
 
     /*Setup the new conversation*/
     BEGIN DIALOG CONVERSATION @dialog_handle
     FROM SERVICE @Source
     TO SERVICE @Destination
     ON CONTRACT @Contract;
 
     /*Log the new conversation ID*/
     UPDATE dbo.SSB_Settings 
          SET dialog_handle = @dialog_handle 
     WHERE [Source] = @Source
          AND [Destination] = @Destination 
          AND [Contract] = @Contract;
 
     IF @@ROWCOUNT = 0
          INSERT INTO dbo.SSB_Settings 
           ([Source], [Destination], [Contract], [dialog_handle])
          VALUES
           (@Source, @Destination, @Contract, @dialog_handle);
END;
 
/*Send the message*/
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE @MessageType
(@XML);
 
/*Verify that the conversation handle is still the one logged in the table. 
  If not then mark this conversation as done.*/
IF (SELECT dialog_handle 
    FROM dbo.SSB_Settings  
    WHERE [Source] = @Source 
        AND [Destination] = @Destination 
        AND [Contract] = @Contract) <> @dialog_handle 
    SEND ON CONVERSATION @dialog_handle
        MESSAGE TYPE EndOfConversation;
GO

Receiving Messages

Receiving messages is done using the RECEIVE command. The RECEIVE command is written much like a SELECT statement where the person writing the statement can specify the columns that should be returned, and the queue is specified as the FROM statement, as shown in the following code snippet. After the data has been received into a variable, anything that needs to be done with it can be done.

DECLARE @dialog_handle UNIQUEIDENTIFIER, @message_body XML
 
RECEIVE TOP (1) @dialog_handle = conversation_handle, 
     @message_body = CAST(message_body as XML)
FROM YourDestinationQueue
 
/*Do whatever needs to be done with your XML document*/
 
END CONVERSATION @dialog_handle

There should also be a second set of code, usually configured as an activated stored procedure on the source queue to read received all messages, and to then end the conversation of all messages that are received.

As with the code shown in the Sending Messages section, the basic code in this previous example is not the most efficient way to receive data. It is more efficient to receive multiple messages at once, and to receive the message body from the queue as the raw binary, and then convert it to XML (or whatever data type it was sent as) after it has been removed from the queue. The following code shows how to receive multiple messages in a single statement.

DECLARE @dialog_handle UNIQUEIDENTIFIER, @message_body XML
 
DECLARE @Messages TABLE
(conversation_handle uniqueidentifier,
message_type sysname,
message_body VARBINARY(MAX))
 
WAITFOR (
RECEIVE TOP (1000) conversation_handle, message_type_name, message_body
FROM YourDestinationQueue
INTO @Messages)
 
DECLARE cur CURSOR FOR select conversation_handle, CAST(message_body AS XML) 
                       FROM @Messages 
                       WHERE message_body IS NOT NULL
OPEN cur
FETCH NEXT FROM cur INTO @dialog_handle, @message_body
WHILE @@FETCH_STATUS = 0
BEGIN
     /*Do whatever needs to be done with your XML document*/
     FETCH NEXT FROM cur INTO @dialog_handle, @message_body
END
CLOSE cur
DEALLOCATE cur;
 
IF EXISTS (SELECT * FROM @Messages WHERE message_type = 'EndOfConversation')
     END CONVERSATION @dialog_handle
GO

Sending Messages Between Databases

Sending messages between databases that reside on the same instance of SQL Server is almost as easy as sending messages within a single database. Sending messages between databases requires creating the same objects on both the source and destination database and then creating routes between the two databases. The new routes within the sending databases should be created on the destination service and point to the second database. They should use an ADDRESS of LOCAL. The routes on the destination database should be created for the source service and point back to the source database, again using an ADDRESS of LOCAL.

You may notice that there is no code sample to show you for this section. The reason for this is that sending messages between databases when the databases are on the same instance doesn’t require any changes to the code when sending or receiving the messages. You take the exact message types, contracts, queues, and services and use the same code that you use to send messages within a single database. The only thing that needs to be changed is that a route needs to be created within each database. In the source database a route is added referencing the destination service and in the destination database a route is added referencing the source service. Once the services are added the messages will now be sent from one database to another.

Sending Messages Between Instances

One of the most powerful features of SQL Server’s Service Broker is its capability to send messages between databases on different instances, which run on different physical (or virtual) servers. Configuring SQL Service Broker to send messages between instances is effectively the same as configuring SQL Service Broker to send messages between databases on the same SQL Server instance. The major difference is the steps needed to configure the authorization of communications between the instances. These steps are outlined in the following list; they should be done on both of the instances that will be exchanging SQL Service Broker Messages:

After you complete these steps, messages can route between the two databases.

Database Master Key

Before you begin using SQL Service Broker between instances, you must enable the database master key for both databases by using the CREATE MASTER KEY statement on both databases. If this has already been done, you do not need to do this again. Creating the master key is quite simple, as shown in the following code snippet, because the command accepts only a single parameter, which is the password used to secure the database master key.

The database master key is a symmetric key used to protect all the other keys within the database, including other symmetric keys, certificates, and asymmetric keys.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourSecurePassword1!'

After you create the database master key, back it up using the BACKUP MASTER KEY statement so that the master key can be recovered if a database failure occurs. Securely store the backup of the database master key at an offsite location.

Creating Certificates

When using certificate authentication between the endpoints, you must create certificates in the master databases of the instances that exchange messages. You can create certificates using the CREATE CERTIFICATE statement within the master database, as shown in the following code snippet. When creating the certificates on each instance, assign a unique name to each one. The easiest way to do this is to include the server and instance name in the name of the certificate.

CREATE CERTIFICATE MyServiceBrokerCertificate
WITH SUBJECT = 'Service Broker Certificate', 
     START_DATE = '1/1/2011',
     EXPIRY_DATE = '12/31/2099'

Exchanging Certificates

Once you create the certificates, you need to exchange them. You can exchange certificates between instances by backing up the certificate using the BACKUP CERTIFICATE statement, as shown in the following code, on the machine that has the certificate. You then need to restore the certificate to the remote instance using the CREATE CERTIFICATE statement, as shown in the second code snippet.

BACKUP CERTIFICATE MyServiceBrokerCertificate 
     TO FILE='C:MyServiceBrokerCertificate.cer'
CREATE CERTIFICATE MyServiceBrokerCertificate
FROM FILE='c:MyServiceBrokerCertificate.cer'

SQL Service Broker Endpoints

Endpoints enable users or other SQL Servers to connect to the SQL Server instance that the endpoint is created on. For SQL Server instances to send messages to another instance, you must create endpoints on each SQL Server instance. You can create endpoints using the CREATE ENDPOINT statement, as shown in the following code. Each SQL Server Instance can have only one Service Broker endpoint; even if multiple instances send messages to a single server, all communication must be done through a single endpoint. Service Broker endpoints support a variety of authentication techniques including NTLM-, KERBEROS-, and CERTIFICATE-based authentication, as well as several combinations of those three authentication techniques. When doing cross-instance authentication for SQL Service Broker, messaging CERTIFICATE authentication is recommended because it removes the dependency on Active Directory.

USE master
GO 
CREATE ENDPOINT ServiceBrokerEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 1234, LISTENER_IP=ALL)
FOR SERVICE_BROKER
(AUTHENTICATION = CERTIFICATE MyServiceBrokerCertificate, 
     ENCRYPTION = REQUIRED ALGORITHM RC4);
GO

You can also configure encryption on the endpoint with the encryption being either DISABLED, SUPPORTED, or REQUIRED. Encryption is supported using both the RC4 or AES algorithms as well as combinations of both algorithms specified as AES RC4 and RC4 AES.

When configuring the SQL Server Service Broker endpoint, a specific TCP port, separate from the default SQL Server TCP port that the instance is listening on, needs to be specified. You must also specify the IP address that the endpoint should be listening on. In the preceding code example, TCP port 1234 is used to listen on, and the endpoint can listen on all IP addresses that are configured on the server that the instance runs on. If the endpoint should listen only on a specific IP address, the IPv4 or IPv6 address should be specified where the LISTENER_IP setting is specified.

External Activation

External activation is different from the normal activated stored procedures, which are available via the CREATE QUEUE or the ALTER QUEUE statements. External Activation runs as a separate Windows service (ssbea.exe), which monitors the SQL Server queue waiting for new messages to arrive in the queue. Upon a new message arriving in the queue, the external activation service launches the Windows application that it is configured to run. The External Activation service monitors the SQL Server Service Broker queue by having you configure an event notification on your queue, which then sends a message to a second monitoring queue. When a message arrives in the application queue, the event notification sends a message to the notification queue, which causes the external activation service to launch the application. Sample code that can create the event notification is shown in the following code snippet. You can download the external activation service from http://bit.ly/SSB_EA.

CREATE QUEUE dbo.MyDestinationQueueEA
GO
CREATE SERVICE MyDestinationServiceEA
ON QUEUE dbo.MyDestinationQueueEA
(
    [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
)
GO
CREATE EVENT NOTIFICATION MyDestinationNotificationEA
ON QUEUE MyDestinationQueue
FOR QUEUE_ACTIVATION
TO SERVICE 'MyDestinationServiceEA', 'current database'
GO

The code in the previous code snippet assumes that the event notification and all the SQL Service Broker objects are created in the same database, which is why 'current database' has been specified. If you want to put the external activation queue and service in another database, then the service broker GUID for the destination database should be specified instead of the 'current database'.

SUMMARY

Although SQL Server Service Broker is quite complex to set up, it is an extremely powerful tool to use when you require asynchronous messaging. The SQL Server Service Broker is a flexible solution that can enable you to send messages within the database, from database to database within the same SQL Server instance, or from database to database between servers even if the servers are located next to each other or are half a world apart.

As you have seen through this chapter, you need to configure a variety of objects. Although SQL Server Service Broker may at first appear to be quite complex, after some time working with it, the system becomes quite easy to use, and all the pieces begin to make sense.