SSB 1

Dialog Conversations

In most messaging systems, messages are the basic unit of communication. Each message is an isolated entity, and it's up to the application logic to keep track of sent and received messages. On the other hand, most real business transactions consist of a number of related steps or operations. For example, a simple purchase order will often involve a series of message exchanges between the purchaser and supplier that continue for several weeks as items are ordered, prices are negotiated, shipment dates are agreed on, order status is communicated, shipment is confirmed, billing is completed, and payment is received. Similarly, think of the number of messages exchanged during the bidding for, purchase of, and delivery of an item from an online auction site. These simple examples illustrate that many real-world business transactions take a significant amount of time and involve many related steps.

Obviously, a business transaction that takes weeks to complete can't be handled as a single database transaction. Each step of the business transaction translates to one or more database transactions. Each database transaction is related to transactions that have already occurred and transactions that will happen in the future to complete the business transaction. The application must maintain the state of the business transaction so that it knows what has happened and what remains to be done. Because this is a database application, it's logical to assume that this application state will be maintained in the database and that updates to the state will be part of the database transactions that make up the business transaction.

The Service Broker dialog conversation was designed to make managing this type of business transaction reliable and simple. As I go through the features of dialog conversations and how they are implemented, you will see how the goal of supporting this kind of business transaction influenced the design of Service Broker. Obviously, not all business transactions fit this model of a long-running exchange of data between two systems. Many business transactions have a single step and can be executed as a single database transaction. Other business transactions only flow data in one direction—for example, a point-of-sale system at a grocery store that transfers a long series of scanned items to be inserted into a database. As you'll see, dialog conversations handle these variations well also.

Conversations

Service Broker defines a conversation as a reliable, ordered, and asynchronous transfer of messages between conversation endpoints. I'll discuss conversation endpoints in more detail later, but for now think of a conversation endpoint as something that can send or receive messages. The endpoints of a conversation might be in the same database, in different databases in the same SQL Server instance, or in different databases in different SQL Server instances.

There were originally two types of conversations defined for Service Broker. A dialog conversation is a reliable, ordered, bidirectional exchange of messages between exactly two endpoints, and a monolog conversation is a reliable, ordered stream of messages from one sending endpoint to many receiving endpoints.

Monolog conversations were cut early in SQL Server 2005, but they will probably be included in a later release. Because a monolog can be implemented as a bunch of dialogs from one sending endpoint to a bunch of receiving endpoints, the dialog conversation is more important and was implemented first. Some aspects of conversations are common to both dialogs and monologs, while other aspects apply to only dialogs or only monologs, so the Service Broker T-SQL statements include both DIALOG and CONVERSATION. Because SQL Server 2005 has only one type of conversation, this amounts to two words for the same thing, but because the differentiation will be necessary when monologs are implemented, both terms are used in the language. This usage might lead to some initial confusion, but in the long run, it will be necessary to make monologs work.

The three distinguishing features of Service Broker conversations are the reliable, ordered, and asynchronous delivery of messages. Because these are key concepts, I'll explain them in some detail:

Reliable

Reliable delivery of conversation messages means that Service Broker will keep trying to deliver the message in the face of pretty much any type of failure or interruption, including the following ones:

• The sending database is stopped and started.

• The receiving database is stopped and started.

• The network goes down.

• The network is being reconfigured.

• The sending database or receiving database is being restored from backup.

• The sending database or receiving database fails over to a disaster recovery site.

• The sending or receiving database is being moved to a different server.

This kind of reliability means Service Broker applications don't have to be written to deal with delivery issues. When a message is sent, it is delivered reliably or the conversation is put into an error state and all endpoints of the conversation are informed of the error with an error message in the endpoint queue. As you can see, Service Broker goes to great lengths to avoid declaring an error on a conversation, but there are cases in which a conversation can be damaged badly enough that it can't continue. The other common source of conversation errors is the lifetime of a conversation expiring. In many cases, it doesn't make sense to keep trying to deliver messages forever. If you haven't received a response from the airline reservation system and the plane has left already, you don't really care what the answer is. To accommodate this scenario, you can set a maximum lifetime on a conversation. If the conversation is still around when the lifetime expires, an error is declared and error messages are sent to all conversation endpoints.

Reliable delivery also means the message wasn't changed or intercepted along the way, so Service Broker provides a wide range of security options to give you the assurance you need that the message was delivered securely and intact. Even if all Service Broker security is turned off, Service Broker uses checksums to ensure that the message didn't change in transit.

In Order

Service Broker provides unprecedented facilities for message ordering within a conversation. Some messaging systems will ensure messages are delivered in order provided that they are sent in a single transaction and received in a single transaction. Messages in a Service Broker conversation will be received and processed in the order in which they were sent, even if they were sent in multiple transactions issued by multiple applications and received in multiple transactions by multiple different applications.

To see why this is important, imagine an order entry application that receives an order header, three order lines and an order footer. In a messaging system that doesn't ensure ordering, your application might receive an order line before it receives the order header or receive the order footer before it receives all three order lines. This means the application will have to be able to recognize messages arriving out of order and store them somewhere until the missing messages arrive—greatly increasing the complexity of the application.

Even if the messages are delivered in order, if your application is multithreaded for efficiency, the messages still might be processed out of order. For example, if thread one receives the order header and starts processing it and then thread two starts processing the first order line, it's very possible that the order line will be processed before the order header is completely processed. This chain of events could occur because the order header is generally more complex than the order line, so inserting the order line into the database will fail because the order header isn't there yet. The application can try the order line again until it works, but this is inefficient and adds to the complexity of the application. If all the messages are in a Service Broker conversation, they will be delivered and processed in the order in which they were sent so that the application doesn't have to deal with the complexities of processing messages out of order.

Asynchronous

Asynchronous delivery of messages effectively decouples the endpoints of a Service Broker conversation from each other. The application at one endpoint of a conversation sends a message and then goes on to process more work without knowing when or where the message will be processed. It's entirely possible that the sending application and the receiving application never run concurrently. For example, an inventory application might send out manufacturing orders as shortages are produced during the day, and the manufacturing orders might be read by a planning application that runs at night. Even if the sender and receiver are running concurrently, asynchronous message delivery means that the sending application doesn't have to wait around for the receiving application to finish processing the message. It can continue with other work and rely on Service Broker to reliably deliver the message to the receiver for the receiver to process when it has time available.

Asynchronous messaging means you can link fast systems and slow systems into a single application without worrying about the slow systems holding back the fast systems. For example, your new order-entry system can send shipping instructions to your clunky old shipping system without the shipping system slowing down order entry. Virtually all big, scalable, high performance applications make asynchronous calls to slower parts of the system. For example, the operating system performs disk I/O asynchronously, and the Web server makes asynchronous network calls. Another example is SQL Server, which performs many asynchronous operations internally.

With Service Broker, asynchronous processing is now available to database application writers. This means, for example, that if you want to write a trigger that does a significant amount of work without slowing down updates to the table, you can have the trigger send a Service Broker message to another application that does the work. You can also have one stored procedure call several other stored procedures in parallel by using asynchronous Service Broker messages to start the other stored procedures. You will see an example of this later.

Now that you've seen what a conversation is and what it does, I'll spend the rest of this section talking about how Service Broker implements conversations. When I'm done, you will have a thorough understanding of how Service Broker works because almost all of Service Broker revolves around conversations. I'll cover programming and security separately—not because they aren't tightly tied to conversations, but because they're big enough to justify their own headings.

Messages

So far, I've talked a lot about messages without ever saying what they are. Messages are the information exchanged in a conversation. Messages have two parts:

• A message header, which contains information about the message, such as its source and destination, sequence number, and security credentials

• A message body, which the application uses to transport data

The Service Broker message body is a VARBINARY(MAX) datatype that can contain up to 2 GB of any SQL Server data that can be cast to VARBINARY(MAX). A Service Broker message can exist either in the database or on the wire. In the database, it is a row of a SQL Server table whose columns that contain metadata about the message and the message contents. On the wire, it is binary data sent in TCP/IP packets. I won't go into detail about the wire format, but the disk format will be discussed in the upcoming "Queues" section.

The header of a Service Broker message must contain a message type. The message type is just a name for the message that makes it easier to write Service Broker programs to handle messages. To see why a message type is required, think about an application for a human resources department that sends information to an employee database. One message might be an XML document with information on one employee's benefits, another might be an employee's photograph as a JPEG file, and another might be a resume created in Microsoft Office Word, and so on. Without the message type to guide you, all you would know is that all the message bodies are VARBINARY(MAX). Because Service Broker ensures ordering, you might get by with knowing the first message is XML, the second is a JPEG, and so forth. However, error messages get pushed to the front of a dialog, so that is a risky strategy. Because you can rely on Service Broker to always include the message type, you can write your application to extract the message contents into the appropriate data structure depending on the message type.

Before you can use a message type in a Service Broker application, you must create it as a database object. The message type is one of several Service Broker metadata objects that are used to enforce constraints on Service Broker dialogs. Service Broker will use only message types that are defined in the metadata for the database, so you can be confident that your application won't receive a message type you don't know about. The simplest form of a message type is just a name. The following data definition language (DDL) code snippet will create simple message type objects:

CREATE MESSAGE TYPE [//microsoft.com/Inventory/AddItem];
CREATE MESSAGE TYPE [//microsoft.com/Inventory/ItemAdded];

The only parameter in this case is the name. Notice that the name has a URL-like format. This doesn't mean that the message type exists somewhere out on the Web. This format is used to make it easier to uniquely identify a message type. This message type adds an item in an inventory application distributed by microsoft.com. The message type name is just a string, so "AddItem" would have been perfectly legal, but in a large distributed application, it's difficult to ensure that there will never be two different message types named "AddItem". The URL format isn't required, but it can make your life easier when you start putting together large distributed Service Broker applications.

Note

Message type names are sent in messages between databases that might have been configured with very different collations. To make this work, message type names and all other Service Broker metadata sent in the message header use a binary collation. This means that the names must match exactly, character for character, with both case and accent sensitivity. The failure to adhere to this case-sensitive matching is a common source of application errors.

While you are free to put anything you want into a Service Broker message body, you can optionally have Service Broker do some basic checking of the message body for you as messages arrive on a conversation. This checking is specified with the VALIDATION clause of the CREATE MESSAGE TYPE command:

CREATE MESSAGE TYPE message_type_name
[ VALIDATION = {  NONE
| EMPTY
| WELL_FORMED_XML
| VALID_XML WITH SCHEMA COLLECTION
schema_collection_name
} ]

The NONE option, as the name implies, performs no validation and is the default. The EMPTY option ensures that the message body is NULL. Messages with a type but no body are useful as flags. For example, a message that reports that an action has completed successfully might not have any data to convey, so a message with a type of "Success" with no body might be appropriate. The WELL_FORMED_XML option loads the message body into an XML parser and rejects the message if parsing fails. The VALID_XML option loads the message body into the XML parser and validates the XML with the specified SCHEMA COLLECTION. (See Books Online for a more complete explanation of what a schema collection is and how to create one.) For purposes of this chapter, it's enough to know that the message contents must be valid based on the schemas in the schema collection. Because a schema collection can contain many schemas, it is possible to use a single schema collection to validate a number of message types.

Although using one of the XML validations will ensure that your application can handle the incoming message, Service Broker loads the message body into an XML parser to validate it and then, in most cases, the application will load it into a different parser to process it. This process can be a significant resource drain if message volumes and message sizes are large, so unless you are receiving messages from an untrusted source, it might make sense to just use validation until you have your application working correctly and then turn it off when you go into production. Because validation is configured per message type, it's possible to validate only a few message types that have a higher potential to be invalid.

All Service Broker metadata can be seen in SQL Server catalog views. The view for message types is sys.service_message_types. If you look at that view in a user database, you will find several system-defined message types. The message types that begin with http://schemas.microsoft.com/SQL/ServiceBroker/ are used by the Service Broker features. The other system-defined message types are used by other features such as Query Notifications and Events, which use Service Broker to deliver messages.

Contracts

Just as message types constrain the names and contents of conversation messages, a Service Broker contract constrains which message types are allowed in a conversation. A contract, as its name implies, is an agreement among the endpoints in a Service Broker conversation as to which message types can be sent and who can send them. Because the Service Broker enforces the contract, applications that process the conversation can be assured that they won't receive any message types that they aren't equipped to handle. To further ensure this agreement, once a Service Broker contract is created, the list of message types can't be changed.

A contract, like a message type, is a piece of Service Broker metadata that is used by Service Broker to enforce constraints on conversations. Each contract has a name, a list of what message types can be sent on the contract, and information that specifies which endpoint can send the approved message type. Because the endpoints of a conversation are peers once the conversation is established, the only real differentiator among endpoints is which one started the conversation. The endpoint that started the conversation is called the INITIATOR, and the opposite endpoint is the TARGET. These labels are used to specify which endpoint can send a message type. The contract must specify the allowed sender or senders of each message type in the conversation by specifying INITIATOR, TARGET, or ANY for the message type. Put this all together and you get the following CREATE CONTRACT statement:

CREATE CONTRACT [//microsoft.com/Inventory/AddItemContract]
([//microsoft.com/Inventory/AddItem] SENT BY INITIATOR,
[//microsoft.com/Inventory/ItemAdded] SENT BY TARGET);

Notice that the contract also uses a URL-like name format because it too is sent as part of the message header. The contract name uses a binary collation also, so be careful of the case when typing the name.

There's no ALTER CONTRACT statement for adding or removing message types, so you have to get the CREATE CONTRACT right. The only way to change the message type list is to drop the contract and create a new one.

The catalogue view for listing contracts is sys.service_contracts. The view that describes how messages are related to contracts is sys.service_contract_message_usages. The following query will generate a list of all the message types in the database, what contracts they are used in, and which endpoints send them:

SELECT  C.name AS Contract, M.name AS MessageType,
CASE
WHEN is_sent_by_initiator = 1
AND is_sent_by_target    = 1 THEN 'ANY'
WHEN is_sent_by_initiator = 1 THEN 'INITIATOR'
WHEN is_sent_by_target    = 1 THEN 'TARGET'
END AS SentBy
FROM sys.service_message_types AS M
JOIN sys.service_contract_message_usages AS U
ON M.message_type_id = U.message_type_id
JOIN sys.service_contracts AS C
ON C.service_contract_id = U.service_contract_id
ORDER BY C.name, M.name;

DEFAULT

If you ran the previous query, you might have noticed a message type named DEFAULT and a contract named DEFAULT that contains the DEFAULT message type. These were created as a result of customer feedback that writing a simple Service Broker application wasn't very simple. If you want to write an application that just sends and receives simple messages and you want to handle all messages the same, you can use the DEFAULT message type and contract so that you don't have to define your own. In the Service Broker data manipulation language (DML) commands, if you don't specify a message type or contract, DEFAULT is used.

Queues

In defining conversations, I said they were reliable and asynchronous, meaning that messages survive network and database failures and can be delivered even if the sender and receiver don't ever execute at the same time. Service Broker makes this happen by storing messages persistently in the database until they are successfully delivered so that the messages won't be lost. The place messages are stored while they are in transit is called a queue. Queues are very common in computer systems. They are used to store work that passes between tasks that run at different rates. For example, the operating system places disk commands on a disk queue, and the disk controller executes them when it has time available and then removes them from the queue. Using a queue means the operating system doesn't have to wait for the disk controller to be available to issue a disk command. It puts the command on the queue whenever it wants and then goes on to handle other tasks while the disk controller is processing the command. This kind of queue is different from a Service Broker queue because it exists only in memory. However, the principle of loose coupling—enabling the tasks that write to the queue and the tasks that read from it to each run at their own rate—applies to both.

Many large database applications use tables in the database as queues. An order-entry system, for example, might put a list of orders that need to be billed in a table in the billing database for the billing system to handle. The order-entry system only has to wait long enough to insert the order into the billing table. It doesn't have to wait for the bill to be generated. This approach not only allows the order-entry system to process orders faster, it allows the order-entry system to keep running when the billing system is down. Although queues are common in database applications, they are usually difficult to implement and a frequent source of problems. If multiple processes are inserting and deleting messages, blocking can cause performance issues and deadlocks. Getting locking right is difficult—too little locking causes multiple processes to get the same message, and too much locking causes deadlocks and performance problems.

At its most basic level, Service Broker is database queues done right. Service Broker queues are hidden tables that are managed by Service Broker code. Service Broker uses its own locking scheme to maximize queue performance, and queue manipulation commands built into the T-SQL language give Service Broker the tight control it needs to manage queues efficiently. This level of control is one of the primary advantages of having Service Broker built into the database. An external process couldn't get the control over query execution and locking that Service Broker uses to optimize queue operations.

Service Broker queues store messages in internal tables. Internal tables are just like normal tables except they can't be used in SQL commands because the table name is hidden. To see the name of the internal table used by a Service Broker queue, you can run this query:

SELECT Q.name AS QueueName, I.name AS InternalName
FROM sys.service_queues AS Q
JOIN sys.internal_tables AS I
ON Q.object_id = I.parent_object_id;

You will find, however, that trying to issue a SELECT statement against the hidden table name will fail. If you want to see the contents of a queue, you can issue a SELECT statement against the queue name. This approach works because Service Broker creates a view on the internal table with the same name as the queue name. This view is not an exact view of the internal table. Some additional columns are created through joins with the metadata tables to provide names for values that are stored as internal IDs. One of the benefits of Service Broker is that you can see what messages are available in the queue with a simple SELECT statement. If you need to run a SELECT statement on an active queue, I suggest using SELECT * FROM <queue name> WITH (NOLOCK), because most Service Broker actions hold locks on rows in the queue.

Queues are the only Service Broker objects that actually store data. Because of this, you can create a queue in a SQL Server schema and specify a filegroup in which you want the messages stored.

The following statement will create a Service Broker queue:

CREATE PROCEDURE dbo.InventoryProc  AS
RETURN 0;
GO
CREATE QUEUE dbo.InventoryQueue
WITH ACTIVATION (
PROCEDURE_NAME = dbo.InventoryProc ,
MAX_QUEUE_READERS = 2,
EXECUTE AS SELF);

Please ignore the CREATE PROCEDURE statement for now. It is necessary to make the CREATE QUEUE statement work and will be explained later. The full syntax for creating a queue is shown in the following code sample:

CREATE QUEUE <object>
[ WITH
[ STATUS = { ON | OFF }  [ , ] ]
[ RETENTION = { ON | OFF } [ , ] ]
[ ACTIVATION (
[ STATUS = { ON | OFF } , ]
PROCEDURE_NAME = <procedure>,
MAX_QUEUE_READERS = max_readers,
EXECUTE AS { SELF | 'user_name' | OWNER }
) ]
]
[ON { filegroup | [ DEFAULT ] } ]

Queue names are not sent in message headers, so they are just SQL Server object names with the same collation rules as normal SQL Server objects. Queues are also contained in SQL schemas, unlike the Service Broker metadata objects, so they can be referenced with three-part names.

You can ignore the ACTIVATION options for now because I'll explain them later. The STATUS option allows you to specify whether the queue should start handling messages as soon as it's created. You might want to create queues with STATUS set to OFF to keep them from getting filled up before you're ready, but be sure to use ALTER QUEUE to turn on the queue when you want to process messages.

The RETENTION option controls whether all messages should be kept around until the conversation is ended. When RETENTION is ON, all messages going both directions in a conversation are kept in the queue until the conversation ends. Turning on RETENTION might be useful if you need to back out a long-running business transaction by running compensating transactions. The messages will allow you to track what you have done so far so that you know what you have to undo. RETENTION can sometimes be useful for debugging an application also. Generally, I would advise you to use RETENTION with extreme caution. With RETENTION ON, the queue will get very big very fast and performance might degrade significantly. The ON <filegroup> option tells Service Broker where to create the hidden table to store messages. Queues normally don't get too big unless RETENTION is on or the application that receives messages is not running for a while.

You might want to consider not putting queues in the DEFAULT filegroup and instead put them in their own filegroup. This will keep a sudden growth in a queue from using all the free space in one of the critical filegroups. Because a queue is built on an internal table, there is no limit to how big a queue can become other than available disk space.

A key thing to remember is that all queues have the same schema. You are not allowed to add your own columns to a queue. Although defining your own message structure in a queue might seem like an attractive option, much of Service Broker's ability to rapidly transfer and manage messages in a dialog relies on the fact that Service Broker always knows the structure of a message and a queue. For example, if only one type of message could be sent in a conversation (because a queue could only hold one message structure), message ordering would be much less effective because a single order might be sent to three or four different queues. In this case, the application would have to assemble an order from different queues with messages arriving in different order on each queue, making the application logic much more complex.

Most columns in a Service Broker queue are self-explanatory and well documented in Books Online, but there are a few worth describing in a little more detail. The status column tracks the history of a message in the queue. When it is first inserted the status is 1, which means it is ready to be received. After it has been read, if RETENTION is turned on, the status is set to 0, meaning it has been received and processed by the application and the transaction has been committed. If RETENTION is not on, you won't see this status because the message is deleted as soon as it is processed. A status of 2 means the message has been received out of order, so it can't be processed until the missing messages arrive. A status of 3 means the message has been sent. Sent messages won't appear in the queue unless RETENTION is turned on. With RETENTION turned on, an extra copy of the sent message is inserted into the queue. This is another reason RETENTION hurts performance.

The priority column is a little misleading. Service Broker receives messages in priority order, but as of the SQL Server 2005 release, the priority is always 0 and there's no way to change it. Conversations enforce message ordering, so priority within a conversation would be meaningless because messages have to be processed in order no matter what the priority is. The priority column in the queue is intended to set the relative priority of conversations. The development group ran out of time before we could come up with a meaningful way to set the priority, so we deferred the feature to a later release. For now, the best way to implement priority processing is to point high-priority messages at a different queue than low-priority messages and then allow the application to process them appropriately. This approach generally entails not processing messages from the low-priority queue unless the high-priority queue is empty. However, be careful when you design this, because getting the behavior you want means dealing with starvation of the low-priority queues if there are enough high-priority messages to consume all your resources. You'll also have to make sure that processing a low-priority message doesn't keep you from seeing a high-priority message arriving. (Now you understand why we couldn't come up with a workable design in time.)

The last two queue columns worth commenting on are queuing_order and message_sequence_number. These columns are often cause confusion because they appear to be the same thing. The queuing_order column is the order that the message was placed in the queue regardless of the conversation to which it belongs. The message_sequence_number column is the order of the message within its conversation. This is the order that gets enforced when messages are received. You might see gaps in the queuing_order values because Service Broker might skip messages to return the messages in message_sequence_number order within a dialog.

Service Broker queues created with the CREATE QUEUE command hold messages that have reached their final destination and are ready to be received and processed. When Service Broker needs to store messages temporarily before they reach their final destination, it puts them in the sys.transmission_queue view. There is one sys.transmission_queue in every database. The structure of the sys.transmission_queue is a little different from the other queues because the Service Broker needs more information to send a message over the network. If possible, Service Broker will put a sent message directly on the destination queue. This will generally be possible if the destination queue is in the same SQL Server instance as the sender, but it won't be possible if the destination is in a different instance. Service Broker has an internal routine called the classifier, which decides what to do with messages. The classifier will put a message in the sys.transmission_queue in the following cases:

• The destination is in a different SQL Server instance.

• The destination queue is disabled—STATUS = OFF. The most common cause of this is a poison message on the queue. See the "Poison Messages" section later in the chapter.

• The Service Broker is disabled in the database where the destination queue is. This is generally caused by a database being attached or restored without the ENABLE_BROKER option.

• The destination is unknown. Reliable, asynchronous delivery can't fail just because the destination isn't known. The message will hang around on the sys.transmission_queue and be reclassified periodically until the destination is configured or the conversation lifetime expires.

Queues are the key to understanding how Service Broker works. At its lowest level, Service Broker puts messages on queues, takes messages off queues, or moves messages between queues. Most Service Broker problems turn out to be caused by messages being on the wrong queue. One of the most important sources of troubleshooting information is the transmission_status column of the sys.transmission_queue view. If the message has tried to leave the queue and failed, the transmission_status column will indicate what went wrong.

Services

A Service Broker conversation is a reliable, ordered exchange of messages between endpoints. Service Broker names the endpoints with service names. Service is a very overloaded word, but in the case of Service Broker, a service is just a name for a conversation endpoint. This is important to remember because many people assume "service" refers to an executable somewhere. In Service Broker, service is linked to a queue that is the destination for messages sent to the endpoint identified by the service name. Why not just use the queue name? The service name is a logical name used in the code, while a queue is a physical object. This level of indirection means that applications written using service names can be deployed in different physical configurations without needing code changes.

The service object also defines which contracts can be used to establish conversations that target the service. The service that identifies the target of a conversation determines whether it will accept the conversation. Because the list of conversations is enforced by the Service Broker infrastructure, the target application can be sure that it will not receive any unexpected message types.

Here's a simple example of a CREATE SERVICE statement:

CREATE SERVICE [//microsoft.com/InventoryService]
ON QUEUE dbo.InventoryQueue
([//microsoft.com/Inventory/AddItemContract]);

Notice that services are also known across the network, so the URL format for service names is recommended. This example shows only one contract, but any number of contracts can be associated with a service. Also, any number of services can be associated with a queue.

The catalogue view that exposes services is sys.services. This query lists service names and the queue name that receives messages targeted at the service:

SELECT S.name, Q.name
FROM sys.services AS S
JOIN sys.service_queues AS Q
ON S.service_queue_id = Q.object_id;

Because a service can use multiple contracts and a contract can be used in multiple services, the sys.service_contract_usages view shows the mapping between services and contracts. The following query displays the contracts associated with each service:

SELECT S.name AS [Service], Q.name AS [Queue], C.name AS [Contract]
FROM sys.services AS S
JOIN sys.service_queues AS Q
ON S.service_queue_id = Q.object_id
JOIN sys.service_contract_usages  AS U
ON S.service_id = U.service_id
JOIN sys.service_contracts AS C
ON U.service_contract_id = C.service_contract_id;

The conversation endpoint that initiates the conversation does not check the contract list of the initiator service when creating a dialog. For this reason, the contract list for initiator services is generally empty. The message types and contract used by the dialog are required to begin the dialog, so requiring the contract to be in the service list is redundant. That being said, if you want to put the contracts into the initiator service's list to document the interface, it doesn't cause any harm because Service Broker will ignore them. The danger is that if the initiator service's list is wrong, you will never know it.

Begining and Ending Dialogs

You now know about all the pieces necessary for a dialog: message types to label the messages, a contract to define which message types can be sent by each endpoint, queues to hold messages at each endpoint, and services to tie all the endpoint elements together. In this section, I will discuss the T-SQL statements to begin and end Service Broker dialogs. The endpoint that begins the dialog is called the initiator and the opposite endpoint is the target. The T-SQL command that begins a dialog is BEGIN DIALOG. The following code snippet provides an example:

BEGIN DIALOG CONVERSATION  @Dialog
FROM SERVICE    [//microsoft.com/ManufacturingService]
TO SERVICE      '//microsoft.com/InventoryService'
ON CONTRACT     [//microsoft.com/Inventory/AddItemContract]
WITH ENCRYPTION = OFF, LIFETIME = 3600;

This statement begins a dialog from the ManufacturingService endpoint to the InventoryService endpoint. The @Dialog is an output variable that contains a uniqueidentifier, which is used to refer to the dialog in other Service Broker DML commands. The FROM SERVICE and TO SERVICE parameters define the endpoint of the dialog. FROM SERVICE is the initiator, and TO SERVICE is the target. The FROM SERVICE specified must exist in the database where the BEGIN DIALOG command is executed. The TO SERVICE is not validated when the command is executed because in many cases it might be in another database.

More Info

One of the more frequently asked Service Broker questions is why the FROM SERVICE parameter is a SQL Server name and the TO SERVICE parameter is a string. Although in the current implementation the TO SERVICE name will always be a SQL Server name, it's assumed that at some point in the future Service Broker will be talking to services that are not necessarily SQL Server services. For this reason, the TO SERVICE parameter is a 256-character string.

The ON CONTRACT clause specifies which contract will be used by this dialog to limit which message types can be sent on the dialog and which endpoint can send each message type. If this clause is omitted, the DEFAULT contract is used. The DEFAULT contract allows only messages of the DEFAULT type to be sent by either endpoint.

The ENCRYPTION parameter might more accurately be called "encryption required." If this parameter is set to ON, the BEGIN DIALOG command will fail if dialog security is not set up—specifically, a remote service binding must be defined for the TO SERVICE name. (See the "Dialog Security" section for an explanation of dialog security.) If the ENCRYPTION parameter is set to OFF, the decision whether or not to use dialog security is a deployment decision. When the service is deployed, the administrator can decide whether to use security for the dialog based on the requirements of the installation.

Tip

When dialog security is used, the BEGIN DIALOG command will create a key to be used for encrypting the dialog messages. This dialog must be encrypted when it is stored in the database, so if ENCRYPTION is set to ON, the database must have a master key. You can create one with this command: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass.word1';

The LIFETIME parameter sets the maximum time in seconds that the dialog can remain active. If the dialog still exists when the LIFETIME expires, messages of type 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' are put into the queues at both endpoints of the dialog. The state of both endpoints is changed to 'Error' so that no more messages can be sent or received on the dialog. It's important to keep in mind that the dialog still exists until both endpoints end it, so your code needs to handle error messages.

Now that you know how to begin a dialog, you'll learn how to end one. In most applications, the application at one endpoint of the dialog will know that the dialog is complete. In a purchase order application, for example, the dialog might be complete after the purchaser has received acknowledgement that the ordered item has been paid for. The endpoint that determines that the dialog is complete will end it with an END CONVERSATION statement:

END CONVERSATION @dialog;

This command will mark the endpoint as closed, delete any messages still on the queue, and send a message of type 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' to the opposite endpoint of the dialog. This message is sent with a negative sequence number so that it will be received ahead of any other messages in the queue for this dialog. When the opposite endpoint receives the end dialog message, it might continue to process any messages still in the queue, but it can't send messages on the dialog because the opposite endpoint is gone. Similarly, any messages in the sys.transmission_queue that haven't been delivered to the opposite endpoint of the dialog are deleted when an EndDialog message is received. When the endpoint has processed any outstanding messages, it should do any required cleanup and then issue an END CONVERSATION command to terminate its side of the conversation. After both endpoints have ended the dialog, Service Broker will clean up the dialog state.

In some cases, one of the endpoints will decide to end the dialog because an unrecoverable error has occurred. A simple example would be a purchase order for an invalid part number. In this case, the endpoint can specify an error number and error text to let the other endpoint know what the error was. When a dialog is ended with the error option, an error message of type 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' is sent to the opposite endpoint instead of the end conversation message. Here's an example of ending a dialog with an error:

END CONVERSATION @dialog WITH ERROR = 31472
DESCRIPTION = 'Invalid Purchase Order number furnished';

While developing a Service Broker application, you'll see that it's not unusual to end up with a number of "orphaned" dialogs. These are dialogs that are still hanging around after the application that used them is gone. Dialogs usually end up in this state either because the application didn't call END CONVERSATION on one of the endpoints or the target wasn't configured correctly and the dialog lifetime expired before the dialog was established. You can get rid of these dialogs by ending them with cleanup code—for example:

END CONVERSATION @dialog WITH CLEANUP;

This command will unconditionally terminate the dialog without sending a message to the opposite endpoint, and it will discard any unprocessed messages. Therefore, it should be used only if you're sure the dialog is no longer in use. You can use this statement in scripts to clean up large numbers of orphaned or expired conversations. For example, this script will clean up all conversations in an error state:

DECLARE @handle AS UNIQUEIDENTIFIER;
DECLARE conv CURSOR FOR
SELECT conversation_handle
FROM sys.conversation_endpoints
WHERE state = 'ER';
OPEN conv;
FETCH NEXT FROM conv INTO @handle;
WHILE @@FETCH_STATUS = 0
BEGIN
END Conversation @handle WITH CLEANUP;
FETCH NEXT FROM conv INTO @handle;
END
CLOSE conv;
DEALLOCATE conv;

If you remove the WHERE clause from the SELECT statement, this script will get rid of all conversations in the database. This obviously requires administrator privileges to run and should be done only to clean up test data. It should never be done on a system with real dialogs running.

Conversation Endpoints

You have learned by now that Service Broker dialogs are reliable, persistent conversations. In this section, I'll discuss how reliability and persistence are implemented. The state that Service Broker maintains about a conversation is stored in the sys.conversation_endpoints view.

To ensure reliable exactly once delivery, Service Broker must keep track of what messages it has received. Because dialog messages must be in order, Service Broker doesn't have to keep a list of all the messages it has received. It is enough to keep track of the highest numbered message received successfully on the conversation. The receive_sequence column is the sequence number of the next message expected on this conversation. Large Service Broker messages might be sent as a number of smaller message fragments. Each fragment is tracked and acknowledged so that the whole message doesn't have to be resent if there's an error. The receive_sequence_frag column tracks the fragment number of the last fragment received. Note that if a fragment or message is received out of order, it is not thrown away. It is retained so that it doesn't have to be received again, but the receive_sequence and receive_sequence_frag values are not updated until all the missing fragments have been received. The send_sequence column tracks the sequence numbers of messages sent so that the dialog knows which sequence number to assign to the next message.

Note

The actual sequence number stored is not the sequence number of the last message or fragment received or sent, but rather the next sequence number expected. For example, when message 4 has been received successfully, the receive_sequence column will contain 5 because that is the next sequence expected. Because of this, when a message is received, its sequence number is compared to the receive_sequence column. If the numbers match, the message is marked as received and the receive_sequence is incremented. Similarly, when a message is sent, the value in the send_sequence column is used as its sequence number and the column is incremented after the message is sent.

The service_id column identifies the service associated with this conversation endpoint. You can join with the sys.services view to find the name of the service. The far_service column gives the name of the service at the opposite endpoint of this conversation. This is a name instead of an identifier because the far endpoint might be in another database. If the dialog has been successfully established and a message has been received from the opposite endpoint, the far_broker_instance column will be filled with the GUID taken from the service_broker_guid column of the sys.databases entry for the database where the remote endpoint is located. Together, these columns determine the two endpoints of the conversation. Notice that there is no information about the network address of either endpoint. This is to allow either endpoint to move to a different network location during the lifetime of a dialog without affecting message delivery. Moving active dialog endpoints is a very powerful feature that provides flexibility and resilience in a Service Broker network.

The state and state_desc columns of the sys.conversation_endpoints view display the state of the conversation endpoint. The state column is a two-character abbreviation for the full state description given in the state_desc column. The full list of possible states and their meaning is given in Books Online, but I'll highlight some of the more important states here.

• CO, or CONVERSING This is the normal state for a conversation in progress. Both endpoints are active and talking to each other.

• ER, or ERROR This means the conversation has encountered an unrecoverable error. The most common error is that the conversation lifetime has expired. Remember that even though the conversation is in an error state, it will not go away until both endpoints have called END CONVERSATION.

• DI, or DISCONNECTED_INBOUND This means that the opposite end of the conversation has called END CONVERSATION but this endpoint hasn't. If you see many conversations in this state, the code handling this endpoint is not calling END CONVERSATION when it should.

• CD, or CLOSED This means the conversation is closed completely. On the target side of a conversation, Service Broker will keep the conversation around in a CLOSED state for about half an hour to prevent replay attacks where an attacker saves and then resends a message. If the conversation endpoint entry is still there when the replayed message arrives, it will be ignored. After a half hour, the message lifetime will have expired, so a replayed message will be discarded.

One of the more confusing aspects of the sys.conversation_endpoints view is the conversation_handle and conversation_id columns. Both of these are GUIDs that are used to identify a conversation. The conversation_handle is the handle used to address the dialog in T-SQL commands such as SEND or END CONVERSATION. Each endpoint has a different handle so that Service Broker knows which endpoint you are referring to in the command. For example, if both endpoints of a dialog are in the same database, the two endpoints are in the same view. In this case, they have to be different so that Service Broker knows whether you are sending a message from the initiator to the target or from the target to the initiator. The conversation_id is the same for both endpoints of the conversation. It is included in each message header so that Service Broker can determine which conversation a message belongs to.

There are several more columns in the sys.conversation_endpoints view, but I have covered the most interesting ones here. The rest of the columns are either security related or useful only to Microsoft Product Support Services (PSS) or the development team trying to isolate a problem.

Conversation Groups

One of the more difficult aspects of writing asynchronous messaging applications is dealing with multiple applications or multiple threads receiving messages from a queue simultaneously. To understand why this is an issue, think about a purchase order arriving in a message queue. The purchase order is sent as multiple messages—a header message, multiple order-line messages, and a footer message. If multiple threads receive messages from the same purchase order, one or more order lines might be processed before the purchase order header. These transactions would fail because the header isn't in the database yet. Transactional messaging handles this situation because when the order-line transactions fail, the transaction rolls back and the message is put back on the queue. However, it's possible for an order line to be processed multiple times before the header is present. This is inefficient but manageable. However, if an order footer closes out the purchase order before the last order line is processed, data might be lost. Because of these problems, many messaging applications are written with only one receiving process for each queue. This approach obviously doesn't scale well, but it is often necessary to maintain consistency in the data.

The multithreaded reader problem is a bigger issue with Service Broker conversations because Service Broker ensures in-order processing of messages in a conversation. If one thread processes message 20 and another thread processes message 21 of a conversation, it's possible for 21 to complete before 20, which violates message ordering. Service Broker solves this problem through use of conversation group locking.

Every conversation is associated with a conversation group. When an application sends or receives messages, Service Broker locks the conversation group so that no other code can receive or send messages on the locked conversation until the transaction holding the lock completes. This means that even if your application has hundreds of queue readers active, a conversation can be accessed by only one queue reader at a time. This is a very powerful feature because it means that even when thousands of messages are being processed simultaneously, the messages for a particular conversation are processed on one thread at a time. The logic for processing a conversation can assume that a given conversation is processed sequentially, so it doesn't have to deal with the issues associated with multithreaded applications. After the transaction that is processing messages for a conversation group commits, the lock is released and the queue reader goes on to process the next message on the queue, which might be from a different conversation group. Messages in a given conversation group might be processed by many different threads during the life of the conversation group, but they are processed on only one thread at a time.

By default, there is a one-to-one correspondence between conversations and conversation groups. Each conversation group is a row in the sys.conversation_groups view. When a conversation endpoint is created, a GUID is generated and a row is inserted into the sys.conversation_groups view with the GUID in the conversation_group_id column. The conversation endpoint is made part of the conversation group by using the conversation_group_id value as a foreign key in the conversation_group_id column of the sys.conversation_endpoints view. Obviously, any number of conversation endpoints can be made members of a conversation group by using the conversation_group_id foreign key. When the conversation group is locked, the lock applies to all conversation endpoints related to the conversation group. To understand why locking a group of conversations is useful, think about a typical order-entry application implemented in Service Broker. When an order is received, the order processing logic might create dialogs to the inventory service, shipping service, credit check service, and accounts receivable service and send messages to all these services in the initial order transaction. These services will process the messages they received from the order service and send responses. The responses will arrive back on the order queue in a random order that is based on how long it took to process the message. If these response messages are processed by different threads, the order-processing logic will have to deal with responses being processed on different threads simultaneously. On the other hand, if all the dialogs related to a particular order are put into the same conversation group, receiving a response from any of the dialogs in the group will lock the group and ensure that other messages from dialogs in the locked conversation group will only be processed by the thread that holds the lock. Thus, all the conversations in the conversation group will be single threaded. This means that the logic that runs in a highly parallel multithreaded system can be written as a single-threaded application because Service Broker manages concurrency.

There are three ways to group conversations into a conversation group in the BEGIN DIALOG command. You can specify the conversation handle of a conversation already in the group, you can specify the conversation group ID of an existing conversation group, or you can use your own GUID to create a new conversation group. The method you choose depends on what you know at the time you begin the dialog.

For example, if you want to create dialogs from the manufacturing service to the inventory and PO services in the same conversation group, the commands would look something like this:

BEGIN DIALOG  @ManufacturingHandle
FROM SERVICE    [//microsoft.com/ManufacturingService]
TO SERVICE      '//microsoft.com/InventoryService'
ON CONTRACT     [//microsoft.com/Inventory/AddItemContract];
BEGIN DIALOG   @POHandle
FROM SERVICE    [//microsoft.com/ManufacturingService]
TO SERVICE      '//microsoft.com/POService'
ON CONTRACT     [//microsoft.com/Inventory/AddPOContract]
WITH RELATED_CONVERSATION = @ManufacturingHandle;

If the order service receives an order message and wants to begin a dialog to the inventory service in the same conversation group as the incoming order dialog, it would take the conversation group ID out of the incoming message and begin the inventory dialog like this:

BEGIN DIALOG  @POHandle
FROM SERVICE    [//microsoft.com/OrderService]
TO SERVICE      '//microsoft.com/InventoryService'
ON CONTRACT     [//microsoft.com/Inventory/CheckContract]
WITH RELATED_CONVERSATION_GROUP = @OrderGroupID;

This second syntax also allows you to make up your own conversation group ID. If the conversation group specified in the RELATED_CONVERSATION_GROUP parameter doesn't exist, a conversation group with that ID will be created. This approach allows you to use an existing GUID as the conversation group identifier. For example, if the order ID in your order database is a GUID, you can use it as the conversation group for dialogs related to that order. This makes relating incoming messages to the correct order simple. Be sure that the GUID you are using is unique, however. If it isn't, you might end up with unrelated dialogs in the same conversation group.

Sending and Receiving

Now that you have learned how to configure and begin a dialog, you're ready to learn how to send and receive messages on the dialog. The T-SQL command for sending a message on a conversation is SEND, and the command for receiving a message is RECEIVE. These command names can be misleading because there might or might not be a network involved in sending and receiving messages. The SEND command puts a message on a queue, and the RECEIVE command removes a message from a queue. In some cases, the message is transferred across a network to the destination queue, but if possible, the SEND command inserts the message into the destination queue directly and the RECEIVE command deletes the message from the same queue. Arguably, these commands should have been ENQUEUE and DEQUEUE, but most messaging systems use SEND and RECEIVE so these names were adopted.

The SEND command inserts a message into a queue. If the destination queue is available and is in the same database instance where the SEND command was executed, the message is inserted directly into the destination queue. Otherwise, the message is inserted into the transmission queue in the local database. The RECEIVE command dequeues a message from the destination queue. If the RETENTION option is OFF for the queue, the RECEIVE command translates to a DELETE … OUTPUT command that deletes the chosen rows from the queue and returns the deleted rows. If the RETENTION option is ON, the status column of the messages received is updated from 1 to 0 and the updated rows are returned. Because the RECEIVE is done in a database transaction, if the transaction rolls back the delete or update is undone and the messages are put back on the queue as if nothing had happened.

The SEND command needs only three parameters: the handle of the dialog to send on, the message type to use for the message, and the contents of the message. The dialog handle might come from a BEGIN DIALOG command, from a received message, or from the application state. If the supplied dialog does not exist or is in the DI, DO, ER, or CD state, the SEND will fail. One of the more common Service Broker errors is the one returned from a SEND on a closed dialog. The message type supplied must exist in the local database and must be allowed in the contract for the dialog specified. If no message type is supplied, the DEFAULT message type is used and the dialog must be using the DEFAULT contract. The message can be any SQL type that can be cast to VARBINARY(MAX). The SEND command does the CAST internally, so you can supply any compatible type. The message content can be supplied as a variable or as a literal value. Putting this all together, the SEND command looks like this:

SEND ON CONVERSATION @Dialog
MESSAGE TYPE [//microsoft.com/Inventory/AddItem]
(@message_body);

Notice that although a message has 14 columns, the SEND command has only three parameters. The other columns are obtained from Service Broker metadata based on the conversation_handle parameter. The conversation endpoint information is used to route and secure the sent message. It is important to remember that at its lowest level, a SEND is just an INSERT command. The major difference is that SEND uses the Service Broker metadata to fill in the required routing and security information from the established conversation associated with the SEND by the conversation endpoint parameter before the message row is inserted into the queue. The SEND command can be executed with the same tools as the INSERT command—ADO.NET, ODBC, OLE DB, Management Studio, SQL scripts, and so on. This means you can add Service Broker commands to your application without installing any new software or learning new APIs.

The syntax of the RECEIVE command is similar to the SELECT command. You specify the message columns you want to receive, the queue you want to receive from, and optionally a limited WHERE clause. Here's a typical RECEIVE command:

RECEIVE TOP(1)
@message_type = message_type_name,
@message_body = message_body,
@dialog       = conversation_handle
FROM dbo.InventoryQueue;

In this case, I chose to use the TOP(1) clause to receive only a single message. This is much less efficient than receiving multiple messages with each command, but the contents of a single message can be loaded into SQL variables for use in a stored procedure or script. A RECEIVE in a client-side program should retrieve all available messages as a record set.

The next section of the RECEIVE statement is the list of columns to be retrieved. It has the same syntax as a column list in a basic SELECT statement. In the example, only three columns are returned. These three columns are the minimum set of columns necessary to process a message. The message_type_name column indicates what kind of message has been received. You must always know the message type because even if the contract limits a service to a single incoming message type, error and end dialog messages might be received and must be processed appropriately. Using the message_type_id column instead of the name would be a little more efficient because an internal join is eliminated. However, there is no way to control the ID assigned to a message type, so using the ID isn't recommended unless you use only system message types that have stable IDs.

The FROM clause specifies which queue to receive messages from. The RECEIVE statement finds the oldest conversation group with messages available on the queue that is not already locked by another transaction. The command then locks the conversation group and uses a DELETE or UPDATE with OUTPUT command to retrieve the messages. Even if there are message on the queue from various conversation groups, only the messages from a single conversation group will be returned by a RECEIVE command. This approach ensures that a RECEIVE command will lock only one conversation group at a time, which improves parallelism. It is possible and, in many cases, desirable for a single transaction to hold multiple conversation group locks, but each RECEIVE statement locks only a single conversation group. If your program has done a lot of work to restore the state for a conversation group, receiving messages from another conversation group will require throwing that state away and retrieving the state for the new conversation group. If there are more messages on the queue for the original conversation group, it will be more efficient to retrieve them while you have the state loaded. To support this, you can specify which conversation group to receive messages from:

RECEIVE top(1)
@message_type = message_type_name,
@message_body = message_body,
@dialog       = conversation_handle
FROM dbo.InventoryQueue
WHERE conversation_group_id = @CGID;

If there are no messages from the specified conversation group on the queue, this statement returns no rows.

There are also rare circumstances when it make sense to send a message and then wait for the response from that message. This is normally a bad thing to do because it negates the advantages of asynchronous messaging. However, in some cases, the application needs to know that an action is complete before continuing. In this case, you can receive messages from a particular conversation:

RECEIVE top(1)
@message_type = message_type_name,
@message_body = message_body
FROM dbo.InventoryQueue
WHERE conversation_handle = @dialog;

The problem with the RECEIVE statement is that if there are no messages available on the queue, it returns immediately—just like a SELECT statement that finds no records. In some cases, this is the desired behavior because if the queue is empty, the application can do other things. On the other hand, in many cases polling the queue is a waste of resources. To use resources more efficiently, Service Broker allows you to wrap the RECEIVE statement in a WAITFOR statement. The WAITFOR statement allows the RECEIVE statement to return immediately if there are messages in the queue but wait until a message arrives if the queue is empty. The RECEIVE statement now looks like this:

WAITFOR (
RECEIVE top(1)
@message_type = message_type_name,
@message_body = message_body,
@dialog       = conversation_handle
FROM dbo.InventoryQueue
), TIMEOUT 2000;

The TIMEOUT clause defines when the WAITFOR statement should give up and return if no messages arrive. In this case, if there are no messages after 2000 milliseconds (ms), the statement will return no rows (@@ROWCOUNT = 0). If a message arrives before the timeout expires, the statement returns immediately. If the TIMEOUT clause is omitted, the statement will wait until a message arrives on the queue no matter how long it takes.

Important

The RECEIVE and SEND keywords must start the commands. Most SQL keywords that start commands—such as SELECT, INSERT, and UPDATE—are known to the parser as terminal keywords. This means that whenever the parser sees one of these keywords, it knows that a new command is starting. SEND and RECEIVE are not marked as terminal keywords because they are not part of ANSI SQL. To ensure that the parser knows that a new command is starting, the command before SEND or RECEIVE must end with a semicolon (;). The exception to this is a RECEIVE statement inside a WAITFOR statement.