The latest "new thing" in software development is Service Oriented Architecture (SOA). Although there are as many definitions of SOA as there are vendors selling SOA tools, the basic idea is that your application consists of coarse-grained, loosely coupled services connected with messages. The message format defines the interface between services. Although most vendors use Web Services to handle the messaging, this is not absolutely required. Loosely coupled, autonomous services make assembling an application from reusable pieces a straightforward process. Services can be either new applications or legacy applications wrapped with a services layer to handle communication with other services.

The services that make up an application might be running on different systems located in different locations. Using traditional RPC-style synchronous communications to connect services is an issue because if any one of the services is down or unreachable, the application is down. For this reason, connecting services with reliable, asynchronous messaging is preferred in most SOA applications. This connection method is preferred because if one service is unavailable, the application might continue to run. This makes Service Broker an ideal candidate for an SOA infrastructure. Reliable, asynchronous messaging means that if a service is unavailable the application can continue to run while messages for the unavailable service are reliably queued for later processing. Because messaging is tightly integrated with the database, all of SQL Server’s reliability and high-availability features, such as database mirroring, apply to messages as well as data. When a database-mirrored primary database fails over to the secondary database, the messages and the data in the database fail over to a transactionally consistent state. This means the application can be up and running again in a few seconds with no manual intervention and no loss of data.

The new development features of SQL Server 2005 enable a new class of rich data-oriented services that are hosted in a database. Using CLR stored procedures for the processing logic and Service Broker as an asynchronous execution environment, services can be contained entirely within a database. This includes all configuration and security. The advantage of this is that by detaching and attaching a database, a service can be transferred from an overloaded server to an idle server without ending active conversations or losing any messages or transactions. Moving and even duplicating services with no impact on the running application provides a great deal of flexibility in changing deployments in response to changing loads and conditions.

Service Broker queues and activation open up a number of possibilities for asynchronous database operations. For example, if you want to do some extensive operations in a trigger–such as creating a purchase order when an inventory update takes the on-hand quantity below the restock level–you can SEND a message to a Service Broker queue in the trigger and use an activated procedure to do the purchase order after the initiating transaction has completed. Because the majority of the work is done outside of the original transaction, the trigger has a minimal impact on performance of the main transaction. You could also write a stored procedure that uses Service Broker to start a number of asynchronous services in parallel and then waits for all of them to finish. The parallel service might be in the same database or spread out across a number of remote databases.

Because Service Broker ensures that messages on a dialog are processed in order, a dialog can be used to manage a multistep batch of SQL commands. If each message is a SQL statement or stored procedure to process, a service could be written that receives messages from the queue and executes the contents of the message by passing it to a SQL EXEC statement. A typical example of this is the night processing for a data warehouse. If the results of processing the statements are returned as response messages on the dialog, the initiator can collect the results of a number of batches. Statements that must be executed serially are placed in a dialog. Multiple dialogs can be started to enable parallel processing. Because Service Broker dialogs can be distributed, a single controlling database can start batches on a number of machines in a distributed environment. Reliable message delivery means the target machine doesn’t have to be running or connected when the batch is started. When the connection is restored, the batch will be delivered and processed.

With a little imagination, a service broker user can come up with a number of uses for Service Broker’s asynchronous execution capabilities. People who start using Service Broker for a specific project often end up using it for a number of other things once they discover the power of controlled asynchronous processing in a database.