Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 28, 2008

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

By Marcin Policht

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
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 //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]
 VALIDATION = NONE
  • 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:
 CREATE QUEUE qSend
 WITH STATUS = ON
 CREATE QUEUE qRecv
 WITH STATUS = ON
  • 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.
 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.

» See All Articles by Columnist Marcin Policht



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM