11. SQL Server Service Broker
SQL SERVER SERVICE BROKER (SSB) is part of SQL Server and is used to build reliable, scalable, asynchronous, distributed data applications. With Service Broker, the developers of these applications can concentrate their efforts on the issues of the problem space in which they are going to be applied. The system-level details of implementing a messaging application are delegated to Service Broker itself.
Messaging Applications
Messaging applications are nothing new. Almost all large-scale enterprise applications use at least some messaging infrastructure. In general, messaging applications take a different approach from applications based on calling functions that return a result. When you use a message-based application, you send it a message and go on about your business. If you care, you might check some time later to see what, if any, effect the message you sent had, or you might become the recipient of a message that informs you of the status or effect of your message.
The technique of “send a message and go on about your business” has been around for a really long time. It used to be that you would walk to the computer-center window and drop off a message in the form of a tray of punch cards or magnetic tapes. A few hours later, you would check back at the window and probably receive a message in the form of a printout. Even today, many applications work by sending a file to a well-known network share and then check back later to see whether the file has been processed.
The “send a message and go on about your business” technique is the core of Service Broker for the same reason that it was used in providing computing services: It decouples the use of system resources from the overhead of allocating them.
Let’s do a quick thought experiment with the computer-center window that works the way applications that call a function and get a result work. Imagine that you go to the window after waiting in line for quite a while and hand your magnetic tape to the operator, who mounts it, runs your job, dismounts the tape, gets your printout and hands it to you, and then does that all over again for the next person in line. The usage of the computer center grows at some point to where the line of people waiting to hand the operator their jobs just keeps getting longer and longer. Adding another computer to the computer center won’t do any good, because the operator runs just one job at a time. The problem with this way of running the computer-center window is that the allocation of the resource—the computer system—is tightly coupled to its use.
Even applications that are not message based often have some message-based aspects to them. A simple example of this is an application that runs once a day and processes all the files in a particular directory. Messaging has been the original model for processing business data ever since there have been modern businesses. The interoffice mailing envelope used to send forms from one clerk to another for processing is an example. Over time, this was updated to send boxes of punch cards from one office to another, and then magnetic tapes. Now we send messages to services over networks. Services have replaced clerks, and messages have replaced forms, but the basic model and the applicability of decoupling data from the resources used to process it haven’t changed.
Two of the compelling features for using message-based services—and interoffice envelopes and forms, for that matter—are deferred processing and distributed processing. They are compelling because real-life business applications implicitly depend on these features:
• Deferred processing—It may not be possible, or even desirable, to perform all the work associated with a particular task at one time. A work order submitted to fix a piece of broken equipment can’t be fully processed until a work crew is assigned to it, has gone to the site of the equipment, fixed it, and then reported back. In fact, when the work order is received, the only thing it might make sense to do is to assign an order number to it and wait until the end of the day to assign a work crew to it, so that the assignment of work crews can be optimized over all the work orders.
• Distributed processing—The work associated with a task must be completed in a timely manner. Often, however, it is quite difficult to predict how many tasks there will be at a given time and how many resources each will take to be completed. Distributed systems allow processing resources to be applied where they are needed and to be expanded incrementally without changing the applications that use them.
Service Broker is a framework that manages the deferred processing of business tasks and allows system administrators to apply the resources in a distributed system so that the application can be developed as though all the resources it needs are available to it at all times. Deferred and distributed processing are compelling because they allow the development of an application to concentrate on the application’s problem domain and decouple it from the system details.
Figure 11-1 diagrams the typical process followed to fix a piece of broken equipment. A large company has its facilities spread out over several buildings that are known by their nicknames: Building 5, Parker Street, and the Scott Building. An equipment inspector in Building 5 finds that a satellite link is overheating. The inspector in Building 5 and sends a work order to Central Maintenance at the Parker Street building, requesting that the satellite link be repaired. At that point, the inspector immediately goes on to check other equipment; the inspector does not need to wait for a response from Central Maintenance. At the end of the day, the inspector might check to see whether the message he sent was processed.
Figure 11-1. Business transaction
The Parker Street building maintains all the equipment logs that keep track of the status of equipment. Central Maintenance immediately updates the logs to show that the use of the satellite link in Building 5 is limited because of equipment problems. This isn’t an emergency as long as the rest of the company knows that the use of the satellite link is limited. Later, in the evening, Central Maintenance prepares the next day’s work assignments for the repair teams. The next morning, it sends a work assignment to one of the repair teams on site at Building 5 to fix the satellite link.
Later that day, the repair team in Building 5 sends a completed work assignment to Central Maintenance that says the satellite link is fully operational. When Central Maintenance receives this message, it updates its equipment logs to show that the satellite link is fully operational again.
While all this was going on, the Operations group in the Scott Building was checking to see whether the satellite link in Building 5 is fully operational so they could use it instead of going to an outside vendor for the service.
Figure 11-1 shows a business process for fixing things that are broken that has been used in various forms since before computers, though the satellite link might be a bit of an anachronism. Forms, interoffice mail, and log books were used to implement the process. Each of the circles in Figure 11-1 would be a clerk who processed forms sent by interoffice mail. An equipment log would be a book or maybe a box of forms with current equipment status. Note that in its heyday, interoffice mail was being picked up and delivered hourly or even more often for critical operations like these.
The work order process shown in Figure 11-1 could be implemented in many ways today, of course. The “in” thing today would be to make the Central Maintenance, Equipment Logs, and Operations circles in Figure 11-1 Web Services and have the Inspection and Repair Team circles use Web applications to access them.
Web Services, queuing technologies, DCOM (Microsoft’s distributed component object model), or any of several other technologies provide reasonably straightforward ways of moving messages between services. What they don’t provide, but Service Broker does, is a straightforward way to manage deferred and distributed processing.
”Wait a second,” you might say. “Isn’t a queue the archetype for deferred processing and distributed systems?” Well, yes and no. The devil is in the “straightforward way to manage” details.
Almost certainly, any of these implementations would involve databases. Central Maintenance would keep track of work orders and equipment status in databases. The Operations group would also have kept track of its ongoing projects in a database. Even today, though, it’s quite possible that the Inspection and Repair teams might still be running on paper and wouldn’t have their own databases, but just use file-based applications to keep track of what they are doing. But in the end, the messages they send to Central Maintenance are used to update data in some databases.
The first step on the road to understanding what Service Broker brings to the table is the fact that virtually all business applications use databases, though possibly indirectly. Queuing technologies, DCOM, and so on end up acting as an intermediary between databases. In simplest terms, Service Broker cuts out the intermediaries and lets databases talk directly to one another.
Does this mean that Server Broker replaces all these other technologies? If all business operations could be reduced to the simple kind of diagram shown in Figure 11-1, the answer would be yes. But real-life business applications are much larger and have many more requirements to meet than that diagram implies, so the answer is no. Service Broker probably will be the best choice, however, for those operations that require deferred processing among databases located on different machines. Let’s start by looking at what Service Broker actually can do so you can decide on its applicability to your enterprise.
To get things started, we will send a message from the Inspection Team to Central Maintenance. Before we can do anything, we need a database to work with, so we will create one and name it Company. We should make sure that the database has a master key. A CREATE MASTER KEY WITH ENCRYPTION BY PASSWORD = 'P@ssw0rd' expression while Company is the database in use will do this. Chapter 6 discusses keys in more detail.
In Service Broker, messages are sent only from one service to another. A service is a new kind of database object that is created by the CREATE SERVICE statement. Every service must be associated with a queue. A queue is another new kind of database object created by a CREATE QUEUE statement. Messages sent to a service are stored in a queue associated with a service until they are processed.
Figure 11-2 shows the InspectionQueue being created (1) and then the InspectionService being created (2) and associated with the InspectionQueue. Messages sent to the InspectionService will be stored in the InspectionQueue.
Figure 11-2. Creating InspectionService
The metadata for services, queues, and other Service Broker objects can be seen in system views in the database they were created in; sys.services and sys.service_queues are the views used to see the metadata associated with service and queues. Figure 11-3 shows a query that joins sys.services (1) with sys.service_queues (2). The service we created previously and its associated queue (3) are shown in the results. Note also that some other services are shown in the results. The Event and Query Notification features of SQL Server 2005 are implemented using Service Broker.
Figure 11-3. Services and queues metadata
Service Broker handles messaging a bit differently from many other messaging frameworks. The primary messaging construct in Service Broker is not a message; it is a conversation. The BEGIN DIALOG CONVERSATION statement is used to initiate a conversation between two services. As the name implies, messages between services are sent via a conversation. Conversations are not meant to be used as static paths between services, though they could be viewed that way. Typically, a conversation is created for a particular task; then, when it is no longer needed, it is ended using the END CONVERSATION statement. In typical usage, there may be many simultaneous conversations between a given pair of services.
A message in Service Broker is a sequence of bytes. The size of a message is limited to 2GB. These bytes might be bitmaps, text encoded as bytes, or anything else that can be represented as a stream of bytes. Service Broker provides some extra capabilities when the bytes represent XML documents. In general, XML is the preferred way to send text, because it eliminates the need for the service at one end of the conversation to know what text encoding was used by the service at the other end of the conversation. Note that the encoding and collation used in XML are independent of any configuration setting in SQL Server 2005. Chapter 9 covers the XML data type, which is how Service Broker manages XML.
Figure 11-4 shows a Transact-SQL (T-SQL) batch that builds a conversation from the Inspection service to the Central Maintenance service. Each end of a conversation is identified by its conversation handle, which is a UNIQUEIDENTIFIER. In SQL Server, a UNIQUEIDENTIFIER is a globally unique identifier (GUID). Note that a GUID is just a 128-bit number that is generated by a randomizing algorithm.
Figure 11-4. BEGIN DIALOG CONVERSATION
The BEGIN DIALOG CONVERSATION expression (1) builds a conversation between a “from” and a “to” service. Every conversation has two endpoints, each identified by a conversation handle. One endpoint is attached to the “from” service. and the other is attached to the “to” service. The conversation handle for one endpoint of a conversation is used to send a message to the service attached to the other endpoint of the conversation.
BEGIN DIALOG CONVERSATION puts the conversation handle for the endpoint of the conversation attached to the “from” service into the variable that follows it; in this batch, that is @workRequest (4). Later, we will see where the conversation handle for the “to” endpoint is found and used.
The FROM SERVICE phrase (2) identifies the “from” service at one end of the conversation. Services do not have multipart names. The service identified InspectionService in this batch must already exist in the database in use.
The name of a service is stored as an NVARCHAR(128), but regardless of how collation is configured in the server, the name of a service is always case sensitive. Compares to and lookups of services are done with the exact bytes in the name of the service, not the characters. As we will see later, the name of a service may be passed between instances of SQL Server 2005. Byte-by-byte comparison of service names eliminates the need for one instance of SQL Server 2005 to know the collation of another instance of SQL Server 2005.
The TO SERVICE clause (3) in Figure 11-4 identifies the service at the other end of the conversation. At this point, you might think that the batch shown in Figure 11-4 would produce an error if it were run, because we have not yet created the CentralMaintenanceService, let alone identified its location. It is easy to think of a distributed application as being distributed in space; after all, the whole point of a distributed system is that it is spread out over many locations.
But distributed systems, like all real systems, are also distributed in time. The way a system looks today is not necessarily the way it will look a month from now, when a new office is added or a new server comes online. Service Broker does not expect a TO SERVICE to be in existence at the time a conversation is started or even, as we will see shortly, accessible when a message is sent to it. This is why the TO SERVICE name is a literal string—that is, in quotes. This makes it much easier to roll out or reconfigure a system because you do not have to synchronize perfectly the changes in all parts of the system. It is possible, of course, that the “other” system will never exist or is in error, but later in this chapter, we will look at several techniques an application can use to detect this.
Each database in an instance of SQL Server 2005 keeps track of the conversation endpoints associated with the services in that database. The sys.conversation_endpoints view, as shown in Figure 11-5, lists the conversations in the Company database. There is only one: the one we created in Figure 11-4.
Figure 11-5. Conversation endpoints
The SEND ON CONVERSATION statement (2) shown in Figure 11-6 sends a message. Messages are sent “on” the conversation handle that follows the ON CONVERSATION phrase—in this example, @workRequest. The message will be put into the queue of the service at the other end of the conversation. At this point, of course, the service at the other end of the conversation does not exist, but we will get to that soon.
Figure 11-6. Sending message
A SEND expression is required either to be the first statement in a batch or to be separated from the previous statement with a semicolon. Notice that the SET statement (1) in Figure 11-6 is terminated with a semi-colon.
Typically, the first message sent on a conversation is sent immediately after the BEGIN DIALOG CONVERSATION expression, but we have built the conversation in a different batch. The @workRequest variable (1) in Figure 11-6 is set with the conversation handle we found in Figure 11-5. This is an example; in typical usage, it is unlikely that you would want to hard-code a conversation handle.
The message itself (3) in the batch in Figure 11-6 is enclosed in parentheses after the conversation handle and must be of data type VARBINARY(MAX), but a string or XML will automatically be converted to VARBINARY(MAX).
Note that the message we are sending is an ASCII string that has been converted to VARBINARY (MAX). We could have chosen to send a Unicode string by using the N prefix on the literal string. As long as the other end of the conversation interprets the VARBINARY(MAX) byte stream it receives as XML, we need not worry about how the string is encoded, because XML can always detect and properly interpret the encoding of a stream of bytes. There are some caveats to this, but none is of any significance for SQL Server 2005.
We still are playing a bit fast and loose with the way we are moving data from one service to another without defining what it should look like, but only to keep this example as simple as possible. Later, we will see that we can define MESSAGE TYPEs and CONTRACTs to put much tighter constraints on the formats of messages sent between services.
The batch in Figure 11-6 appears to be like the one in Figure 11-4, in that it seems like it should not work because the service at the other end of the conversation, CentralMaintenanceService, does not exist. Whenever a message is sent to a service that cannot be found, or that can be found but exists on a different instance of SQL Server 2005, the message is sent to the transmission queue in the database of the service that tries to send the message. This is an important feature, because it means that Service Broker does not depend on having connections available when messages are sent.
The transmission queue is visible through the sys.transmission_queue system view in the database of the “from” service of the conversation. The SELECT expression (1) shown in Figure 11-7 shows that the transmission queue is holding a message that was sent from InspectionService to CentralMaintenanceService (2) because CentralMaintenance Service could not be found.
Figure 11-7. Contents of sys.transmission_queue
Note that when the system does not seem to be behaving properly, you might consider running as a diagnostic a job that checks the transmission queue to see whether it is unduly full.
Internally, a Service Broker queue is a table, but it is handled in a special way. A SELECT expression can be used to look at the content of a queue, and a new kind of expression in SQL Server 2005, the RECEIVE expression, can be used to take things out of a queue. Even though a queue is internally a table, using INSERT, UPDATE, or DELETE on one will raise an error. Note that sys.transmission_queue is a queue in the architectural sense, but it was not created using CREATE QUEUE and may not be used to RECEIVE expressions.
So even though there is no CentralMaintenanceService, at least Service Broker hasn’t lost our message, but it didn’t tell us that the TO SERVICE wasn’t there either. Let’s think again about what would happen if a SEND expression raised an error when it could not find the TO SERVICE. If you ran a batch to SEND a message, and it raised an error saying that it couldn’t find the TO SERVICE, what would you do? You probably would retry the SEND. After all, it may have failed due to some sort of intermittent network issue.
Service Broker has a built-in algorithm for retrying anything that is in the sys.transmission_queue. If the transmission to the TO SERVICE fails, it will try it again in about 4 seconds and then again about 8 seconds after that. It will keep on doubling the time intervals between attempts to transmit the message until the interval becomes about 60 seconds. Then it will continue to retry once a minute until you stop it. Service Broker will never lose a message and never stop you from sending one.
Now we will add the Central Maintenance service and see what happens to the message stored in sys.transmission_queue. Figure 11-8 shows a batch to make the Central Maintenance service. It is almost the same as the one that we used to make the Inspection service. First, we create the CentralMaintenanceQueue (1). Then we create the CentralMaintenance Service (2) on that queue.
Figure 11-8. CentralMaintenanceService
The difference for CentralMaintenanceService is that a service that is used as the TO SERVICE in a BEGIN DIALOG CONVERSATION expression must specify the format of the messages it is willing to accept. The messages a service is willing to accept are specified in a CONTRACT. We will look at the details of using CONTRACTs later in this chapter, but for now, we will use the built-in DEFAULT CONTRACT which says in effect that the service will accept any sequence of bytes as a message. Technically, it says that it will accept messages of the DEFAULT type, but we will cover message types when we get to CONTRACTs.
In Figure 11-8, the name of the contract (3) is in parentheses just after the name of the queue associated with the service. The CentralMaintenance Service accepts messages that comply with the DEFAULT contract—that is, any message of the DEFAULT type. Note that the name of the contract is in square brackets, because DEFAULT is a keyword in T-SQL and DEFAULT happens to be the name of the contract that CentralMaintenance Service uses.
It won’t happen right away, but eventually, Service Broker will try to send the message in the transmission queue for CentralMaintenance Service as part of the retry algorithm explained earlier in this chapter, and it will be successful. A message sent to a service is put into the queue associated with that service. One of the columns of a queue is the message_body column, which is of type VARBINARY(MAX) and contains the actual message. The query in Figure 11-9 casts the message_body (1) from the CentralMaintenanceQueue (3) as XML. The results (3) show the message that was sent to the CentralMaintenanceService by the InspectionService.
Figure 11-9. CentralMaintenanceQueue
One of the often-unrecognized values of XML is that the encoding of its text is embedded in the XML itself. This means that the recipient of an XML-based message never needs to be told what the encoding is. If, however, the message body is one of the character types from SQL Server 2005—that is, CHAR, NCHAR, VARCHAR, or NVARCHAR—there is no way for the recipient of the message to know how to cast the message body to read it. This means that enterprisewide conventions, configuration messages, or some other means will have to be used and maintained so that recipients of character-based messages will know how to cast them to turn them back into characters.
On the other hand, always using XML for text messages, even when they don’t need the structure that can be added by XML, means that the recipient of the message need not know what the SQL Server 2005 character type is or, in the case of CHAR and VARCHAR, what code page was used in that message. Figure 11-10 shows the kind of error that can occur when the recipient of a message chooses to cast it to the wrong character type. Note that the example in Figure 11-9 did not need to know anything about the encoding of the message to turn it into text.
Figure 11-10. NVARCHAR(MAX) message_body
Service Broker is used to build distributed system, and using XML for all text messages means that one system will not have to tell other systems what encoding or code page to use to read the messages it sends. That information will be part of the XML itself.
Processing Messages
We have seen that we can send messages from one service to another and not have to worry very much about intermittent faults or whether the service has been configured yet. The next thing to do is process the message. The act of processing a single message is fairly straightforward, and we will do that now to illustrate the syntax and operation of the RECEIVE expression and the typical way it is used.
But before we write the code to process the message, we should define the processing requirement for it. To keep things simple, we will do two things: move the message into a table that keeps track of pending work orders, and send a message back to the sender with a timestamp added to it so the sender will know when the work order was processed.
We do not need to design a proper database just to show how messages are processed, so the pending work table will have just a single XML column to store messages. A simple CREATE TABLE PendingWork (work XML) is sufficient for our example.
Syntactically, RECEIVE is the similar to SELECT in that it has a list of columns, a FROM clause, and an optional WHERE predicate. Like a SELECT statement, it can also use a TOP expression to limit the number of rows returned. The FROM clause of a RECEIVE must specify a queue. It cannot be used with a table.
The columns the RECEIVE specifies come from a queue. A queue has 15 columns, but 4 are enough to do simple message processing, conversation_ group_id, conversation_handle, message_body, and message_type columns. This example will use only two of them, but all the columns are documented in Books Online. We will make use of the other columns in later examples.
Figure 11-11 shows a batch that processes a single message from the CentralMaintenanceQueue. The first thing to note is that the RECEIVE expression and the processing of the message are run under a common transaction (1). This is not necessary, but processing of messages is typically done this way. Queues are transactional; after all, they are tables. This batch is not doing any error checking, which it should, but doing so would not keep the example as simple. If the batch did detect an error, it could roll back the transaction, which would put the message back into the queue so that the message could be processed later.
Figure 11-11. Receiving a single message
The two columns of interest for this batch are the message_body and conversation_handle columns. The message_body is, of course, the message itself, and it is captured by the @message variable. The conversation handle is for the endpoint of the conversation that is attached to the CentralMaintenanceService.
The conversation handle is captured in the @conv variable (2) and is later used to send a reply (5) to the sender of the message the batch is processing. This is an important feature of Service Broker; the batch that processes a message does not need to know the identity or location of the service that sent the message it’s processing to send a reply to that service.
The batch in Figure 11-11 reads the message out of the CentralMaintenanceQueue using a RECEIVE statement (3). Service Broker guarantees that messages that are sent on a given conservation will be received exactly once and received in the order in which they were sent. Note that this guarantee applies only to messages within a given conversation, not across multiple conversations.
A RECEIVE is also similar to a SELECT in that it sets the @@ROWCOUNT variable with the number of rows received. The RECEIVE expression, as it is written in this batch, will return immediately with @@ROWCOUNT = 0 if the queue is empty.
A timestamp is applied to the message when it is received. The @ts variable in the batch contains the current time as a string. The XQuery modify function is applied to the @message variable to insert a “received” attribute into the document element of the message. See Chapter 9 for details on using the XML data type and XQuery in SQL Server 2005. In brief, the /* always means the documents element of an XML document—that is, the first element in the document, which is sometimes called the root element. The insert attribute in the modify function says to add the timestamp as an attribute named 'received' to this element.
After the message has a timestamp, it is sent (5) as a reply back to its sender; last, the message itself is inserted into the PendingWork table.
We can see the results of the batch from Figure 11-11 in Figure 11-12. The InspectionQueue (1) has the original message with a timestamp in it. The message appeared in the InspectionQueue because the Central-MaintenanceService sent a reply to the message sent by the Inspection Service.
Figure 11-12. Results of processing messages
The conversation_handle for the message is the same one that was originally used to send the message from InspectionService to Central-MaintenanceService in Figure 11-6 and can be used to correlate messages if need be. Note that the conversation_handle identifies the endpoint of a conversation, not the conversation itself. Each endpoint of a conversation has a different conversation_handle. The PendingWork (2) table also has the message that was inserted into it.
So far, we have looked at the basics of using Service Broker. To recap where we are now, we have made two services, InspectionService and CentralMaintenanceService, in a single database named Company. Each service has an associated queue, InspectionQueue and Central-MaintenanceQueue, respectively. We can build a conversation between two services and use that conversation to send messages, with guaranteed delivery and order, between them.
Messages sent to a service are put into the queue associated with that service. All messages include a conversation handle that can be used to send a reply to the sender of the message. The conversation handle can be used to correlate messages. We saw that sending messages is asynchronous and that connectivity is not required to build conversations or send messages. Listing 11-1 puts together all the elements we have just covered.
Before going further, consider a general note on syntax. SQL Server 2005 and Service Broker in particular introduce several new expressions to T-SQL. Some of the expressions used for Service Broker require a semicolon (;) to separate them from preceding statements if they are not the first statement in a batch. The expressions that require this are SEND, RECEIVE, MOVE CONVERSATION, and GET CONVERSATION GROUP.
Also, the part of Listing 11-1 from Figure 11-6 shows a hard-coded GUID. It is from the part from Figure 11-4 that uses BEGIN DIALOG CONVERSATION to get a conversation endpoint.
Listing 11-1. Messages between services
CREATE DATABASE Company
GO
ALTER DATABASE Company
SET ENABLE_BROKER
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO
use Company
GO
-- Figure 11-3
CREATE QUEUE InspectionQueue
CREATE SERVICE InspectionService
ON QUEUE InspectionQueue
GO
-- Figure 11-3
SELECT S.name AS Service, Q.name AS Queue
FROM sys.services AS S
JOIN sys.service_queues AS Q
ON Q.object_id = S.service_queue_id
GO
-- Figure 11-4
DECLARE @workRequest UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @workRequest
FROM SERVICE InspectionService
TO SERVICE 'CentralMaintenanceService';
SELECT @workRequest;
GO
-- Figure 11-5
SELECT conversation_handle FROM Company.sys.conversation_endpoints
GO
-- Figure 11-6
DECLARE @workRequest UNIQUEIDENTIFIER;
SET @workRequest = '6AF33B51-EE4A-DA11-91C9-00904B8B6392';
SEND ON CONVERSATION @workRequest
('<WorkRequest>
<Equipment>2-37-BK</Equipment>
<Reason>Overheat</Reason>
</WorkRequest>'
);
GO
-- Figure 11-7
SELECT from_service_name,
to_service_name,
transmission_status
FROM Company.sys.transmission_queue
-- Figure 11-8
CREATE QUEUE CentralMaintenanceQueue
CREATE SERVICE CentralMaintenanceService
ON QUEUE CentralMaintenanceQueue
([DEFAULT])
GO
-- Figure 11-9
SELECT CAST(message_body as XML)
AS Message
FROM CentralMaintenanceQueue
GO
-- Figure 11-10
SELECT CAST(message_body as NVARCHAR(MAX))
AS Message
FROM CentralMaintenanceQueue
GO
CREATE TABLE PendingWork
(
work XML
)
GO
-- Figure 11-11
BEGIN TRAN
DECLARE @message XML;
DECLARE @conv UNIQUEIDENTIFIER;
DECLARE @ts VARCHAR(MAX);
SET @ts = CAST(GETDATE() AS VARCHAR(MAX));
RECEIVE TOP (1)
@message = CAST(message_body AS XML),
@conv = conversation_handle
FROM CentralMaintenanceQueue;
IF @@ROWCOUNT = 1
BEGIN
SET @message.modify('insert (
attribute received{sql:variable(''@ts'')}
) into (/*[1])'),
SEND ON CONVERSATION @conv
(@message)
INSERT INTO PendingWork VALUES (@message)
END
COMMIT TRAN
GO
-- Figure 11-12
SELECT * FROM PendingWork
SELECT CAST(message_body as XML) FROM InspectionQueue
GO
Business Transactions
The preceding example and discussion deliberately skipped many of the features of Service Broker so that its basic mechanics would be clear. In fact, from what we have seen so far, Service Broker is not a whole lot different from any other form of messaging framework. The additional features we are going to look at now show the main strength of Service Broker: its ability to manage business transactions.
A business transaction is an activity that has been part of the business process since long before computers existed. An example of a business transaction is the purchase of parts by a manufacturer from a vendor. The business transaction starts when the vendor receives a purchase order. Some work is required to ensure that the manufacturer’s credit is good and that the items ordered are still being sold. Then there might be some work to set up a manufacturing process, followed by other work to ship the parts, send a bill, and so on. Almost all these pieces of work take time. A business transaction usually has a long, indeterminate lifetime, stretching into days, months, or even years. Typically, many independent people and databases are involved in it. In other words, a business transaction consists of many somewhat-independent pieces of work spread out over time and space.
SQL Server 2005, like all relational databases, supports database transactions. A database transaction is different from a business transaction. A database transaction is sometimes called a unit of work, and this term is a bit more descriptive of what a database transaction is. A transaction is some work that is done as a single unit. Everything the transaction does is described in a single place in the transaction.
A database transaction has the properties of being atomic, consistent, isolated, and durable—that is, the ACID properties. We will not discuss what each of these properties means, but taken together, they ensure that database transactions are guaranteed, by mathematics, to maintain the integrity of a relational database. In practice, this typically means that a database transaction must be finished in a fraction of a millisecond, and at its completion, it will either be committed or rolled back.
A business transaction is implemented as a series of related database transactions. Each database transaction is, of course, independent of other database transactions. This series of database transactions is sometimes called a long-running transaction or sometimes a saga to distinguish it from a database transaction. The term long-running transaction is a bit conflicted; there is no way that a series of independent transactions can be ACID, and for this reason, some will object to this term. For clarity, this chapter will use the term transaction to mean to a database transaction and the term saga, rather than long-running transaction, for the series of database transactions that makes up a business transaction.
A transaction is easier to deal with than a saga. A transaction starts and then finishes by either committing or rolling back; then you are done with it. A saga will do a transaction when it starts—to insert a new purchase order into a database, for example. Many systems would assume, for the sake of efficiency, that the purchase order would be approved and do some other transactions that might, for example, take out of inventory some parts the vendor will need to fulfill the purchase order.
The saga that implements a business transaction can fail. The customer might cancel the order after the saga has started, for example. Unlike a transaction, which can be rolled back and undo everything it did, a saga cannot be undone. A saga must be designed so that if it fails, its effects can be compensated. Compensation is not the same as rolling back a transaction. A failed saga for a purchase order that initially took parts out of inventory could compensate by putting those parts back into inventory. It could not compensate, however, for the fact that another purchase order was rejected because the parts needed to fulfill it were not available from inventory, so the order subsequently was canceled.
Service Broker provides straightforward solutions to several programming issues that usually crop up in implementing a business transaction as a saga. The design of the application has to take these issues into account; in general, they are managed in Service Broker with little or no coding on the part of the application:
• Concurrent processing—An application often runs more than one thread of execution at the same time to use system hardware more effectively; to manage independent items of work logically; or to allow some work to progress while other work is blocked, such as waiting for a disk access to complete. Increasing the concurrency of applications can lead to doing more work in less time.
Resources, such as queues and other pieces of state, are shared among all the threads of execution in the application and, if not properly managed, will lead to corrupting a shared resource. This is sometimes called the synchronizer problem and is very difficult to prevent. Databases manage this problem through the use of transactions by automatically applying locks as needed to achieve a desired level of transaction isolation. Transaction isolation ensures the integrity of the database but also has the undesirable effect of decreasing the concurrency of an application.
Service Broker manages the issues associated with concurrent processing with minimal negative impact on concurrency.
• Preserving message order—It usually is much easier to write an application that receives messages in the order in which they were sent to it. In general, messages cannot be depended on to arrive in the order in which they were sent, and they may not arrive at all. Applications must somehow take this into account. Service Broker ensures that messages are received, and received in the order in which they were sent in a conversation.
• Correlating messages—Applications often receive replies to messages that they have sent. The replies to these messages may be delayed, occurring even weeks or years later, and they rarely arrive in the order in which the messages that caused them were sent. The message the application receives may be related to a specific message that it sent or just in general to a particular saga it is working on.
An application, while processing a business transaction, might send many messages, one of which says, “Order part 123.” Later, it might receive a message that says, “Part 123 no longer available.” The application needs some way to determine whether this message is related to the “Order part 123” message, some other message, or to a saga. Service Broker can be used to manage the correlation of replies with the messages and sagas that caused them.
• Processing serialization—Receiving messages in the order in which they were sent is not the same as processing messages in the order in which they were sent. In a multithreaded environment, for example, it is possible for one thread to receive a message but not complete its processing until after a second thread finishes processing a message received after the first message.
Sometimes, processing messages in the order in which they are received is not important, and sometimes, it is. An application may receive a message that says, “Start a new purchase order,” followed by several messages that say, “Add this line item to the purchase order.” The “Start a new purchase order” message must be processed before any line items can be added to it. Service Broker can guarantee the serial processing of messages over a reasonably arbitrary set of messages.
• Compensation—Compensation is the process of undoing the effects of a business transaction that fails. There are several ways to implement compensation. One way is for each transaction in a saga, in addition to its other work, to add an instruction to a list of “undo” instructions. If the saga fails, the application runs the list of undo instructions to compensate for the effects of the failed saga.
Service Broker takes a different approach to compensations. Optionally, it will retain all the messages associated with a saga until the saga is finished. If the saga finishes by failing, the applications can leverage the fact that Service Broker can preserve message order, correlate messages, and ensure the serial processing of messages to implement compensation. In effect, the “undo” list is a list of all the messages in the saga that were processed, in the order in which they were processed, up to the point of failure.
Next, we are going to look at the features of Service Broker that make these issues fairly easy to deal with. Note that the order of explanation of these features that follows is not necessarily the order in which they would be used to build a system. The listings that follow these discussions illustrate the use of the topics in an appropriate operational order.
Service Programs
Messages that end up in a queue must at some point be read and processed. The code that processes them is generically called a service program. It can be a stored procedure that is executed, though it could be an ad hoc script or even a program running outside SQL Server 2005 that has a connection to it.
The service program may be executed on an ad hoc basis, on a regular schedule, or in response to other events in the overall system. In some cases, the same service program is always used with a given queue, and sometimes, several service programs are used with a given queue.
The main difference among these service programs is what causes them to be executed. It might be that, using the work-order-processing system discussed at the beginning of this chapter as an example, a clerk might run a particular stored procedure as he left work for the day. The stored procedure would read all the new work orders out of the CentralMaintenanceQueue and put together the work crews for the next day. And of course SQL Server Agent could be used to automatically schedule the stored procedure to run on a regular basis.
In other cases, events in the system might trigger a service program to read and process the messages in queue. Some companies that deal with equipment that must sit outside in the weather, for example, have systems that automatically respond to computer-based weather reports. Part of the response might be to execute a service program to read and process the messages in the CentralMaintenanceQueue as part of preparation for a storm.
Theses are all examples of batch processing. At some time of day or in response to an event, queues are read, and their messages are processed; Service Broker supports this, of course. Another way to process messages is to do the processing continuously—that is, process the messages as they arrive. Service Broker supports this, too. Service Broker activation procedures are a mechanism for processing messages continuously. Sometimes, this is called internal activation because the activation procedure is a stored procedure inside the database that is executed by Service Broker. Later, we will look at external activation where a Service Broker is used to activate a program that is outside SQL Server 2005. Most systems will have a mixture of batch processing and continuous processing.
We processed messages in the example that started in Figure 11-2 and was illustrated in Listing 11-1 by executing a batch that read a single message from a queue. To get the effect of continuous processing, we could put the batch in Figure 11-11 inside a loop and just let it run forever, or we could have it submitted by SQL Agent on some schedule to process messages as they arrive.
Though functionally, these solutions would work, both would add a large amount of unnecessary overhead; batches would run when there was nothing in a queue for them to read, taking away resources from batches that had messages to process. And neither would adapt to changing load by giving more resources to queues that were filling up faster than others. Both of these techniques would have trouble scaling to process a large number of messages because of the unnecessary overhead they would incur.
Ideally, for continuous processing, we would like the batch that processed messages for a queue to be executed magically when messages appeared and to not be running at all when the queue was empty. In fact, we would like multiple instances of the batch to start up when a single instance could not process messages in the queue fast enough. In other words, we would like our batches to be running only where there are messages in queues and otherwise to be completely out of the way. Activation procedures have exactly this behavior.
An activation procedure is a stored procedure invoked by Server Broker to process messages as they appear in a queue. Later in this chapter, we will look at the way Service Broker uses activation procedures, but first, we will look at the syntax for associating an activation procedure with a queue.
Note that a queue is not required to have an activation procedure, but it is the preferred technique when continuous processing of messages is required. A queue may have at most one activation procedure, but a stored procedure may be used as the activation procedure for more than one queue. Later, we will see that the information in a message is sufficient for a stored procedure to know which queue a message came from.
An activation procedure can be associated with a queue when the queue is created or altered. Figure 11-13 shows the CREATE QUEUE expression being used to create a queue with an activation procedure. The CREATE QUEUE expression has an optional WITH ACTIVATION (1) clause that designates a stored procedure as the activation procedure for the queue being created. The PROCEDURE_NAME clause (2) must specify an existing stored procedure. The MAX_QUEUE_READERS clause (2) specifies the maximum number of instances of the activation procedure that Service Broker is allowed to run at the same time for this queue. In general, the higher the MAX_QUEUE_READERS setting, the more throughput there will be in reading messages from the queue, at the cost of using more system resources. Note that the ALTER QUEUE statement, described in Books Online, can be used to change this number while an application is running, meaning that maximum concurrency associated with processing messages in this queue can be increased or decreased dynamically as needed.
Figure 11-13. Activation procedure
Figure 11-14 diagrams a queue with messages flowing into it and an associated activation procedure. Service Broker can detect that a message has been put into a queue; after all, it put the message there. When Service Broker puts a message into a queue, if it has not already done so, it will execute the activation procedure associated with that queue, with no parameters. Service Broker expects that the activation procedure will read the message from the queue.
Figure 11-14. Continuous message processing
In effect, Service Broker monitors the depth of the queue—that is, the number of messages in it—over time. If it sees that the queue isn’t being empted by the activation procedure it previously executed, it executes the activation procedure again, with no parameters, provided that there are fewer than MAX_QUEUE_READERS instances of the activation procedure for that queue already running.
It’s a reasonable model to think of Service Broker executing more and more copies of an activation procedure until the depth of the queue stops growing. In fact, Service Broker does not actually measure the depth of the queue; that would be inefficient. Instead, it monitors events that happen to queues—a message being put into a queue, a conversation group being unlocked, and a RECEIVE expression returning no rows—to determine whether the queue requires the execution of another instance of the activation procedure. Note that conversation groups are a construct related to conversations that we will be discussing later in this chapter.
Service Broker does not manipulate the queue itself in any way; it expects that the activation procedure will do that. All Service Broker does is keep on executing more instances of the activation procedure as long as it sees that messages in the queue are not being processed fast enough, up to a limit configured for the queue. Service Broker is in effect automatically increasing the processing concurrency of the queues that need more concurrency.
The algorithm that Service Broker uses to monitor queues is not exposed, and there is no way to configure it. Every few seconds, it checks the queues to see whether any activation procedures need to be executed. The result is that an application can adapt automatically to the message load presented to it.
An activation procedure can be enabled or not. Disabling an activation procedure prevents Service Broker from executing it. By default, the WITH ACTIVATION clause of the CREATE QUEUE statement enables the activation procedure. The STATUS clause of the WITH ACTIVATION clause determines whether the activation procedure is enabled. Figure 11-15 shows a queue that is initially created with an activation procedure disabled (1). Later, an ALTER QUEUE expression can be used to enable (2) the activation procedure.
Figure 11-15. Enabling activation procedure
Note that because ALTER QUEUE can be used while an application is running, processing of messages for a particular queue can be turned on or off as needed. Turning off the processing of messages in effect leaves more computing resources for other queues.
When an activation procedure for a queue is turned off, the activation procedure is not executed, and messages just pile up in the queue until the activation procedure is turned back on or some other batch reads the queue. Sometimes, it is useful to turn off the queue itself and not allow any message to enter it. By default, when a queue is created, it is enabled. Figure 11-16 shows the use of the STATUS option for a queue to create it in an off (1) state. The queue can be enabled or disabled at any time by using the ALTER QUEUE expression with a STATUS option (2).
Figure 11-16. Enabling queue
It might seem that turning off a queue is a drastic thing to do; will messages be lost? Service Broker does not lose messages even when queues are turned off. In Figure 11-6, we saw that we could send a message to a service and queue that did not even exist and not lose the messages. When a queue is turned off, messages destined for it will wait in transmission queues, as we saw in Figure 11-7.
Almost all database applications adjust computing resource usage based on time of day or other events, sometimes automatically and sometimes manually. The simplest form of this kind of control is to submit at night a batch that you don’t want using resources in the daytime. Sometimes, you have to make adjustments manually by stopping some batches and starting others due to unforeseen events. Activation procedures give you a way to use all available resources automatically without giving up the ability to make manual or automatic adjustments to resource usage.
What we have just looked at is sometimes called internal activation because the code that processes the messages in a stored procedure inside the database. It is also possible to use external activation to process messages that use a procedure outside the database to process messages. When a queue needs to be processed, Service Broker generates a QUEUE_ACTIVATION event. An Event Notification created with a CREATE EVENT NOTIFICATION expression can be used to handle this event. See Chapter 7 for a discussion of Event Notifications. Search for “Event-Based Activation” in Books Online for a discussion of using Event Notifications in external applications.
Conversations
In Figure 11-4, we began a conversation and then proceeded to use that conversation to send messages. Service Broker uses conversations to preserve message order—which is to guarantee that messages are received in the order in which they were sent—in a conversation.
A conversation is begun between services. Every conversation has two endpoints; each endpoint is associated with a service and, indirectly, with its associated queue. Messages are sent on a conversation, not to a queue or services, as shown in Figure 11-6. Specifically, the ON CONVERSATION clause in a SEND expression specifies a conversation_handle, which is a GUID that represents a conversation endpoint. Messages sent on a conversation endpoint appear in the queue at the opposite endpoint of the conversation in the order in which they were sent.
Figure 11-17 illustrates a conversation preserving message order. The left endpoint of the conversation is identified by a GUID that starts with F93B, and the right endpoint of the conversation is identified by a GUID that starts with 0A8F. The batch shown on the left side of the illustration uses the SEND expression to send, in order, messages M1, M2, M3, M4, and M5 on conversation endpoint F93B. They appear in the queue at the other end of the conversation in the order in which they were sent. Likewise, the batch on the right side of the illustration sends five messages that appear in the queue on the right side of the illustration in the order in which they were sent.
Figure 11-17. Conversation preserving order
We saw in Figure 11-5 that conversation endpoints are visible in the sys.conversation_endpoints system view in a database. The conversation_handle specified in the ON CONVERSATION clause are scoped to the database in use and must be in the conversation_handle column of the sys. conversation_endpoints system view for the database in use when the SEND expression is executed; otherwise, an error will be raised.
Conversations are meant to have a limited lifetime, even though that lifetime might be measured in months or years. The END CONVERSATION expression is used to end a conversation.
A conversation may be active or inactive. A conversation is active from the time it is begun until an END CONVERSATION expression has been used on both ends of the conversation, at which point it becomes inactive.
The END CONVERSATION expression may used to end an active conversation only by the owner of the conversation, a user in the role of the owner of the database, or a user in the sysadmin role. Note that a conversation is just another database object and is owned by the identity that created it using a BEGIN DIALOG CONVERSATION expression.
There are three reasons to end a conversation. One is the normal end of conversation that occurs when the conversation is no longer necessary. A second reason is that an error has been detected, and it is not possible to continue the conversation.
The third reason to end a conversation is as a maintenance operation performed because a conversation that should have ended has not, and it must be cleaned up, administratively, to free system resources. Note that conversations that are not active do not take up memory resources, but they do take up nonmemory resources, such as disk and backup systems. Typically, a conversation is cleaned up administratively because there is reason to believe that it will never end, in which case it is a resource leak.
To bring a normal end to a conversation, the END CONVERSATION expression is used with the conversation_handle (2) of the conversation being ended, as shown in Figure 11-18. This example starts a conversation, sends (1) a message on that conversation, and then ends (2) the conversation. In this case, any pending messages for the other end of the conversation—those sent with SEND (1) before the END CONVERSATION—are sent to the other end of the conversation, followed by a message with a NULL message_body and a message type of http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog (3). Any messages still in the queue from the other end of the conversation are deleted with no warning. In addition, any messages that subsequently arrive from the other end of the conversation will be silently thrown away.
Figure 11-18. Normal END CONVERSATION
The recipient of an http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog message must in turn use END CONVERSATION to end its side of the conversation. This allows Service Broker to clean up the database resources associated with the conversation.
A conversation can be ended with an indication that an error has occurred. Figure 11-19 shows an END CONVERSATION expression that includes a WITH ERROR clause (1). The WITH ERROR clause requires an error number, which must be positive, and an error description. Negative error numbers are reserved for Service Broker itself to use. This works in the same way as an END CONVERSATION expression without a WITH ERROR clause, except that the message to the other end of the conversation (2) is of message type http://schemas.microsoft.com/SQL/ServiceBroker/Error, and the message body contains the error information. The recipient of this message should respond to it with an END CONVERSATION, as it would for a normal end of conversation. Note that this example shows a hard-coded GUID being used for @workRequest. In normal practice, this would not be done, but it is done in this example just to make it complete.
Figure 11-19. END CONVERSATION with error
To clean up completely all the resources associated with a conversation, both ends of the conversation must use the END CONVERSATION statement. Sometimes, due to an equipment failure or programming error, this does not happen, and the resources associated with a conversation must be cleaned up forcibly. Adding a WITH CLEANUP phrase to the end of an END CONVERSATION expression will forcibly clean up the resources associated with a conversation and not send any messages to the other end of the conversation and will drop all pending messages from the other end of the conversation. This is not the recommended way to end a conversation and should be used only as a last resort. An END CONVERSATION expression that includes the WITH CLEANUP phrase may be executed only by a user in the role of sysadmin or owner of the database that contains the conversation.
Note that typically, equipment failure is handled by Service Broker by just waiting for the broken equipment to come back online, at which time conversation can go on running. In some cases, when the equipment never comes back online, the WITH CLEANUP phrase will be needed to free up resources.
Sometimes, there is a need to clean up forcibly all resources associated with all conversations in a database. The ALTER DATABASE <database> SET NEW_BROKER expression will do this. Note that this expression will not complete as long as there are any users of the database being altered. This empties all queues, ends all conversations, drops any pending messages without sending any messages to the other end of the conversations that are dropped, and creates a new GUID to identify the broker instance. We have not yet discussed the broker-instance GUID, but this is used by conversations to communicate with services across databases, and creating a new broker-instance GUID will doom any conversation from another database. SET NEW_BROKER can be quite useful during development, but it should be used only as a last resort in a production setting.
The SEND expression, by design, is asynchronous. A byproduct of this is that it does not inform the sender whether the message was delivered to the other end of the conversation. Timeouts are used with asynchronous messages both as part of normal message processing (sending a message like “I’ll accept bids for the next 20 minutes,” for example) and to detect errors and failures. Service Broker provides two kinds of timeouts: the lifetime of a dialog and a conversation timer.
All dialogs have a lifetime, which optionally may be specified by the use of a LIFETIME clause (1) appended to the BEGIN DIALOG CONVERSATION expression, as shown in Figure 11-20. The LIFETIME clause specifies the lifetime of the dialog in seconds. If the LIFETIME clause is not used, the lifetime is the maximum value of the int type, in seconds. If the dialog has not been ended by both ends of the conversation using END CONVERSATION before the lifetime expires, a message (2) is sent to both ends of the conversation indicating that the lifetime of the dialog has expired.
Figure 11-20. Dialog lifetime
When the lifetime of a dialog has expired, it may no longer be used. Attempts to send messages using it will fail with an error. The conversation for the dialog still exists, however, until both ends of the conversation have executed an END CONVERSATION expression for the conversation.
The lifetime of a dialog is a big hammer; when it has expired, you can no longer use the dialog. An alternative to the lifetime of a dialog is the conversation timer. A conversation timer is a timer that sends a message to the conversation endpoint that set it when it expires. It has no other effect on the conversation.
The purpose of a conversation timer is to act as a reminder that a dialog has been inactive for longer than you typically expect it to be. When you receive a message that a conversation timer has expired, you take some sort of corrective action. You might send a message to the service, saying, “Auction has begun and will end in 10 minutes,” and also set a conversation time for ten minutes. If you receive a conversation timeout message, your corrective action might be just to end the conversation.
Figure 11-21 shows the use of a conversation timer. The BEGIN CONVERSATION TIMER expression (1) specifies a conversation_handle in parentheses, followed by a TIMEOUT phrase, which sets the timeout in seconds. When the time expires, the endpoint specified in the BEGIN CONVERSATION TIMER expression receives a message (2) with a NULL message_ body of type http://schemas.Microsoft.com/SQL/ServiceBroker/Messages/DialogTimer.
Figure 11-21. Conversation timer
If you are familiar with the way that timers are built in Windows or .NET Framework, you might think that a conversation timer requires a lot of system resources. SQL Server 2005 does not manage timers the same way that Windows or .NET Framework does. Timers are internally just some data, and an appropriate-size instance of SQL Server 2005 can support millions of timers. Keep in mind, though, that a conversation timer is only approximate.
Note that a conversation timer is specific to an end of a conversation; each end can have a different timer. There is only one conversation time at each end of the conversation.
Conversation Groups
Service Broker uses conversation groups to correlate messages and, if required by the application, to serialize the processing order of messages.
A business transaction typically involves more than a single message. A business transaction involving a purchase order, for example, might involve a message requesting a purchase order number and other messages checking stock levels, customer credit, and so on. In most cases, messages associated with one business transaction can be processed independently of those associated with other business transactions.
Conversation groups, in conjunction with activation procedures, are a core aspect of Service Broker’s ability to process messages in a scalable and efficient manner. It does so by allowing an application to leverage the fact that some messages are related to others. A conversation group identifies a set of conversations whose messages must be processed serially but that may be processed in parallel with messages in conversations from other conversation groups.
Every conversation belongs to a conversation group. Optionally, multiple conversations may be put into the same conversation group. A message is said to belong to a conversation group if the conversation that sends or receives it belongs to that conversation group.
Every message includes a conversation_handle and a conversation_group_id. The conversation_handle can be used to correlate messages within a conversation. Likewise, the converstation_group_id can be used to correlate messages within a conversation group.
Messages in a given conversation group can be, and often are, processed serially. This means that messages are processed in a way similar to the way rows in a table are processed in a transaction when the transaction isolation level is serializable. Processing the messages in a transaction gives that transaction exclusive access to all messages in that conversation group, in all dialogs, until the transaction commits or rolls back.
A conversation group can be locked, and when it is, the lock is held until the transaction under which it was obtained finishes by a commit or rollback. While the lock is held, only expressions running in the scope of the transaction that obtained the lock can read messages from any queue belonging to that conversation group. Note that this is done by Service Broker without using any locks on the queue itself. Note that it is possible to add more conversation groups to the transaction.
There is no way to lock a conversation group directly, as there is in some T-SQL statements that allow locking hints. Several expressions will lock a conversation group when executed: RECEIVE, SEND, MOVE CONVERSATION, END CONVERSATION, and GET CONVERSATION GROUP. In typical usage, there is a BEGIN TRAN before using one of these statements, followed by a COMMIT or ROLLBACK TRAN after the processing of the messages involved has been completed. This is why the example Figure 11-11 starts with a BEGIN TRAN before it executes a RECEIVE expression.
There is a difference in guaranteeing that messages in a conversation will be received in the order in which they are sent and guaranteeing that messages are processed in the order in which they are received. Service Broker guarantees the former but not the latter. Figure 11-22 shows a queue with messages flowing into it. In this example, assume that all the messages in the queue are from the same conversation. There are two batches in the illustration, each using RECEIVE to read the queue. The batches are executed at almost the same time. Batch 1 is executed (1) first; it uses RECEIVE to read the first message, message 1, from the queue and begins to process it. If there were no locking for conversation groups, when batch 2 is executed (2), it would read the next message in the conversation, message 2.
Figure 11-22. Preserving processing order
Now there are two batches running at the same time, and each is processing a different message from the same conversation. It may just so happen that batch 2 finishes before batch 1, even though it started later, which would result in the second message being processed before the first.
RECEIVE, like SELECT and similar T-SQL statements, is always executed under a transaction. In other words, even if you don’t write it that way, in effect RECEIVE always looks like this:
BEGIN TRAN
RECEIVE ...
COMMIT TRAN
-- more T-SQL expressions
This is not enough to ensure that messages are processed in the order in which they are received; that is the problem shown in Figure 11-22.
A programming convention is required to ensure that messages in a conversation group are processed in the order in which they are received. The RECEIVE expression that reads a queue must be inside an explicit transaction, as shown in Figure 11-11, and the transaction may not be committed or rolled back until all processing for the message has been completed. If this programming convention is followed, all messages in a conversation group will be processed in the order in which they were received.
The specific conversation group a conversation belongs to is determined by the BEGIN DIALOG CONVERSATION expression when the conversation is begun or later by a MOVE CONVERSATION statement. By default, a new conversation group is created for a new conversation. There are some limits on which existing conversation group a new conversation can join. Likewise, a conversation can be moved from one conversation group to another, again with some limitations. The conversation_group_id in a message identifies the conversation group for the message, and the conversation_handle identifies its conversation.
There are four ways a conversation can become part of a conversation group. The conversation begun in Figure 11-4 did not specify a particular conversation group to join, so a new conversation group was created, and the conversation was added to that conversation group. Optionally, a new conversation can be added to an existing conversation group, added to a specified new conversation group, or added to the conversation group of another conversation.
Figure 11-23 shows three ways a conversation can be made part of a conversation group. In these cases, a WITH clause is appended to the BEGIN DIALOG CONVERSATION expression. Specifying an existing conversation_group_id (1) in WITH RELATED_CONVERSATION_GROUP clause will add the new conversation to that conversation group.
Figure 11-23. Adding conversation to conversation group
A conversation_group_id is a UNIQUEIDENTIFIER—that is, a GUID. If the UNIQUEIDENTIFIER used in the WITH RELATED_CONVERSATION_GROUP clause does not specify an existing conversation group (2), the new conversation will be added to a new conversation group with that UNIQUEIDENTIFIER as its conversation_group_id.
A new conversation can be added to the conversation group of an existing conversation by using the WITH RELATED_CONVERSATION clause (3) and passing it the conversation_handle of the existing conversation. The new conversation will be added to the conversation group of the related conversation.
A new conversation may not be added to an arbitrary existing conversation group. All conversations in a conversation group must have the service specified in the FROM clause as either the initiator or the target of the conversation.
Figure 11-24 illustrates how access to queues is managed internally by Service Broker. There are two tables for managing conversations; they can be seen through the system views sys.conversation_groups and sys.conversation_endpoints. Service Broker in effect locks a conversation_group_id in sys.conversation_groups (1). Each conversation is related to its conversation group by the conversation_group_id. Each message (3) is related to its conversation by its conversation_id. The result of this technique is that Server Broker can add messages to queues and read via RECEIVE at a much greater rate than if it implemented queues as ordinary tables accessed under an isolation-level serializable transaction.
Figure 11-24. Conversation group locking
Note that this explanation of locking of conversation groups is just informational. The actual implementation is hidden. sys.conversation_groups and sys.conversation_endpoints are views, not physical tables, but are used to show the concept. Scalability and performance were important goals for Service Broker, and the internal design makes sure that the overhead of using queues is minimized.
The effect of locking on conversation groups is illustrated in the diagram in Figure 11-25. It has a queue (1) with messages entering the queue at the left and leaving it at the right. Conversations and conversation groups are identified in SQL Server 2005 by a UNIQUEIDENTIFIER, but to simplify referring to them in this diagram, conversations are identified with an integer, and conversation groups are identified with a letter. The next message ready to leave the queue is from conversation C:9 and conversation group G:A—that is, the message farthest to the right.
Figure 11-25. Conversation message processing
There are two RECEIVE expressions (2) executing at about the same time. Before they start, no conversation groups are locked. The semantics of RECEIVE are to read the next message ready to leave the queue that is not in a conversation group locked by a transaction different from the one it is running under. The RECEIVE in the top part of the diagram starts just before the one in the bottom part.
When the top RECEIVE in Figure 11-25, starts none of the conversation groups is locked, so it gets the next message ready to leave the queue—that is, the one farthest to the right—and locks conversation group A. When the bottom RECEIVE starts to execute, the next message ready to leave the queue is the second one from the right, but it is from conversation group A, which was locked by the top RECEIVE. The bottom RECEIVE instead gets the third message from the right, because it is the next message ready to leave the queue that is in a different conversation group, B, from the one that the top RECEIVE read.
Figure 11-25 shows the locking behavior of conversation groups in a single queue. In fact, the locking behavior extends across all queues, and the locking is determined only by conversation group. Figure 11-26 shows the same two batches that were shown in Figure 11-25, but in this case, each batch is working with a different queue. The top queue (1) in Figure 11-26 is being read by the top RECEIVE expression (2). Likewise, the bottom queue (3) is being read by the bottom RECEIVE expression(4). The top RECEIVE expression executes first and reads the first message in the top query, which is from conversation C:9 and conversation group G:A. This locks all the messages in conversation group G:A. When the bottom RECEIVE expression is executed, the G:A conversation group is already locked, so it reads the third message in the bottom queue—that is, the first message in that queue that is not in conversation group G:A.
Figure 11-26. Conversation groups across multiple queues
Note that this locking behavior extends only to queues in the same database.
A SEND expression locks the conversation group of the message it is sending. This means that there will be no processing of messages in the conversation group of the message until the transaction under which the SEND expression was executed has finished.
So far, we have been using the RECEIVE expression with a TOP (1) clause. A RECEIVE expression is similar to a SELECT expression in that it returns a set of rows and the TOP (1) clause is limiting the number of rows returned to a single row. If RECEIVE is used without a TOP clause, or a TOP(n) where n is greater than one, it may return multiple rows in the same way that SELECT does, with some limitations.
A RECEIVE expression without a TOP(1) clause will potentially return multiple rows, with each row representing a message from the queue it is reading. A RECEIVE expression does not attempt to return all the messages in a queue, however. The messages returned from a single execution of a RECEIVE expression will always be from the single conversation group.
The rows that a RECEIVE expression returns will be sent back to the client of the connection that executed the RECEIVE expression if there are no INTO clause or variables to capture the results, just as a SELECT expression returns rows when there are no INTO clause or variables.
An INTO clause can be added to a RECEIVE expression to capture the returned rows. The target of the INTO clause must be a table variable; it cannot be an actual table, because RECEIVE supports a table variable only in an INTO clause. Figure 11-27 shows a RECEIVE expression used in this way. The INTO clause comes immediately after the queue name (2), similar to placing it after the table name in a SELECT expression. The target of the INTO expression must be a table variable (1). The rows of the table variable are selected (3), and the results show that all the messages returned are from the same conversation group.
Figure 11-27. RECEIVE returning multiple rows
A RECEIVE expression may also have a predicate, but the predicate may do an equals test only on the conversation_handle or conversation_group_id column.
An application often needs to store state between the processing of its messages. One message in a conversation might say, “Create a new purchase order.” Then another message in the same conversation might arrive hours later, saying, “Cancel.” Processing the second message would require finding the purchase order created by the first message. This problem could be solved by requiring all messages about purchase orders to contain a purchase order number, of course. Many times, however, this is not really convenient, and sometimes, it is not possible. The client that sent the “Create new purchase order” message might want to send the “Cancel” message before it knows the purchase order number.
A straightforward way to store state for a Service Broker application is to put it into tables and identify it by the conversation_group_id of the message being processed. Every message includes its conversation_group_id. In addition, assuming that the application follows the convention of doing all message and state processing inside a transaction, there will never be the issue of two parts of an application trying to process the state at the same time. The message processing will in effect be given exclusive access to the state for the messages’ conversation group without using locks on the tables that hold the state, by leveraging the lock on the conversation group.
Message Types
In Figure 11-6, we sent an XML message. Later, in Figure 11-9, we saw that the message was stored in the queue in as a VARBINARY(MAX) in the Message column, and we had to cast back to XML to read it. Messages are in fact just a sequence of bytes, and the SEND in Figure 11-6 could have sent any sequence of bytes we wanted. Often, an application will want tighter control over what gets sent in a conversation. The CREATE MESSAGE TYPE and CREATE CONTRACT expressions are used to specify the format of messages and the direction in which they can be sent in a conversation.
The CREATE MESSAGE TYPE expression defines the format of a message, which is the content of the message_body column in the queue. Figure 11-28 shows the usage of the CREATE MESSAGE TYPE (1) statement. All message names—WorkRequest, in Figure 11-28—are case sensitive, just as service names are, regardless of the collation of the SQL Server instance.
Figure 11-28. CREATE MESSAGE TYPE
Every database contains a built-in message type named DEFAULT. The example in Listing 11-1 used the message type even though it did not specify it. The DEFAULT message type allows any message content because its data type is VARBINARY(MAX). The DEFAULT message type cannot be changed or dropped.
Message types are stored in a database and can be seen via a sys.service_ message_types (2) view for that database, as shown in Figure 11-28. Message types have a one-part name and cannot be referenced outside the database that is in use.
Every message type is associated with a VALIDATION. The VALIDATION specifies how the content of the message must be validated. Message validation is done only on the target. There are four kinds of validation: NONE, EMPTY, WELL_FORMED_XML, and VALID_XML.
NONE is the validation associated with the DEFAULT message type and specifies that no validation is to be done, which means that any content is allowed.
EMPTY specifies that there may be no message content. This type of message can be thought of as a marker message, where the name of the message type indicates the meaning of the message, and no message body is required.
WELL_FORMED_XML, from its name, implies that the message must conform to the Extensible Markup Language (XML) Recommendation from the World Wide Web Consortium, because the term “well-formed XML” comes from this recommendation. This recommendation specifies what is commonly known as an XML document, WELL_FORMED_XML in Service Broker, however, does not mean quite the same thing that “well-formed XML” does in the XML Recommendation.
The WELL_FORMED_XML validation does not allow a DTD to be part of the document, even though the recommendation does. It also allows what are commonly called document fragments, which the recommendation does not. Document fragments are documents that have more than one root element or maybe no elements at all. A document fragment is essentially a document that can be represented by the data model described in the XPath 1.0 Recommendation of the World Wide Web Consortium. These recommendations are available at http://www.w3.org/TR/2004/REC-xml11-20040204/ and http://www.w3.org/TR/1999/REC-xpath-19991116, respectively.
Coverage of the XML and XPath 1.0 recommendations are beyond the scope of this book except by way of the example that follows. Figure 11-29 shows two XML documents. The first document has a DTD (1) and is well-formed XML according to the XML Recommendation. However it may not be used when the validation for the message type is either WELL_FORMED_ XML or VALID_XML. This document shows a common idiom used in XML where the DTD contains ENTITYs that are essentially used as macros to be expanded elsewhere in the document. The T-SQL CONVERT function, using style 2, can be used to remove the DTD and expand the ENTITYs that they can be used as a message. The usage of CONVERT will be covered later.
Figure 11-29. XML documents
The second document is not well formed according to the XML Recommendation because there are two root elements in it: one WorkRequest (2) followed by another WorkRequest (3). Even though this document is not well formed, it may be used as a message when the validation WELL_FORMED_XML is used.
VALID_XML specifies the content is well-formed XML as specified in the XML recommendation, except that no DTD is allowed. But documents like the second one in Figure 11-29, with more than one root element, are not allowed. In addition, this specifies that the message can be validated against an XML Schema from the XML schema collection specified in the WITH SCHEMA COLLECTION clause. XML Schema is specified in the XML Schema 1.0 Recommendation from the World Wide Web Consortium. It can be found at http://www.w3.org/TR/2004/REC-xmlschema-1-20041028/ and http://www.w3.org/TR/2004/REC-xmlschema-2-20041028/. XML, the XML data type, and XML Schema in SQL Server 2005 are discussed in Chapter 9. Note that SQL Server 2005 does not support all the features of the XML Schema Recommendation. See Chapter 9 for a discussion of what is supported.
Using the validation VALID_XML in a message type puts the tightest constraints on a message but also has the most overhead, as it completely specifies all parts of a message. An explanation of XML Schema is beyond the scope of this book, but an introductory tutorial that covers the parts used in this example can be found at http://www.w3.org/TR/2004/REC-xmlschema-0-20041028/.
Figure 11-30 shows the XML Schema (1) and a message (2) that is valid according to this XML Schema. This schema specifies that the document element—that is, the outermost element—of the message will be named WorkRequest. The WorkRequest element will contain two other elements: in order, Equipment and Reason. The WorkRequest element has a timestamp attribute. All the elements will be in the 'urn:maintenance' namespace. As you can see, an XML Schema allows for fairly fine-grained control over the format of a message.
Figure 11-30. XML Schema and message
The example in Listing 11-1 used the DEFAULT message type, which uses validation NONE. Figure 11-31 shows the three other message types to illustrate the other types of validation. The WorkInProcess (1) message type uses VALID_XML validate. The WITH SCHEMA COLLECTION clause references an XML SCHEMA COLLECTION that contains the XML schema shown in Figure 11-30. WorkInProgress will look like the typical message shown in Figure 11-30. Chapter 9 covers the use of the CREATE XML SCHEMA COLLECTION expression.
Figure 11-31. Message types
The WorkRequest (2) message type in Figure 11-31 uses WELL_FORMED_ XML, so messages of this type may be any XML message, even a document fragment. The WorkComplete (3) message uses EMPTY validation. A Work-Complete message may have no content; the name of the message type itself indicates the meaning of the message.
Contracts
A contract specifies a set of messages and the endpoints of a conversation that can use them. Contracts are used in a BEGIN DIALOG CONVERSATION expression to constrain the messages used in a conversation. Figure 11-32 shows a CREATE CONTRACT expression (1) creating the Maintenance contract. The name of a contract is case sensitive regardless of the collation for the SQL Server instance. Message types are used in two places: SEND expressions and contracts. Contracts may be used in two places: BEGIN DIALOG CONVERSATION and CREATE SERVICE expressions.
Figure 11-32. Contract
The CREATE CONTRACT expression specifies, in parentheses, a comma-separated list of message types. A SENT BY (2) clause with each message type specifies the direction in which that message can be sent in a conversation. There are three choices for direction: ANY, TARGET, and INITIATOR. ANY specifies that the message may be sent from either endpoint in a conversation. TARGET specifies that the message may be sent only by the conversation endpoint associated with the TO SERVICE, as specified in the BEGIN DIALOG CONVERSATION expression. INITIATOR specifies that the message may be sent only from the endpoint associated with the FROM SERVICE.
Message types have only one-part names, so any message type specified in a CREATE CONTRACT expression must exist in the database in use when the CREATE CONTRACT expression is executed. The database and schema prefixes used when referring to other database objects may not be used on message type names.
Note that a contract specifies only message types and direction. It does not specify a message protocol—that is, it does not specify the sequence of or when a particular message should be used.
Contracts are stored in the database in use when the CREATE CONTRACT statement is executed. There is a built-in contract named DEFAULT in every database. It specifies the DEFAULT message type with a SENT BY ANY clause. The DEFAULT contract cannot be dropped or changed. The sys.service_contracts view (1), shown in Figure 11-33, can be used to see (2) the contracts in a database. Note that besides the DEFAULT contract, several other contracts are used by applications built into SQL Server 2005.
Figure 11-33. Contracts view
The CREATE SERVICE expression may optionally specify that it supports one or more contracts. The CentralMaintenanceService in the preceding example specified the built-in DEFAULT contract, as shown in Figure 11-8. The InspectionService did not specify any contract, as shown in Figure 11-2.
Figure 11-34 shows a CREATE SERVICE expression (2) that supports two contracts: DEFAULT and Maintenance. A service can support only a contract (2) that has been created in the same database as the one in which it was created. Note that the DEFAULT contract is not supported by default; if it is not specified, the service will not support it.
Figure 11-34. Supporting contracts
Every conversation uses a contract to constrain the messages that will be sent on it. If a contract is not specified in the BEGIN DIALOG CONVERSATION expression, as it was not in Figure 11-4, the DEFAULT contract is used by the conversation. The ON CONTRACT clause (2), shown in Figure 11-35, specifies the contract to use. The contract specified must be one of the contracts (1) that is supported by the TO SERVICE.
Figure 11-35. Conversation contract
There is a corollary to the fact that the TO SERVICE must support the contract a conversation will use. A service that does not specify any contracts may not be used as a TO SERVICE. The InspectionService in Figure 11-2, for example, could not be used as a TO SERVICE.
Send and Message Type
We have already seen that a message always includes a conversation handle, which is in the conversation_handle column of the queue, which can be used to send a reply to the sender of the message. Messages also always include the names of the service contract being used and the message type of the message. Figure 11-36 shows the conversation_handle, service_contract_name, and message_type_name columns of the CentralMaintenanceQueue (1). You can see that the queue contains the WorkRequest message that was sent using the Maintenance contract.
Figure 11-36. Message types in queue
The message type name is added to the message by the SEND expression (1) with an optional MESSAGE TYPE clause, as shown in Figure 11-37. When a SEND expression does not specify a message type, such as the SEND expression in Figure 11-6, the DEFAULT message type is used.
Figure 11-37. SEND and message type
The SEND expression will not block or raise an error if a message that does not comply with the validation chosen for the messages is attempted to be sent. Instead, at some point it will receive an error message in its queue. Figure 11-38 shows an example of sending a malformed message. The message being sent is a WorkRequest message, which was specified as using validation WELL_FORMED_XML in Figure 11-31.
Figure 11-38. Sending malformed message
Note that executing a SEND expression specifying a message type that is not part of the contract will raise an error.
The problem with the message in Figure 11-38 is that there is a string(1) that must be enclosed in single or double quotes to be in compliance with the XML Recommendation. Even though the message is malformed, the SEND expression will be executed successfully.
The service associated with the other end of the @workRequest conversation in this example is the InspectionService. The result of sending the malformed message will be that an error message will be put into the InspectionQueue (2). The message (3) will be an XML description of the error that was found. The message is from Service Broker, and it can be identified as such because the document element is “Error” in the http://schemas.microsoft.com/SQL/ServiceBroker/Error namespace.
The message that caused the error will be lost. When a service receives an error, any subsequent message sent on the conversation on which the error occurred will be lost. In other words, when an error occurs on a conversation, an application will no longer be able to send messages on that conversation. Note that even though it will no longer be possible to send messages on that conversation, it still will be possible to read messages from it. Also, as we will discuss in more detail later in this chapter, if retention is enabled in the queue, it is possible to recover the message that caused the error.
This response to an error, basically turning off the conversation, fits in with the Service Broker view that the primary programming element in Service Broker is the conversation, not the message. In other words, the message in error was lost; therefore, any message that followed it would be out of order. A conversation is by definition a sequence of messages received in the order in which they were sent, so the conversation is broken, and it makes no sense to carry it on any further.
Many queuing technologies view the message as the primary programming element; they will automatically send a bad message to a special queue and continue to process subsequent messages. Imagine if the intent of the bad message was to say, “Do not process any of the funds-transfer messages that follow me.” Service Broker’s approach to error handling is quite different from this; it stops further processing in the conversation.
There are a couple of important points to note about XML documents that contain a DTD. The first message shown in Figure 11-29 contains a DTD. This message does not comply with the WELL_FORMED_XML validation. If SQL Server 2005 detects a WELL_FORMED_XML message that has DTD in it, however, it will not send an error message. In other words, the application will not know that a malformed message was sent if the problem was due to a DTD.
This is not correct operation and may be corrected in some future Service Pack for SQL Server 2005 release. The conversation will be shut down as with any error, though, and the message itself will be stored in the sys.transmission_queue and never be sent, but no error message will be sent to inform the application.
It turns out that the idiom shown in the message (2), as shown in Figure 11-39, is a fairly common one, where an ENTITY is used like a simple replacement macro. If some of the messages in your application are from sources outside SQL Server, it is possible that they use this idiom. The T-SQL CONVERT function (1) has been enhanced in SQL Server 2005 to do the macro replacements and strip out the DTD when the expression it is converting is a string or VARBINARY, the desired type is XML, and the style is 2. The result (3) of using the CONVERT function in this way is an XML document that complies the XML_WELL_FORMED validation.
Figure 11-39. Using CONVERT with DTD
Processing Loop
We saw in the ad hoc batch in Figure 11-11 how to read a single message from a queue with a RECEIVE expression and then process it within a transaction. We also saw in Figure 11-13 that a queue can be configured with an activation procedure that Service Broker will start whenever the queue seems to be becoming deeper. This gives us the ability to process messages in the queue as they appear.
Some overhead is required to start a stored procedure, so in general, it is best to process all the messages in a queue in a single execution of a stored procedure. In fact, the overhead is sufficient that waiting a short time to see whether any more messages appear after the queue is emptied before returning from the stored procedure is best practice.
Typically, the procedure that processes messages will dispatch the messages based on their message type before processing them. This procedure must also take into account the fact that some of the messages it receives may not be part of a contract; they may be error or status messages from Service Broker.
Figure 11-40 shows a simple message-processing loop. This loop could be used to process messages for the CentralMaintenanceService. The loop runs until a BREAK (4) is executed when no messages have been read by the RECEIVE expression. A WAITFOR (1) clause is wrapped around a RECEIVE expression and specifies a TIMEOUT (2) in milliseconds. This makes the RECEIVE expression wait for up to 5 seconds to see whether something appears in the queue. If there is nothing in the queue after 5 seconds of waiting, the RECEIVE will end with @@ROWCOUNT = 0, which will execute the BREAK (4) instruction and roll back the outstanding transaction.
If there is anything in the queue within 5 seconds of the RECEIVE being executed, the RECEIVE ends with @@ROWCOUNT = 1; the variables used to capture the columns from the queue are set with appropriate values; and the message is processed (3) by a stored procedure, ProcessCentralMaintenance. When the message has been processed, the transaction is committed; then a BEGIN TRAN is executed, followed by the RECEIVE expression. The RECEIVE will again wait for up to 5 seconds for another message and will keep doing this until the queue is empty and stays empty for 5 seconds.
The RECEIVE and the processing that follows are done under the same transaction. It is important that each RECEIVE/processing step be under a different transaction. Each RECEIVE has the potential of locking a different conversation group. If all the RECEIVE/processing steps were done under the same transaction, they could unnecessarily prevent processing loops from running. There is one exception to this that we will look at later in this chapter: multiple RECEIVE/processing steps done under the same transaction and the same conversation group.
The message-processing (3) part of the processing loop in Figure 11-40 must take into account all the messages it might receive. The processing loop itself doesn’t really know what conversation it will be part of, so we will assume that it is going to be in the activation procedure for the CentralMaintenanceQueue that was created in Figure 11-34. This means that the processing loop will have to support the DEFAULT and Maintenance contracts, along with the END CONVERSATION and error messages that Service Broker might send to it.
Figure 11-40. Message processing loop
Listing 11-2 shows the stored procedure, ProcessCentralMaintenance, that dispatches messages based on their message_type_name. It handles the four possible message types that the CentralMaintenanceService might receive: DEFAULT, WorkRequest, http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog, and http://schemas.microsoft.com/SQL/ServiceBroker/Error.
We have not defined the processing for the DEFAULT, so the processing for it just selects the message itself as a placeholder.
We will process the WorkRequest the same way we did in Figure 11-11—send back a reply that is timestamped and add the request to the WorkIn-Progress table—but with a few differences. We used the XQuery modify function in Figure 11-11 to add the timestamp to the incoming message, so we could use it as a reply. But in Figure 11-11, we received the message as an XML type. The ProcessCentralMaintenance stored procedure, however, receives the message as a VARBINARY(MAX) so that it can handle any of the possible message types it could receive. The XQuery modify method may not be used on a VARBINARY(MAX), even if it contains XML. So we have to convert the message to XML by copying it into an XML variable and then use the XQuery modify method on that variable.
We also have changed the WorkInProgress table by adding a group column to hold the conversation_group_id of the message. This is the typical way state is stored in a Service Broker application. When another message is processed by ProcessCentralMaintenance, it can use that message’s conversation_group_id to look up the data in the WorkInProgress table.
When an http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog message is processed, the ProcessCentralMaintenance stored procedure just executes an END CONVERSATION expression to clean up its end of the conversation.
When an http://schemas.microsoft.com/SQL/ServiceBroker/Error message is processed, the ProcessCentralMaintenance stored procedure removes all entries in the WorkInProgress table for the conversation group of the error message and then executes an END CONVERSATION expression to clean up its end of the conversation.
Note the processing that is done when an http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog or http://schemas.microsoft.com/SQL/ServiceBroker/Error is not dictated by Service Broker. The application must plan for responding to these messages in a way that makes sense for the overall application. In most cases, though, both messages will execute an END CONVERSATION, and an error may require any state to be cleaned up.
Note that there a number of ways to dispatch on message type. A case statement could be used, for example. Also notice that the names of the message types from Service Broker are rather long. In fact, they are uniform resource identifiers as are typically used on the Web. These are not the URLs for Web sites, however. Uniform resource identifiers are specified in the Uniform Resource Identifier RFC (2396). A URL is a kind of uniform resource identifier. The examples in this chapter have used short, simple names for Service Broker objects to make it easier to follow the explanation. It is best practice to use a uniform resource identifier for the name of a Service Broker object, as that will make it easier to maintain them in a large enterprise.
Note that at the end of the ProcessCentralMaintenance stored procedure simply removes the work in progress from the WorkInProgress table when an error is detected. An actual application would have to have a more sensible way to handle errors; the purpose of this example is just to show that errors must be handled in some way.
Listing 11-2. Message dispatch
CREATE
PROC ProcessCentral Maintenance
(
@message VARBINARY(MAX),
@conv UNIQUEIDENTIFIER,
@group UNIQUEIDENTIFIER,
@type NVARCHAR(256)
)
AS
IF @type = 'DEFAULT'
BEGIN
--process default message
SELECT @message
END
IF @type = 'WorkRequest'
BEGIN
DECLARE @ts VARCHAR(MAX);
SET @ts = CAST(GETDATE() AS VARCHAR(MAX));
DECLARE @xmlMsg XML
SET @xmlMsg = @message
SET @xmlMsg.modify('insert (
attribute timestamp{sql:variable(''@ts'')}
) into (/*[1])'),
SELECT @message, @conv;
SEND ON CONVERSATION @conv
Message Type WorkInProcess
(@message)
INSERT INTO PendingWork VALUES (@group, @message)
END
IF @type =
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @conv
END
IF @type =
'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
—handle error
DELETE WorkInProgress WHERE group = @group
END CONVERSATION @conv
END
The message-processing loop shown in Figure 11-40 and Listing 11-2 processes one message at a time. Even though it is processing one message at a time, it is still very efficient, because it processes as many messages as possible each time the loop is called.
There is an enhancement to this loop that in some cases will make it even more efficient. The principle is to process messages one conversation group at a time instead of one message at a time. This can be more efficient, because the cost of getting the state for the conversation group can be amortized over many messages.
The GET CONVERSATION GROUP expression gets the conversation_group_id for the next available message—that is, the next message in an unlocked conversation group—in the queue and locks the corresponding conversation group.
The implementation of the conversation-group-at-a-time processing is shown in Listing 11-3. It is a loop within a loop. The outer loop uses a GET CONVERSATION GROUP expression to get and lock the next conversation group. A GET CONVERSATION GROUP expression can use a WAITFOR clause, shown in bold, in the same way that a RECEIVE expression can. If no conversation group is available after a few seconds, the transaction is rolled back, and the lock is released.
A conversation group is available; then the loop from Listing 11-3 is run. But the RECEIVE expression uses a predicate that looks only for messages in the conversation group that were found by the GET CONVERSATION GROUP expression.
Listing 11-3. Conversation group at a time
WHILE 1 = 1
BEGIN
DECLARE @message VARBINARY(MAX);
DECLARE @conv UNIQUEIDENTIFIER;
DECLARE @group UNIQUEIDENTIFIER;
DECLARE @type NVARCHAR(256)
BEGIN TRAN
WAITFOR (
GET CONVERSATION GROUP @conv
FROM CentralMaintenanceQueue
), TIMEOUT 5000
IF @ROWCOUNT = 0
BEGIN
ROLLBACK TRAN
BREAK
END
-- get state
WHILE 1=1
BEGIN
RECEIVE TOP (1)
@message = message_body,
@group = conversation_group_id,
@type = message_type_name
FROM CentralMaintenanceQueue
WHERE conversation_group_id = @conv;
-- process message
EXEC ProcessCentralMaintenance
END
COMMIT TRAN
END
Poison Messages
Message processing, as shown in Listing 11-2 and Listing 11-3, is done under a transaction, and queues themselves are transactional. If, in the course of processing a message, the transaction is rolled back, the message will be put back into the queue. At some point—in fact, in most cases immediately, because the message will be at the beginning of the queue—the message will be read again, and during its processing, the transaction might again be rolled back. A message whose processing always causes the transaction that wraps it to be rolled back is called a poison message.
One of the strategies for managing poison messages is to move the message to a special queue after it causes some small number of rolled-back transactions and then continue processing the messages that follow it. The “poison message” queue is processed at some later time, possibly by hand. Some queuing frameworks will automatically implement this strategy, relieving the application code of having to track the number of times a transaction is rolled back.
Service Broker provides no special support to continue processing automatically when a poison message is received. Service Broker treats the conversation as the fundamental unit of information, not the message. Imagine a conversation that produced a message that said, “Ignore all following funds transfers,” but that could not be processed because of other data it contained. Moving this message to a poison-message queue and then processing the ones that follow would not be a good thing to do.
Service Broker provides no automatic support for bypassing poison messages. It does allow for the fact that in the course of normal processing of a message, something unexpected might happen—for example, the transaction that wraps the processing might become the victim of a deadlock.
The processing loop for a queue must process all messages it receives. If a poison-message queue makes sense for the application, the processing loop will have to implement it.
Service Broker does provide last-resort protection for poison messages, so it will never get into an infinite loop of rolling back a transaction and reprocessing a message. Whenever a queue has a message put back into it five times in a row, due to a rolled-back transaction, Service Broker disables the queue—that is it, in effect executes ALTER QUEUE WITH STATUS = OFF, as shown in Figure 11-16. Note that this is truly a last-resort protection, as it stops not only messages from the conversation involved, but also messages in all conversations that use the queue that it turned off. This means that sys.transmission_queues trying to send messages on these conversations will be filling up until the queue is enabled.
When Service Broker uses the last-resort protection, it also raises a SQL Event named Broker_Queue_Disabled. This event can be found in Books Online under BROKER_QUEUE_DISABLE and “designing Event Notifications,” SQL Events are discussed in Chapter 8. The handler for this event may be able to clean up the problem automatically, or it may have to notify a person to clean things up manually.
Compensation
Service Broker is a framework that can be used to implement business transactions as sagas—a series of independent database transactions that may go on for a long time, even years. Compensation is a way to undo the effects of a saga in a way analogous to a rollback of a database transaction. A saga is not a database transaction, however; there is no “guaranteed by mathematics” way to clean up what has been done by a saga when it fails. It is up to the application to design a compensation strategy for when a saga fails.
An example of a saga that fails is one for a purchase order that was canceled by the customer who issued it. Between the time the purchase order was received and the time that it was canceled, the saga associated with it may have taken parts out of inventory or even, as a side effect, started up a manufacturing process. When a cancellation is received, the saga must compensate for the changes it has made—for example, return parts to inventory and cancel production runs. Note that unlike database transactions, compensation does not actually put things back exactly as they were.
Queues optionally may be configured to retain messages for the life of a conversation. Note that an error will not end the life of a conversation; both ends of the conversation must call END CONVERSATION to do that, even when an error is involved. The retained messages include both those sent by the service associated with the queue and those received by the queue.
By default, retention is not enabled for a queue. Figure 11-41 shows a CREATE QUEUE expression that uses a WITH RETENTION option (1) to enable retention for the queue. Retention may be enabled or disabled at any time by using ALTER QUEUE expression (2).
Figure 11-41. Retention
The example in Figure 11-42 has retention enabled for both the Central-MaintenanceQueue, which is associated with the CentralMaintenanceService, and the InspectionQueue, which is associated with the InspectionService. It sends a message (1) from the InspectionService to the CentralMaintenanceService. The message, however, appears (2) in the InspectionQueue—that is, the queue for the FROM SERVICE.
Figure 11-42. Retention of sent message
Even though this message is in the InspectionQueue, it cannot be read using a RECEIVE expression. The RECEIVE expression will always skip any retained messages. The status column of a queue indicates why the message is there. A status of 0 indicates that the message is ready to be read. The RECEIVE will read only messages whose status is 0.
A status of 3 indicates that the message was sent by a service associated with the queue, which is the case for the message we can see in the InvoiceQueue (2) in Figure 11-42. A status of 1 means that the message has already been read but is being retained.
Figure 11-43 shows a queue retaining a message that was received. A RECEIVE expression is used to read (1) the CentralMaintenanceQueue. After the message has been read, it is still in (2) the CentralMaintenanceQueue but with a status of 1, indicating that it has been read.
Figure 11-43. Retention of received message
When a saga fails, the application can go back to all the queues involved, use a SELECT * WHERE STATUS = 1 expression to find the messages that have been retained for it, and use those messages to determine how to compensate for the effects of the saga.
There is no way to remove retained messages directly. The retained messages will remain in the queue until the conversation they belong to has been ended. When using retention to implement compensation, you must keep conversations involved with a saga active until the retained messages associated with them are no longer needed.
Retention is not the only way to implement compensation. The main disadvantage of retention is that if sagas are long or involve many messages, queues will become large and impact overall performance. Retention not only requires more storage to hold the messages, in effect each message is sent twice, once to put it back into the queue it was read from and then again to send it to its destination.
Compensation can be implemented without using retention. If possible, it is best to implement a compensation strategy based on some simple state rather than on all the messages processed to get to that state. A saga that processes a purchase order, for example, might add a column for each line item that says whether that part has been reserved from inventory for that purchase order. If the saga fails, the compensation could use the purchase order to determine what to return to inventory. This technique would almost certainly require less storage than all the messages used to process the purchase order.
Distribution
So far, all the discussions and examples of using Service Broker have been with services that are all in the same database. This has been done to make the explanations of how services, queues, and the like work easier to follow. A typical Service Broker application will be distributed over many databases and many servers. Now we are going to look at how this is done.
The example shown in Figure 11-6 that sends a message to a nonexistent service shows that Service Broker manages services through their names, not directly by their locations. Service Broker maps the name of a service to its location. If it can find no location for the name when a message is sent, it puts the message into the sys.transmission_queue and keeps it there until it can.
There are two separate topics to cover. One is how Service Broker maps the name of a service to the location of that service, and the other is how Service Broker manages security for a conversation between two different instances of SQL Server on different machines. You may find it easier to try the examples that follow by using two instances of SQL Server on the same machine. Sometimes, the order in which things are set up is important. Listing 11-6 and Listing 11-7, later in this chapter, summarize the points that follow with things set up in an appropriate order.
Figure 11-44 diagrams two instances of SQL Server 2005 that contain the services we have been working with, CentralMaintenanceService and InspectionService, in a single database. The instance on the left, Bismark, contains a database, Company, that contains the CentralMaintenance-Service. Service Broker does not use the name of a database to identify the location of a service. It uses a UNIQUEIDENTIFIER, which it assigns, called a broker instance. In Figure 11-44, the CentralMaintenanceService is located in the 65C9E167-0DE0-4B12-9E14-A0DAEB2B4140 broker instance in the SQL Server 2005 Bismark instance.
Figure 11-44. Service broker instances
On the right side of Figure 11-44 is an instance of SQL Server 2005 named Klamath. It contains the FieldOffice database, which contains the InspectionService.
The two instances of SQL Server communicate by using the network and the Adjacent Broker Protocol, which is implemented by Service Broker. Note that the connection between SQL Server 2005 instances shown in Figure 11-44 is not a static connection; it is made only as required.
Every database in a SQL Server 2005 instance is a Service Broker and has a unique broker_instance in the form of a GUID. The broker instance for a database is in the service_broker_guid column of the sys.databases system view. Figure 11-45 shows a SELECT expression used to find the broker instance for the Company database in the Bismark SQL Server 2005 instance. Note that terms Service Broker GUID and broker instance are synonyms, and both terms appear in Books Online and various system views.
Figure 11-45. Service broker instance
Sometimes, you need to know the broker instance for a database. In those cases, you have to look it up in the sys.databases view of the appropriate instance of SQL Server 2005. Figure 11-45 shows a SELECT expression used to find the service instance of the Company database.
BEGIN DIALOG CONVERSATION optionally allows the TO SERVICE to specify a broker instance. When it is not specified, the broker instance of the database in use when the BEGIN DIALOG CONVERSATION is executed is used.
In the previous examples, all the services were in the same database, so we didn’t have to deal with the broker instance. It is possible to have two services with the same name in different databases of the same instance of SQL Server 2005. In this case, the broker instance can be used to specify one of these two services.
Listing 11-4 shows a BEGIN DIALOG CONVERSATION that specifies the broker instance, shown in bold, for CentralMaintenanceService. When the broker instance is used, it follows the name of the TO SERVICE after a comma. A special broker instance, 'CURRENT DATABASE', may also be used to specify the current database explicitly.
Listing 11-4. Specifying broker instance
DECLARE @workRequest UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @workRequest
FROM SERVICE InspectionService
TO SERVICE 'CentralMaintenanceService',
'65C9E167-0DE0-4B12-9E14-A0DAEB2B4140'
ON CONTRACT Maintenance;
SELECT @workRequest;
SEND ON CONVERSATION @workRequest
MESSAGE TYPE WorkRequest
('
<WorkRequest xmlns="urn:maintenance">
<Equipment>1ccc2-37-BK</Equipment>
<Reason>Overheat</Reason>
</WorkRequest>'),
Service Broker always uses a routing table to determine the instance of SQL Server 2005 that a message should be sent to. Each database in a SQL Server 2005 instance has a routing table. The routing table can be seen though the sys.routes system view in that database. The routing table from the database in use when the SEND expression is executed is used to determine the instance of SQL Server 2005 that a message should be sent to.
A routing table includes several pieces of information, all of which are documented in Books Online. There are three key pieces of information used to route messages, and they are in the remote_service_name, broker_instance, and address columns of the sys.routes system view. The routing table maps the remote_service_name of a service and its broker instance, from the TO SERVICE in BEGIN DIALOG CONVERSATION expression, to an instance of SQL Server 2005.
When SQL Server 2005 is installed, it includes a route named AutoCreatedLocal in the routing table for the model database. As a result, unless this is changed, all new databases include this route in their routing table. Figure 11-46 shows the contents of the initial routing table for a database. The AutoCreatedLocal route is used as a wildcard for those services that do not match any other entry in the routing table. The NULL for the remote_service_name (1) and broker_instance (2) are wildcards that match any service name or broker instance. LOCAL (3) means that the message should be sent to the instance of SQL Server 2005 that the SEND command is running on—that is, the local instance.
Figure 11-46. Default routing table
Routes are managed using the CREATE ROUTE, ALTER ROUTE, and DROP ROUTE expressions. CREATE ROUTE specifies the three key pieces of information that were previously mentioned, along with a LIFETIME for the route. Listing 11-5 shows the typical usage of CREATE ROUTE. The SERVICE_NAME option corresponds to the remote_service_name shown in the sys.routes system view. The BROKER_INSTANCE and ADDRESS options correspond to the like-named columns in the sys.routes system view.
The only required parts of the CREATE ROUTE expression are the name of the route—CentralMaintenance, in this case—and the ADDRESS option. Unspecified options will be entered into the routing table as NULL. The address in this example, N’TCP://192.168.226.138:4030’, is the IP address of an instance of SQL Server 2005 and the port on which it is listening for Service Broker messages. Alternatively, the address may be a host name and port. The port used is unrelated to the port SQL Server 2005 listens on for TDS client connections; an instance of SQL Server 2005 has to be configured to listen for Service Broker messages, and later in this chapter, we see how to do that. The BROKER_INSTANCE is from the sys.databases table in the instance of SQL Server 2005 that has the CentralMaintenanceService, as was shown in Figure 11-45.
Listing 11-5. Create route
CREATE ROUTE CentralMaintenance
WITH SERVICE_NAME = 'CentralMaintenanceService',
BROKER_INSTANCE = '65C9E167-0DE0-4B12-9E14-A0DAEB2B4140',
ADDRESS = N'TCP://192.168.226.138:4030'
The SQL Server 2005 instance referenced in a CREATE ROUTE or ALTER ROUTE expression does not have to be enabled to listen for Service Broker messages or even exist when the route is created or changed. Similar to what we saw in Figure 11-6, when we sent a message to a nonexistent service, the sys.transmission_queue will hold messages until the instance of SQL Server 2005 referred to by the ADDRESS option exists and is ready to accept messages. Also, creating a route does not cause a connection to be made to the ADDRESS specified at that time; Service Broker uses the routing table to make connections as needed by SEND expressions. The CREATE ROUTE expression can succeed with a completely fictitious address.
If there are entries where remote_service_name and broker_instance match exactly, they are used. It is possible to have more than one route for a given service name. If there is no remote_service_name that exactly matches the name of the service, the entries in the routing table with a NULL remote_service_name are used. If there are entries that match the service name, and the TO SERVICE of the conversation involved did not specify a broker instance, those entries are used. The full details of the matching algorithm are in Books Online. Keep in mind that route names are always binary matched, not by characters, so they are case sensitive regardless of how SQL Server is configured.
If only one route is found, it is used to find the address of the SQL Server 2005 instance for that service. Figure 11-47 shows a routing table with two routes that could match the TO SERVICE of a conversation.
Figure 11-47. Route assignment
Whenever there is more than one match for a service, Service Broker uses one of them based on a simple algorithm that uses the modulus of the handle of the dialog. This is a very useful feature of Service Broker. It provides a sort of load for parts of Service Broker applications. The load balancing is “sort of” because the load balancing is done with no real load information and is done on a per-conversation basis, not a per-message basis. Nonetheless, this simple feature of Service Broker can be used to make a Service Broker application a lot more robust without having to make any changes to the application itself.
In fact, we have covered only half the routing story. Service Broker has to use a routing table when a SEND expression is used to figure out where a service is located. What happens when a message arrives at an endpoint? Remember that a Service Broker endpoint is associated with an instance of SQL Server 2005, not a particular database. When a message arrives at an endpoint, Service Broker uses the routing table that is in msdb to find the location of the service.
The implication of this is that all conversations that go between instances of SQL Server 2005 require four routing tables to be configured: one routing table in the database that sends a message on the conversation, a second in msdb of the instance of SQL Server 2005 that receives the message, a third in the database of the service that sends a reply to the message, and a fourth in msdb of the instance of SQL Server 2005 that sent the message. The third and fourth routing tables are needed so that the service that receives the message will be able to send a reply.
Figure 11-48 shows how msdb would be set up in the Bismark instance of SQL Server 2005. Note that Service Broker assigns the broker instance to a database, so you will have to look it up in sys.databases. Note that this route specifies a SERVICE_NAME for a service that is in Bismark.
Figure 11-48. Routing in msdb
SQL Server 2005 is secure by design and default, so several things are required to enable Service Broker conversations between SQL Server 2005 instances. Each SQL Server 2005 instance must be configured to listen for other SQL Server 2005 instances using the Adjacent Broker Protocol. There must also be a way for each server to authenticate the other so it can decide whether to authorize the connection. Service Broker can use Windows authentication to do this.
When the service, sqlservr.exe, that implements SQL Server 2005 starts running, it logs onto Windows with a Windows identity. You configure this identity when you install SQL Sever 2005, as shown in Figure 11-49. Corp, in this diagram, is a domain, and SQLID is a member of it.
Figure 11-49. Windows logon
The identity that an instance of SQL Server 2005 uses to log onto Windows must be a SQL login on any instance of SQL Server 2005 that it will be sending a Service Broker message to. Figure 11-50 shows two SQL Server instances. The Klamath instance, on the right, logged onto Windows using the identity DomainSQLID2 when it started running. It you are trying out the examples with two instances of SQL Server on a single machine, Domain would be the name of the machine. This means that the Windows identity used run an instance of SQL Server must also be a login on every instance of SQL Server that it will be connecting to with Service Broker.
Figure 11-50. Connections identities
The Bismark instance on the left has a SQL Windows login for DomainSQLID2. Likewise, the Klamath instance has a login for Domain SQLID. These logins do not require a user in any database.
Figure 11-51 shows a batch that will create the Service Broker endpoint for the Klamath instance of SQL Server 2005 that can be accessed by the Bismark instance of SQL Server 2005. It starts by creating a login (1) based on a Windows identity used by the Bismark instance when it logs onto Windows. Then it creates an endpoint (2). SQL Server supports several kinds of endpoints, but Service Broker supports only a single Service Broker endpoint in an instance of SQL Server 2005, and it must be a TCP endpoint. The AS TCP clause must specify the port that it will listen on. The actual port number is not important, but it must not be in use already.
Figure 11-51. Endpoint for Klamath
An endpoint has a name, so it can be referenced. This endpoint is created in the STARTED state, but by default, an endpoint is created in the STOPPED state. There is an ALTER ENDPOINT expression, documented in Books Online, that can be used to change the state of an endpoint. This endpoint is configured as a Service Broker endpoint, and it uses Windows authentication.
This login is granted CONNECT (3) permission on the Service Broker endpoint. This permission is required for the Bismark instance to send messages to the Klamath instance. Note that the master database must be in use to be able to GRANT CONNECT to an endpoint. You cannot GRANT CONNECT to an endpoint from any other database.
The data saved by CREATE ENDPOINT for a Service Broker endpoint can be seen in two system views, sys.endpoints and sys.tcp_endpoints. Figure 11-52 shows a join of these views from the Klamath instance of SQL Server 2005. It shows that there is a Service Broker endpoint named Inspection (1) that is in the STARTED state (2) and listens on port 4030 (4) using the TCP (3) protocol.
Figure 11-52. Endpoints
Listing 11-6 and Listing 11-7 are batches for setting Klamath and Bismark, respectively. The batches assume that both instances are set up on the same machine and that the machine’s name is DANAL-BASE. The IP addresses, ports, and broker instances used are machine dependent and will have to be changed to match the machine they are run on. It is recommended that you run each statement in the batch separately and look at how it affects the data in the various system views we have been discussing.
Note that the GUIDs in the CREATE ROUTE expression near the ends of Listing 11-6 and Listing 11-7 had to be looked up using the technique shown in Figure 11-45.
Listing 11-6. Klamath setup
use master
-- Figure 11-51
CREATE LOGIN [DANAL-BASESQLID] FROM WINDOWS
CREATE DATABASE FieldOffice
ALTER DATABASE FieldOffice
SET ENABLE_BROKER
USE FieldOffice
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
use master
CREATE ENDPOINT Inspection
STATE = STARTED
AS TCP (LISTENER_PORT = 4031)
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS)
GRANT CONNECT ON ENDPOINT::Inspection
TO [DANAL-BASESQLID]
use FieldOffice
GRANT SEND ON Service::InspectionService
TO PUBLIC
-- Listing 11-5
CREATE ROUTE CentralMaintenance
WITH SERVICE_NAME = 'CentralMaintenanceService',
ADDRESS = N'TCP://192.168.226.138:4030'
-- Figure 11-48
use msdb
SELECT service_broker_guid FROM sys.databases WHERE name='FieldOffice'
CREATE ROUTE Inspection
WITH SERVICE_NAME = 'InspectionService',
ADDRESS = N'LOCAL',
BROKER_INSTANCE = '424D4331-B382-49C7-81A0-D2B82D81350F'
use FieldOffice
CREATE ROUTE CentralMaintenance
WITH SERVICE_NAME = 'CentralMaintenanceService',
ADDRESS = N'TCP://192.168.226.138:4030'
Listing 11-7. Bismark setup
CREATE DATABASE Company
ALTER DATABASE Company
SET ENABLE_BROKER
USE Company
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
use master
CREATE LOGIN [DANAL-BASESQLID2] FROM WINDOWS
CREATE ENDPOINT Central
AS TCP (LISTENER_PORT = 4030)
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS)
ALTER ENDPOINT Central STATE = STARTED
GRANT CONNECT ON ENDPOINT::Central
TO [DANAL-BASESQLID2]
use Company
GRANT SEND ON Service::CentralMaintenanceService
TO PUBLIC
CREATE ROUTE Inspection
WITH SERVICE_NAME = 'InspectionService',
ADDRESS = N'TCP://192.168.226.138:4031'
use msdb
SELECT service_broker_guid FROM sys.databases WHERE name='Company'
CREATE ROUTE Central
WITH SERVICE_NAME = 'CentralMaintenanceService',
ADDRESS = N'LOCAL',
BROKER_INSTANCE = '1A245923-86D3-4B52-BCAD-0713E0C4F7FC'
One of the nice things about a Service Broker application is that when you distribute it across systems, the application code doesn’t change. In fact, the code we used at the beginning of the chapter, where the services were both in the same database, will still work; we just have to move it to the servers we want it to work on, configure the routes, and add the endpoints.
Listing 11-8 has the application that would run on the Bismark instance of SQL Server 2005, and Listing 11-9 has the application that would run on the Klamath instance of SQL Server 2005. The results of using these applications would be the same as when we used the CentralMaintenanceService and InspectionService at the beginning of this chapter.
One important thing to note about moving a Service Broker application: All the XML schemas, message types, and contracts used must be copied to any database the application gets moved to.
Listing 11-8. Bismark application
CREATE XML SCHEMA COLLECTION WorkOrder
AS
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified"
targetNamespace="urn:maintenance"
xmlns="urn:maintenance">
<xs:element name="WorkRequest">
<xs:complexType>
<xs:sequence>
<xs:element name="Equipment" type="xs:string"/>
<xs:element name="Reason" type="xs:string"/>
</xs:sequence>
<xs:attribute name="timestamp" type="xs:string" use="required"/>
</xs:complexType>
</xs:element>
</xs:schema>
'
CREATE MESSAGE TYPE WorkInProcess
VALIDATION = VALID_XML WITH SCHEMA COLLECTION WorkOrder
CREATE MESSAGE TYPE WorkRequest
VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE WorkComplete
VALIDATION = EMPTY
CREATE CONTRACT Maintenance
(
WorkInProcess SENT BY TARGET,
WorkRequest SENT BY INITIATOR,
WorkComplete SENT BY TARGET
)
CREATE QUEUE CentralMaintenanceQueue
WITH ACTIVATION (
PROCEDURE_NAME = CentralMaintenance,
MAX_QUEUE_READERS = 2,
EXECUTE AS SELF )
CREATE SERVICE CentralMaintenanceService
ON QUEUE CentralMaintenanceQueue
([DEFAULT], Maintenance)
CREATE TABLE PendingWork
(
work XML
)
GO
CREATE
PROC CentralMaintenance
AS
WHILE 1 = 1
BEGIN
DECLARE @message VARBINARY(MAX);
DECLARE @conv UNIQUEIDENTIFIER;
DECLARE @group UNIQUEIDENTIFIER;
DECLARE @type NVARCHAR(256)
BEGIN TRAN
WAITFOR (
GET CONVERSATION GROUP @conv
FROM CentralMaintenanceQueue
), TIMEOUT 5000
IF @conv IS NULL
BEGIN
ROLLBACK TRAN
BREAK
END
-- get state
WHILE 1=1
BEGIN
WAITFOR (
RECEIVE TOP (1)
@message = message_body,
@group = conversation_group_id,
@type = message_type_name
FROM CentralMaintenanceQueue
WHERE conversation_group_id = @conv
), TIMEOUT 5000;
IF @@ROWCOUNT = 1
-- process message
EXEC ProcessCentralMaintenance
END
END
GO
CREATE
PROC ProcessCentralMaintenance
(
@message VARBINARY(MAX),
@conv UNIQUEIDENTIFIER,
@group UNIQUEIDENTIFIER,
@type NVARCHAR(256)
)
AS
IF @type = 'DEFAULT'
BEGIN
--process default message
SELECT @message
END
IF @type = 'WorkRequest'
BEGIN
DECLARE @ts VARCHAR(MAX);
SET @ts = CAST(GETDATE() AS VARCHAR(MAX));
DECLARE @xmlMsg XML
SET @xmlMsg = @message
SET @xmlMsg.modify('insert (
attribute timestamp{sql:variable("@ts")}
) into (/*[1])'),
SELECT @message, @conv;
SEND ON CONVERSATION @conv
Message Type WorkInProcess
(@message)
INSERT INTO PendingWork VALUES (@message)
END
IF @type =
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @conv
END
IF @type =
'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
-- handle error
SELECT @message
END
GO
CREATE QUEUE CentralMaintenanceQueue
WITH ACTIVATION (
PROCEDURE_NAME = CentralMaintenance,
MAX_QUEUE_READERS = 2,
EXECUTE AS SELF )
CREATE SERVICE CentralMaintenanceService
ON QUEUE CentralMaintenanceQueue
([DEFAULT], Maintenance)
Listing 11-9. Klamath application
CREATE XML SCHEMA COLLECTION WorkOrder
AS
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified"
targetNamespace="urn:maintenance"
xmlns="urn:maintenance">
<xs:element name="WorkRequest">
<xs:complexType>
<xs:sequence>
<xs:element name="Equipment" type="xs:string"/>
<xs:element name="Reason" type="xs:string"/>
</xs:sequence>
<xs:attribute name="timestamp" type="xs:string" use="required"/>
</xs:complexType>
</xs:element>
</xs:schema>
'
CREATE MESSAGE TYPE WorkInProcess
VALIDATION = VALID_XML WITH SCHEMA COLLECTION WorkOrder
CREATE MESSAGE TYPE WorkRequest
VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE WorkComplete
VALIDATION = EMPTY
CREATE CONTRACT Maintenance
(
WorkInProcess SENT BY TARGET,
WorkRequest SENT BY INITIATOR,
WorkComplete SENT BY TARGET
)
CREATE QUEUE InspectionQueue
CREATE SERVICE InspectionService
ON QUEUE InspectionQueue
DECLARE @workRequest UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @workRequest
FROM SERVICE InspectionService
TO SERVICE 'CentralMaintenanceService'
ON CONTRACT Maintenance
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @workRequest
MESSAGE TYPE WorkRequest
('
<WorkRequest xmlns="urn:maintenance">
<Equipment>1ccc2-37-BK</Equipment>
<Reason>Overheat</Reason>
</WorkRequest>'),
Where Are We?
SQL Server Service Broker is a platform that deeply integrates messaging into the database architecture and provides a framework for implementing business transactions as sagas—a series of related database transactions that may be distributed across time and systems.
We started this chapter by building some services that were contained in a single database; later, albeit with some configuration, we were able to move these applications to different servers without changing them.
Distributed applications introduce several other issues that applications that run on a single server do not have. Authentication is required for remote communication, and Service Broker leverages Windows authentication to handle this. Service Broker also can use X509 Certificates to do authentication when there is no common Windows domain between servers. Service Broker supports doing next-hop routing so that virtual networks can be implemented. Service Broker supports both point-to-point encryption, when message confidentiality is required, and end-to-end encryption, when message confidentiality is required across a virtual network. Space constraints limit this chapter from covering all these topics, but they are covered in Books Online. Search for “Service Broker Routing” and “Service Broker Dialog Security” for more discussion of these topics.