SQL Server 2005 Express Edition - Part 27 - Implementing Basic Service Broker Objects
July 28, 2008
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
USE master SELECT name, is_broker_enabled FROM sys.databases GO ALTER DATABASE myDB SET ENABLE_BROKER GO
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
CREATE MESSAGE TYPE [//databaseJournal.com/SQL2005EX/ServiceBroker/msgNV] VALIDATION = NONE
CREATE CONTRACT [//databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV] ([//databaseJournal.com/SQL2005EX/ServiceBroker/msgNV] SENT BY ANY )
CREATE QUEUE qSend WITH STATUS = ON CREATE QUEUE qRecv WITH STATUS = ON
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])
CREATE TABLE tbMsgs ( convHandle UNIQUEIDENTIFIER, msgTypeName SYSNAME, status TINYINT, srvName NVARCHAR(512), srvConName NVARCHAR(256), msgTypeValidation NCHAR(2), msgBody NVARCHAR(50), cdatetime DATETIME DEFAULT CURRENT_TIMESTAMP);
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.