SQL Server 2005 Express Edition – Part 27 – Implementing Basic Service Broker Objects

In the previous installment of our series dedicated to features available in the SQL Server 2005 Express Edition, we have presented basic concepts of the Service Broker by defining the primary elements of its asynchronous messaging framework built directly into the product. Now it is time to explain their interaction and describe a process of setting up the database objects necessary to demonstrate their sample implementation (which will be the subject of our next article).

As we have discussed earlier, Service Broker functionality significantly simplifies development of applications that require the ability to exchange data in a reliable, asynchronous manner. This is accomplished by providing a mechanism for communication (referred to as a conversation or dialog) between two parties (known as services) based on a mutually agreed contract (which specifies permitted message types to be used for its duration). Individual messages are stored in queues (which take the form of hidden database tables), where they await delivery and subsequent processing by service programs (running arbitrarily assigned stored procedures or external executables). Transactional mechanisms incorporated into the framework eliminate the possibility of duplicate or out-of-order messages within a designated set of related conversations (called conversation groups). By the virtue of the asynchronous nature of data exchange, initiator (which starts a dialog) and its target do not need to operate concurrently or have matching processing capacities to maintain conversation (however, the latter is capable of dynamically activating its resources in order to adjust to a volume of incoming messages). Furthermore, with all of Service Broker components residing in a database (as its objects), it is possible to incorporate their configuration and management into already established operational procedures (such as backups, restores or maintenance tasks). Communication security is ensured through integrated Windows authentication and certificate support.

In order to better understand these principles, let’s take a closer look at the way they function in practice by reviewing a few simple examples illustrating the creation of relevant database objects and their role in message exchange. Since this functionality depends on having the Service Broker feature enabled in databases where initiator and target services participating in a conversation reside, you should verify and (if needed) modify its state by running the following T-SQL statements. (The first one generates a list containing the name of every database in the local instance along with current Service Broker activation status for each and the latter changes it to the appropriate value for the database myDB).

USE master
SELECT name, is_broker_enabled FROM sys.databases

In our first example, we will create two services coexisting within a single database (which eliminates the need for endpoints and routes). For the sake of simplicity, we will omit any error checking and refrain from such enhancements as message validation, activation, or encryption (which will be presented in subsequent articles of this series). However, we will introduce commonly used nomenclature of Service Broker objects (such as message types, contracts, or services that need to be unique in order to facilitate enterprise-wide communication), which follows the Universal Resource Identifier (URI) convention (in our case, we will use an arbitrary namespace called //databaseJournal.com/SQL2005EX/ServiceBroker for this purpose). While this is not a requirement, such an approach eliminates the possibility of name clashes, especially in larger, distributed environments. With these considerations in mind, the process of implementing a Service Broker dialog will consist of the following steps:

  • creating a message type (in our case, no validation will be performed):
 CREATE MESSAGE TYPE [//databaseJournal.com/SQL2005EX/ServiceBroker/msgNV]

  • creating a contract allowing the newly defined message type to be used by both an initiator and a target (as indicated by the SENT BY ANY clause):
 CREATE CONTRACT [//databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV]
([//databaseJournal.com/SQL2005EX/ServiceBroker/msgNV] SENT BY ANY )

  • creating active (determined by the WITH STATUS = ON clause) queues for initiator and target services:

  • creating initiator and target services utilizing the newly defined queues (and associated with them contracts):
 CREATE SERVICE [//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend]
ON QUEUE qSend ([//databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV])
CREATE SERVICE [//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv]
ON QUEUE qRecv ([//databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV])

  • At this point, we could potentially start a conversation between our initiator and target and track its progress by examining content of their respective queues (as well as some Service Broker-related system views introduced in SQL Server 2005, which we will discuss in our next article). However, to make our analysis easier, we will capture their dynamically changing content into a standard database table, whose format will match their pre-defined structure. In particular, we will collect the value of a handle uniquely identifying each conversation, message type name (since conversations might involve, besides our custom [//databaseJournal.com/SQL2005EX/ServiceBroker/msgNV], also system-generated message types), message status (with 0 indicating ready, 1 received, 2 not complete, and 3 retained sent message), service name (either [//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend] or [//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv]), service contract name (our [//databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV]), message type validation (which can be set to Empty, None, or XML), message body (its actual payload), and the timestamp indicating when the message has been processed. (Note that this is different from date and time of its arrival into the queue). For the full listing of columns of Service Broker queues, refer to the RECEIVE (Transact-SQL) article in the SQL Server 2005 Books Online.
msgTypeName SYSNAME,
status TINYINT,
srvName NVARCHAR(512),
srvConName NVARCHAR(256),
msgTypeValidation NCHAR(2),
msgBody NVARCHAR(50),

With these basic constructs in place, our upcoming tasks will involve initiating a dialog, processing its messages (by invoking stored procedures that retrieve them from each queue, parse their content, and populate our custom table), monitoring its progress (by examining the content of queues and system views), and finally handling graceful termination. We will review each of these actions in detail in the next article of our series.

» See All Articles by Columnist Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles