Configuring SQL Service Broker

When the SQL Server Service Broker was introduced in SQL Server 2005, it added a new weapon to the SQL Server arsenal: a technique known as asynchronous data processing. Although it isn’t new to the programming world, it’s new to the database world. During asynchronous data processing, a command is executed by a client, but the client doesn’t wait for the command to be completed.

The Service Broker offers guaranteed message delivery in the order in which the messages are sent, provided that the messages are sent within a single conversation. However, messages that are sent in different conversations can be received out of order if an earlier message takes longer to arrive. This typically happens when messages are sent between servers over a slow network link. Regardless of the order in which they’re sent, messages are processed in the order in which they’re received. They’re always processed one time and only one time, within the confines of the conversation in which they’re sent.

Using Object Types to Configure the Service Broker

The Service Broker doesn’t have a UI for configuration, so you configure it through T-SQL. When you configure the Service Broker, you must create the following six types of objects:

Message
Contract
Queue
Service
Route
Endpoint

Each of these object types fulfills a specific function within the Service Broker, and the objects must be created in the correct order. Most objects should be created in pairs in which one object is the source of the communication and one is the destination of the communication.

The data that’s sent within a message can be of any type of data. The data is kept in a binary state while it’s in transit and while it’s stored in the queue. When data is sent via the Service Broker, it’s typically sent within an XML document for the most flexibility.

Before you can use the Service Broker, you must enable it by using the ALTER DATABASE command. By default, when you create or restore a database, the Service Broker is disabled. The ALTER DATABASE command has two switches that you use together with the Service Broker: NEW_BROKER and ENABLE_BROKER. The NEW_BROKER switch is used to create a new Service Broker in the database. If you already have the Service Broker enabled, the NEW_BROKER switch removes any messages that are in flight and closes any conversations that are open. If you use the ENABLE_BROKER switch, any messages that are in flight when the database is backed up will be allowed to continue, and any conversations that are already open will be allowed to continue. You can usually get away with using only the ENABLE_BROKER switch.

ALTER DATABASE sample
SET NEW_BROKER;

ALTER DATABASE sample
SET ENABLE_BROKER;

You can check whether the Service Broker is enabled by checking the is_broker_enabled column of the sys.databases catalog view.

Before you can send and receive messages by using the Service Broker, you have to set a database master key, if one doesn’t already exist in the database. To do this, use the CREATE MASTER KEY statement, and specify the password for the database master key.

CREATE MASTER KEY ENCRYPTION BY PASSWORD='MyPa$$w0rd'

Object Type 1: Message

The first object to configure is the message type. The message tells the computer that’s running SQL Server what sort of validation is required for the data that’s being sent within the message. Message types are created by using the CREATE MESSAGE TYPE statement. When you use this statement, you tell the message type what kind of validation should be performed. Use the following code to create the message:

CREATE MESSAGE TYPE SampleMessageType
AUTORIZATION dbo
VALIDATION=NONE;

Object Type 2: Contract

The second object to configure is the Contract type. The contract tells the SQL Server instance what message types can be used within the conversation. You can bind one or more messages within a single contract. Contracts are created by using the CREATE CONTRACT statement. Only a single contract is required. However, you can use more than one contract if you want. Multiple contracts can be used when multiple applications need to send data into a single queue or when there is a need to show the data from multiple processes. Use the following code to create the contract:

CREATE CONTRACT SampleContract
AUTHORIZATION abo
(
     SampleMessageType SENT BY ANY
);

Object Type 3: Queue

The third object to configure is the queue itself. The queue is where the messages are stored between the time that they are sent and the time that they are processed by the receiving computer. Because the queues will store physical objects, you have to define in which file group the object is created. You create two queues, one as the source and one as the destination. After messages are processed, an acknowledgement is sent back to the sending queue. Therefore, a separate queue is used to hold and process these messages. You can use the following code to create the queue:

CREATE QUEUE SampleQueueSource
CREATE QUEUE SampleQueueDestination

Object Type 4: Service

The fourth object to configure is the service. The service binds the queue to the contract for the purposes of sending messages. A message is sent to a specific service. That service is configured by the queue to which the message is delivered and also by the contracts that can be used to send that message. The contract defines which message types are available when you send the message. The contract is bound to the service. A service, in turn, is bound to a single queue, but it can be bound to more than one contract. Listing 1 shows the code to create the service.

Object Type 5: Endpoints

Endpoints are required for server to server communications. Endpoints aren’t required for Service Broker solutions that don’t send messages between instances. The syntax for creating an endpoint is pretty straightforward. You specify the IP address, the TCP port, how the authentication should be handled, and what encryption option should be used when passing data between the two instances. You can use the following code to create the endpoints:

CREATE ENDPOINT ServiceBrokerEndpoint AUTHORIZATION sa
STATE=STARTED
AS TCP (LISTENER_PORT=5555, LISTENER_IP=ALL)
 FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS, ENCRYPTION=REQUIRED, ALGORITHM=RC4)
GO

In this sample code, the listener port is set to 5555. This is the same port that’s specified within the route. The IP address is set to ALL because we want the endpoint to listen on all the server’s IP addresses. This setting can be limited to a single IP address by changing the LISTENER_IP address to an IP address that the server uses.

You can specify either Windows Authentication or certificate-based authentication. Windows Authentication can be used when the instances are on the same domain. Certificate-based authentication must be used when the machines aren’t on the same Windows domain.

Object Type 6: Route

You can optionally configure a sixth object, a route, if the source and target services aren’t on the same system. A route enables messages to flow from one database to another, regardless of whether these databases are stored on the same SQL Server instance, on different instances on the same server, or on different servers. To create a route, each instance that will be involved will require you to create an endpoint for the specific use of the Service Broker. You create a route by using the CREATE ROUTE statement. On the computer that’s sending the message (SQL01, in this example), you create a route to the destination machine (SQL02, in this example).

In the following code, I’ve assumed that you have created endpoints on both computers, and that these endpoints are listening on TCP port 5555:

CREATE ROUTE SampleRoute
AUTHORIZATION dbo
WITH SERVICE_NAME = 'SampleServiceDestination',
 BROKER_INSTANCE = '1B164171-8061-4C56-AB32-8DBBE629DB38',
 ADDRESS='tcp://SQL02:5555';

You’ll have to change the BROKER_INSTANCE value to match the BROKER_INSTANCE values from the service_broker_guid column of the sys.databases system catalog view on the remote server to which you’re creating the route.

On the destination server, you must also create a route pointing back to the source server:

CREATE ROUTE SampleRoute
AUTORIZATION dbo
WITH SERVICE_NAME = 'SampleServiceSource',
 BROKER_INSTANCE = 1B164171-8061-4C56-AB32-8DBBE629DB38',
 ADDRESS='tcp://SQL01:5555';

When you create the routes, the service name of the local service is case sensitive regardless of what the database collation is set to. This is because the Service Broker does a bit-level comparison between the name that’s entered and the name of the service on the instance. If the case does not match, the messages will not flow across the route.

The BROKER_INSTANCE setting is an optional field. You can leave it blank or you can configure it by using the broker instance ID value from the sys.databases catalog view on the remote computer. If database mirroring is used on the computer at the other end of the route, you can include the MIRROR_ADDRESS parameter to tell the route the location of the database mirror. If you do not include the MIRROR_ADDRESS parameter, and if the database mirror fails over, the messages do not move to the destination database. This is because the route will not have the information to locate the database mirror.

Creating a Conversation Dialog

Sending messages does not require much T-SQL code. Before you send a message, you must first create a conversation dialog to identify the conversation because you send the messages from the source to the destination. You create the dialog by starting with the BEGIN DIALOG statement. After you create the conversation dialog, the dialog is assigned a GUID that’s used to differentiate the conversation from all other conversations.

After you create the conversation dialog, you can send messages over the conversation by using the SEND statement. In the following example, we take the contents of the sys.objects catalog view and put it into the XML document. Listing 2 shows the code to create the conversation dialog.

Processing the Messages

At this point, the message has been sent and can be viewed by querying the queue object directly by using the SELECT statement. You can view the data in the queue by running

SELECT * FROM \\[SampleQueueDestination\\]

You can look at the messages in the queue without causing them to process by using the SELECT statement. To process the messages, use the RECEIVE statement to obtain the contents of the message and to remove the message from the queue.

You can receive either a single message at a time using the code in Listing 3, or you can receive multiple messages using the code in Listing 4. After you process all messages in a conversation, use the END CONVERSATION statement to close the conversation so that no other message can be sent on the conversation. After the END CONVERSATION statement runs and closes the conversation, the conversation cannot be reopened. (Note that if there aren’t any messages in the destination queue, you’ll receive an error message stating that the conversation handle is missing.) After you process the values from the @Messages table, close the conversation as required.

After the END CONVERSATION statement, you’ll also see a message in the source queue. You have to receive this message and perform an END CONVERSATION procedure at the source end of the original message. This confirms for the Service Broker that the conversation is complete and that all the metadata about the conversation can be cleaned up.

Most typically, you’ll want to configure the receiving code within a stored procedure. This will allow you to configure the queue to run the stored procedure automatically when messages are received. You do this by configuring an activation procedure on the queue. After the stored procedure is created, the queue should be modified by using the ALTER QUEUE statement. Listing 5 shows the code to configure the receiving queue.

When you configure the MAX_QUEUE_READERS setting, you can configure the SQL Server computer to run anywhere from 0 to 32,767 parallel processes. The greater the number of parallel processes that you have running, the faster that messages can be processed. However, you should run no more than one parallel processes per CPU core on the server so that you do not overload the CPUs.

The Power to Build

When used correctly, the Service Broker can provide an easy-to-use, asynchronous message processing platform. It gives you the ability to build a wide variety of applications that don’t require an immediate response to the command that’s executed.

Listing 1: Code to Create the Service

CREATE SERVICE SampleServiceSource

AUTHORIZATION dbo 

ON QUEUE SampleQueueSource


(

     SampleContract

);

CREATE SERVICE SampleServiceDestination

AUTHORIZATION dbo

ON QUEUE SampleQueueDestionation

(

     SampleContract

);

Listing 2: Code to create the conversation dialog

DECLARE @conversation_handle UNIQUEIDENTIFIER

BEGIN DIALOG @conversation_handle

FROM SERVICE \\[SampleServiceSource\\]

     TO SERVICE 'SampleServiceDestination'

     ON CONTRACT \\[Sample Contract\\]


DECLARE @message_body XML

SET @message_body = (SELECT*FROM sys.objects FOR XML AUTO, ROOT('root'));

SEND ON CONVERSATION @conversation_handle

MESSAGE TYPE \\[SampleMessageType\\]

(@message_body);

Listing 3: Code to process a single message

DECLARE @message_body XML

DECLARE @conversation_handle UNIQUEIDENTIFIER

RECEIVE TOP (1) @conversation_handle = conversation_handle,

          @message_body = cast(message_body AS XML)

FROM SampleQueueDestination

END CONVERSATION @conversation_handle;

Listing 4: Code to process multiple messages

DECLARE @Messages TABLE

(conversation_handle UNIQUEIDENTIFIER,

     message_body varbinary(MAX));


RECEIVE TOP(100) conversation_handle, message_body

FROM SampleQueueDestination

INTO @Messages;

Listing 5: Code to create the receiving queue

ALTER QUEUE SampleQueueDestination

WITH ACTIVATION (

     STATUS=ON,

     PROCEDURE_NAME = dbo.YourProcedureName,

     MAX_QUEUE_READERS=2,

     EXECUTE AS dbo);

Using Object Types to Configure the Service Broker

The Service Broker doesn’t have a UI for configuration, so you configure it through T-SQL. When you configure the Service Broker, you must create the following six types of objects:

· Message

· Contract

· Queue

· Service

· Route

· Endpoint \\[Denny, is an endpoint an object type? I wasn’t able to verify this online. Thanks!\\]

ALTER DATABASE sample

SET NEW_BROKER;

ALTER DATABASE sample

SET ENABLE_BROKER;

You can check whether the Service Broker is enabled by checking the is_broker_enabled column of the sys.databases catalog view.

CREATE MASTER KEY ENCRYPTION BY PASSWORD='MyPa$$w0rd'

Object Type 1: Message

CREATE MESSAGE TYPE SampleMessageType

AUTHORIZATION dbo

VALIDATION=NONE;

Object Type 2: Contract

CREATE CONTRACT SampleContract

AUTHORIZATION dbo

(

SampleMessageType SENT BY ANY

);

Object Type 3: Queue

CREATE QUEUE SampleQueueSource

CREATE QUEUE SampleQueueDestination

Object Type 4: Service

Object Type 5: Endpoints

CREATE ENDPOINT ServiceBrokerEndpoint AUTHORIZATION sa

STATE=STARTED

AS TCP (LISTENER_PORT=5555, LISTENER_IP=ALL)

FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS, ENCRYPTION=REQUIRED, ALGORITHM=RC4)

Object Type 6: Route

In the following code, I’ve assumed that you have created endpoints on both computers, and that these endpoints are listening on TCP port 5555:

CREATE ROUTE SampleRoute

AUTHORIZATION dbo

WITH SERVICE_NAME = 'SampleServiceDestination',

BROKER_INSTANCE = '1B164171-8061-4C56-AB32-8DBBE629DB38',

ADDRESS='tcp://SQL02:5555';

On the destination server, you must also create a route pointing back to the source server:

CREATE ROUTE SampleRoute

AUTHORIZATION dbo

WITH SERVICE_NAME = 'SampleServiceSource',

BROKER_INSTANCE = '1B164171-8061-4C56-AB32-8DBBE629DB38',

ADDRESS='tcp://SQL01:5555';

Creating a Conversation Dialog

Processing the Messages

At this point, the message has been sent and can be viewed by querying the queue object directly by using the SELECT statement. You can view the data in the queue by running

SELECT * FROM \\[SampleQueueDestination\\]

The Power to Build

InstantDoc ID 128931

Denny Cherry has more than a decade of experience managing SQL Server installations. He works as an independent consultant, holds several Microsoft certifications, and is a Microsoft MVP.

Listing 1: Code to create the service

CREATE SERVICE SampleServiceSource

AUTHORIZATION dbo

ON QUEUE SampleQueueSource

(

SampleContract

);

CREATE SERVICE SampleServiceDestination

AUTHORIZATION dbo

ON QUEUE SampleQueueDestination

(

SampleContract

);

Listing 2: Code to create the conversation dialog

DECLARE @conversation_handle UNIQUEIDENTIFIER

BEGIN DIALOG @conversation_handle

FROM SERVICE \\[SampleServiceSource\\]

TO SERVICE 'SampleServiceDestination'

ON CONTRACT \\[SampleContract\\]

DECLARE @message_body XML

SET @message_body = (SELECT * FROM sys.objects FOR XML AUTO, ROOT('root'));

SEND ON CONVERSATION @conversation_handle

MESSAGE TYPE \\[SampleMessageType\\]

(@message_body);

Listing 3: Code to process a single message

DECLARE @message_body XML

DECLARE @conversation_handle UNIQUEIDENTIFIER

RECEIVE TOP (1) @conversation_handle = conversation_handle,

@message_body = cast(message_body AS XML)

FROM SampleQueueDestination

END CONVERSATION @conversation_handle;

Listing 4: Code to process multiple messages

DECLARE @Messages TABLE

(conversation_handle UNIQUEIDENTIFIER,

messag

Using Object Types to Configure the Service Broker

The Service Broker doesn’t have a UI for configuration, so you configure it through T-SQL. When you configure the Service Broker, you must create the following six types of objects:

Message
Contract
Queue
Service
Route
Endpoint \\[Denny, is an endpoint an object type? I wasn’t able to verify this online. Thanks!\\]

ALTER DATABASE sample

SET NEW_BROKER;

ALTER DATABASE sample

SET ENABLE_BROKER;

You can check whether the Service Broker is enabled by checking the is_broker_enabled column of the sys.databases catalog view.

CREATE MASTER KEY ENCRYPTION BY PASSWORD='MyPa$$w0rd'

Object Type 1: Message

CREATE MESSAGE TYPE SampleMessageType

AUTHORIZATION dbo

VALIDATION=NONE;

Object Type 2: Contract

CREATE CONTRACT SampleContract

AUTHORIZATION dbo

(

SampleMessageType SENT BY ANY

);

Object Type 3: Queue

CREATE QUEUE SampleQueueSource

CREATE QUEUE SampleQueueDestination

Object Type 4: Service

Object Type 5: Endpoints

CREATE ENDPOINT ServiceBrokerEndpoint AUTHORIZATION sa

STATE=STARTED

AS TCP (LISTENER_PORT=5555, LISTENER_IP=ALL)

FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS, ENCRYPTION=REQUIRED, ALGORITHM=RC4)

Object Type 6: Route

In the following code, I’ve assumed that you have created endpoints on both computers, and that these endpoints are listening on TCP port 5555:

CREATE ROUTE SampleRoute

AUTHORIZATION dbo

WITH SERVICE_NAME = 'SampleServiceDestination',

BROKER_INSTANCE = '1B164171-8061-4C56-AB32-8DBBE629DB38',

ADDRESS='tcp://SQL02:5555';

On the destination server, you must also create a route pointing back to the source server:

CREATE ROUTE SampleRoute

AUTHORIZATION dbo

WITH SERVICE_NAME = 'SampleServiceSource',

BROKER_INSTANCE = '1B164171-8061-4C56-AB32-8DBBE629DB38',

ADDRESS='tcp://SQL01:5555';

Creating a Conversation Dialog

Processing the Messages

At this point, the message has been sent and can be viewed by querying the queue object directly by using the SELECT statement. You can view the data in the queue by running

SELECT * FROM \\[SampleQueueDestination\\]

The Power to Build

InstantDoc ID 128931

Denny Cherry has more than a decade of experience managing SQL Server installations. He works as an independent consultant, holds several Microsoft certifications, and is a Microsoft MVP.

Listing 1: Code to create the service

CREATE SERVICE SampleServiceSource

AUTHORIZATION dbo

ON QUEUE SampleQueueSource

(

SampleContract

);

CREATE SERVICE SampleServiceDestination

AUTHORIZATION dbo

ON QUEUE SampleQueueDestination

(

SampleContract

);

Listing 2: Code to create the conversation dialog

DECLARE @conversation_handle UNIQUEIDENTIFIER

BEGIN DIALOG @conversation_handle

FROM SERVICE \\[SampleServiceSource\\]

TO SERVICE 'SampleServiceDestination'

ON CONTRACT \\[SampleContract\\]

DECLARE @message_body XML

SET @message_body = (SELECT * FROM sys.objects FOR XML AUTO, ROOT('root'));

SEND ON CONVERSATION @conversation_handle

MESSAGE TYPE \\[SampleMessageType\\]

(@message_body);

Listing 3: Code to process a single message

DECLARE @message_body XML

DECLARE @conversation_handle UNIQUEIDENTIFIER

RECEIVE TOP (1) @conversation_handle = conversation_handle,

@message_body = cast(message_body AS XML)

FROM SampleQueueDestination

END CONVERSATION @conversation_handle;

Listing 4: Code to process multiple messages

DECLARE @Messages TABLE

(conversation_handle UNIQUEIDENTIFIER,

message_body varbinary(MAX));

RECEIVE TOP (100) conversation_handle, message_body

FROM SampleQueueDestination

INTO @Messages;

Listing 5: Code to create the receiving queue

ALTER QUEUE SampleQueueDestination

WITH ACTIVATION (

STATUS=ON,

PROCEDURE_NAME = dbo.YourProcedureName,

MAX_QUEUE_READERS=2,

EXECUTE AS dbo);

e_body varbinary(MAX));

RECEIVE TOP (100) conversation_handle, message_body

FROM SampleQueueDestination

INTO @Messages;

Listing 5: Code to create the receiving queue

ALTER QUEUE SampleQueueDestination

WITH ACTIVATION (

STATUS=ON,

PROCEDURE_NAME = dbo.YourProcedureName,

MAX_QUEUE_READERS=2,

EXECUTE AS dbo);

Comments

Plain text