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
AUTHORIZATION 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 dbo
(
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.