Featured Database Articles

One of the more interesting innovations incorporated into the SQL Server 2005 platform is Service Broker. Designed as a scalable, secure, asynchronous messaging framework, it provides infrastructure for implementing reliable data exchange, which can be easily leveraged by local and distributed database applications. Since its core features are available in the entire product line (including Express Edition - although in somewhat limited capacity), we will include its overview as part of our series, presenting its basic concepts and architectural principles in this article. Starting with the next installment, we will describe a sample application illustrating its operations and continue our discussion focusing on its management, security, and routing characteristics.

While it was possible to implement asynchronous messaging in earlier versions of Microsoft SQL Server, such arrangements had to rely on other products or services, such as Microsoft Message Queuing (MSMQ). Even though this approach offered a number of additional benefits, including the ability to extend the scope of transactions to a variety of Windows applications (rather than being limited to SQL Server-based ones only) communicating via either TCP/IP or HTTP SOAP (remote instances of Service Broker are limited to TCP/IP connections), the new, built-in functionality improves performance (dynamically adjusting to volume of incoming requests) and scalability (maximum message size has been increased from 2MB to 4GB), as well as superior transactional behavior (MSMQ utilizes distributed model with two-phase commit, while Service Broker treats message exchange like any standard database transaction). Furthermore, the introduction of reliable messaging capabilities inherent to the database engine simplifies application development (eliminating the need for custom code dealing with multithreading and locking challenges) and administration (by streamlining common management tasks, such as backups and maintenance). It also provides advantages in the area of high availability and recoverability, by easily merging with such technologies as database mirroring or clustering.

As an asynchronous messaging framework, Service Broker accommodates the reliable exchange of messages among systems operating in distributed fashion, connected via slow or unreliable links, on different, potentially non-overlapping schedules. This eliminates the need for a direct communication between them and for a match of their processing capabilities (to prevent the volume of data submitted by the sender from overwhelming its recipient). It also offers resiliency to such events as the temporary unavailability of underlying infrastructure, including server failures or network interruptions. Delivery status is easily verifiable and messages are guaranteed to be received and processed in the proper order (following the sequence in which they were sent), ensuring application-wide consistency. This functionality is made possible by providing persistent storage (for messages and their metadata), as well as message sending, routing, and reception facilities, which are all implemented as database objects.

In order to gain a better understanding of the Service Broker architecture, it is important to become familiar with a number of components that constitute its building blocks (we list them below, including a short description of T-SQL statements that are used for their creation):

message type - included in the header of every message, identifies the type of data they contain. While XML content is commonly used, any datatype that can be cast to VARBINARY(MAX) is allowed. Database objects containing its definition are generated by executing the CREATE MESSAGE TYPE T-SQL statement. The optional VALIDATION parameter gives you the ability to enforce a specific XML schema or opt out of type checking (to avoid performance impact associated with this process). Message types must match between databases that participate in Service Broker-based communication. As mentioned earlier, messages can accommodate data chunks of up to 2GB in size.

contract - defines message types that specific participants of Service Broker-based exchange are permitted to use. It is represented by a database object, residing in the same databases as message type objects it references and is generated by executing the CREATE CONTRACT T-SQL statement. The party initiating communication (known as INITIATOR) and its partner (referred to as TARGET) might be assigned distinct message types, designated by values allocated to SENT BY INITIATOR and SENT BY TARGET arguments. (it is also possible to use SENT BY ANY option if there are message types available to both).

queue - provides storage for holding messages while they await processing. (The sending queue hosts messages awaiting delivery and receiving queue accepts them once delivery successfully completes and message type is confirmed to be valid). Queues are created with the CREATE QUEUE T-SQL statement. Its STATUS argument allows you to turn it ON or OFF (depending on whether you intend to make it operational). By applying the RETENTION switch (useful primarily in debugging scenarios) you have ability to ensure that all messages are kept in the queue until both parties agree on ending the communication. The optional ACTIVATION parameter allows you to associate a message arrival with an arbitrary action (known as a service program or queue reader), that can be implemented as a stored procedure (T-SQL or CLR-based) or an external program. Service Broker has the ability to activate additional instances of service programs (restricted by the value of the MAX_QUEUE_READERS parameter) to handle a backlog of messages in the queue.

service - constitutes the logical representation of an endpoint in Service Broker-based communication. Services function as initiators and targets for message exchange. Each of them is associated with specific queues and contracts (which, in turn, reference specific message types), linking them together into a single entity. Their implementation involves executing the CREATE SERVICE T-SQL statement, which in addition to containing their name, specifies the owner (via the AUTHORIZATION parameter), associated queue (following the ON QUEUE clause), and one or more contracts (note that this argument is not required for initiator, because contracts are always specified when starting Service Broker conversation).

conversation (which, in the SQL Server 2005 is synonymous with a dialog, since monolog conversations were not implemented in its RTM version) - corresponds to two-way, ordered, and asynchronous communication between Service Broker services (which can be located on the same or distinct SQL Server 2005 instances). They facilitate message exchange and ensure its reliability and consistency. To initiate a dialog, you need to launch the BEGIN DIALOG CONVERSATION T-SQL statement, which assigns a unique identifier to it, specifies the initiator (FROM SERVICE clause), target (TO SERVICE clause), and contract (optionally, using ON CONTRACT syntax), as well as defines LIFETIME (maximum amount of time in seconds for which the dialog remains active) and ENCRYPTION (which enforces encryption of messages). To terminate a conversation in an orderly manner, execute the END CONVERSATION T-SQL statement referencing its unique identifier (which precludes exchange of any additional messages).

conversation group - is associated with every conversation (by default, a new group is generated automatically when a conversation is initiated) in order to set the boundaries of the Service Broker locking mechanism. In this way, any message that is sent or received as part of a conversation becomes part of a transaction, which must complete before another message is allowed to be sent or received within the same group. Effectively, messages for each conversation group are always processed sequentially, eliminating issues that would otherwise need to be addressed when developing multithreaded applications (in particular, those resulting from messages arriving out-of-order or being simultaneously processed by different threads). Conversation group can consist of one or more conversations (in case of the latter, grouping is done based on their transactional dependencies, which typically reflect their common purpose). In order to assign a new conversation to an existing conversation group, specify its unique identifier when invoking BEGIN DIALOG CONVERSATION T-SQL statement (alternatively, you can set its value to the WITH RELATED_CONVERSATION_GROUP parameter).

Service Broker endpoint - facilitates network transmission of messages between SQL Server 2005 instances residing on separate computers. They are defined using the CREATE ENDPOINT T-SQL statement with the FOR SERVICE BROKER qualifier, which assigns a listener TCP port (with LISTENER PORT clause), defines its STATE (STARTED or STOPPED), as well as specifies authentication (via AUTHENTICATION) and encryption (via ENCRYPTION) settings.

route - associates a Service Broker service with a network location of a computer hosting the SQL Server 2005 installation. This information makes it possible to establish a delivery path between any two services that intend to engage in a conversation. Service Broker relies on TCP/IP-based protocols, consisting of Adjacent Broker Protocol (which handles lower-level transport tasks) and Dialog Protocol (which implements higher level features, responsible for session management between endpoints, including message delivery acknowledgements and retries, as well as authentication and encryption). To define a route, use the CREATE ROUTE T-SQL statement, which allows you to set such properties as target service name (assigned to the value of SERVICE_NAME parameter), amount of time the route remains in routing table database object (LIFETIME), and network address of the next route hop (ADDRESS) in the form of an IP address (alternatively, you can specify corresponding NetBIOS or DNS name) combined with an unused TCP port.

In the next article of our series, we will review the process of establishing conversation between two Service Broker services in the context of SQL Server 2005 Express Edition and present its sample implementation.