In today's complex and demanding environments, it is quite common for users to be able to submit requests faster than those requests can be processed. In some situations, this is because the system has an enormous number of users. In other situations, it is because the requests take a long time to process. In both cases, the system needs to be designed so that it can accept the requests immediately, and process those requests later on. Such a system is said to be asynchronous.

Asynchronous systems are typically built around queues. Queues function just like those long lines at the bank or movie theater, where people enter the line from one end and exit at the other. In computer terms, this is a FIFO or first-in-first-out data store. In an asynchronous system, the queues accept messages containing data about particular requests. The messages are stored until needed, and then deleted from the queue. This may sound very easy and straight-forward, but in practice it can be quite complex. A queuing system that is improperly implemented can cause an otherwise perfect system to fail miserably. The new Service Broker subsystem in SQL Server 2005 is designed to address this situation.

Service Broker Overview
Service Broker is a new feature of the SQL Server 2005 database engine. It helps developers build secure, reliable, and scalable applications. It provides queuing and messaging components that can be used inside a single instance of SQL Server, or inside a large, distributed, multi-server data farm. Inside a single server, Service Broker provides a highly-capable asynchronous programming model. This model is typically used to increase the responsiveness of an interactive application. In a multi-server data farm, Service Broker can also provide reliable and secure inter-server messaging. This can greatly increase the scalability of large applications. In both cases, sensitive data can be protected by the use of X.509 certificates and message encryption.

At a conceptual level, Service Broker is a queuing system. Messages are sent back and forth between queues. In this respect, it is similar to Microsoft Message Queue (MSMQ), and offers the following features:

• Messages are received once, and in the correct order.
• Related messages are grouped together.
• Messages can be delivered asynchronously.
• Message delivery can be scheduled.
• Message delivery can automatically trigger the activation of a service program.

Second, because SQL Server can now create and manage X.509 certificates, Service Broker messages can be encrypted. Data can be securely routed between far-flung servers without compromising security. Finally, transactional messaging in Service Broker is significantly faster than in MSMQ. This is a direct result of the database integration, and is a great boon to large systems.

Service Broker Objects
Let's look at an example of how you could use Service Broker to implement an asynchronous process. Assume that we have a system that must perform some long-running task. This task must be initiated by a Web application, and this Web application must appear very responsive to the user. The task may take a very long time to complete, so the request for this task must be made asynchronously. We've chosen to use Service Broker's queuing mechanism to decouple the task's initiation from its execution.

The first thing we must do is ensure that Service Broker has been enabled in the database we are going to use. In Listing 1, you can see an ALTER DATABASE statement that does indeed enable Service Broker. It is preceded by an IF statement that checks to see if Service Broker is already enabled. If it is, the ALTER DATABASE statement is not executed. There are two important points to note here. First, this is a normal ALTER DATABASE statement, and you will need the appropriate rights in order to execute it. Second, because it is an ALTER DATABASE statement, it requires exclusive access to the database. If there are other open connections to this database, then the statement will not succeed.

The next step is to define the types of messages that can be sent back and forth between our queues. Listing 2 shows this. This system will use two types of message, ASYNCREQUEST and ASYNCRESPONSE. Both message types will contain only valid XML documents, so their validation clause is set to WELL_FORMED_XML. (Alternatively, we could have specified that the messages must be empty, that they must be validated against one or more schemas, or that no validation should occur at all.) Note that the message type names are specified as full namespaces. Most of the objects in Server Broker are named this way. Listing 2 also contains the definition of a contract. The contract specifies which messages can be sent by whom. In Service Broker, messages are sent inside a dialog conversation, and dialog conversations have two sides. Our contract, called ASYNCCONTRACT, declares that the ASYNCREQUEST message can only be sent by the initiator of the conversation, and that the ASYNCRESPONSE message can only be sent by the target. This helps to ensure that our messages are used appropriately.

Listing 3 defines our queues and services. Queues are similar to tables, and they hold our messages until they can be processed. Services are conversation endpoints, and they represent each side of a dialog conversation. Here, we create two queues. The first, ASYNCREQUESTQUEUE, will hold messages that invoke our long-running task. The STATUS = ON clause makes the queue available to receive messages. The second queue, ASYNCRESPONSEQUEUE, will hold responses from the task. Queues receive their messages via services, so the listing defines two services. The first service, ASYNCREQUESTSERVICE, uses the ASYNCRESPONSE queue and the ASYNCCONTRACT contract. It may seem a little strange that the request service is assigned to the response queue, but this is typical. (It may help if you sketch this out on a piece of paper.) Similarly, the ASYNCRESPONSESERVICE uses the ASYNCREQUEST queue and the same ASYNCCONTRACT contract.

At this point, we've created all of the Service Broker objects that we need for this example. We have defined the two types of messages that can be used, and a contract that identifies the message rules. We've also created two queues that can accept and store the messages that will be sent between our two services. Now we are ready to write the stored procedures that will use these objects.

Service Broker Messaging
This example contains three stored procedures. The first procedure will be called by the client application and will submit the client's request to the request queue. The second procedure will accept this request message, and perform the work that the client requested. When complete, this procedure will submit a message to the response queue. The final procedure will accept this response message, and perform any final actions needed. (Typical actions include logging and/or sending email.) These procedures show how to send messages with Service Broker, so let's walk through each one.

The first procedure is called ASYNCREQUEST and is the only procedure that will be used by the client. Its job is to begin a Service Broker conversation and to send the message that initiates the entire process. Refer to Listing 4. (Insert Listing 4) You can see that the procedure accepts one parameter called @DELAY. (Because this is only an example, the system won't do any actual work. This way, we can concentrate on the Service Broker statements and not get bogged-down in system logic.) This parameter controls how long the system will pause when it processes the request. The procedure creates an XML document (actually a string) that contains the delay value. Now it gets interesting. The procedure creates a new Service Broker conversation using the BEGIN DIALOG CONVERSATION statement. This statement names the initiating and target services and the appropriate message contract. (Note the different syntax for the service names; this is not a typo.) It returns a conversation handle, which we must use when sending the actual message. The WITH ENCRYPTION = OFF clause disables message encryption, which is on by default. Message encryption is always recommended, especially in production environments. It is bypassed here in order to simplify the example. The final statement sends the actual message. The SEND ON CONVERSATION statement accepts the conversation handle created above, along with the appropriate message type. The XML document containing the message data is also specified here. (Note the semi-colon before the SEND. This is required if SEND is not the first statement in the SQL batch. Note also that the END CONVERSATION statement is NOT issued at this point. Ending the conversation too soon is a common mistake.) When this procedure executes, the ASYNCREQUEST message will be placed in the request queue, and will stay there until another procedure needs it.