VSM Cover Story

Program SQL Server 2005's Service Broker

SQL Server 2005's versatile Service Broker infrastructure enables asynchronous messaging between databases and server instances, handles database server event notifications, responds to database change notifications, and sends Database Mail.

Technology Toolbox: VB.NET, SQL Server 2005, XML, Other: Visual Studio 2005 Standard (or higher) or Visual Basic Express edition, SQL Server 2005 Developer Edition or higher (SP-1 March CTP or later optional), and the Northwind sample database (go here). SQL Server Express has Service Broker restrictions.

SQL Server 2005's new Service Broker (SSB) component integrates asynchronous programming, queuing, and reliable messaging services with the Database Engine.

SSB applications can send and receive messages between service endpoints, such as server instances, databases, and .NET 2.0 clients. The most common message format is a Unicode string containing a well-formed XML document that SSB sends to and retrieves from a pair of message queues as a SQL Server 2005 native XML datatype.

Alternatively, messages can use the varbinary or varbinary(max) datatype. In either case, the maximum SSB message size is 2 GB (one billion Unicode characters.) Many data-intensive VS 2005 projects use custom SQL Server projects to manage message queues stored in tables. Writing T-SQL stored procedures or ADO.NET code to manage queues effectively is a daunting task, especially for endpoints on multiple remote databases; testing such projects is even more challenging. SSB provides a complete, asynchronous, exactly-once-in-order messaging infrastructure that you can set up and manage with a basic set of T-SQL statements.

Service Broker also provides the infrastructure that underlies new Event Notifications, Query Notifications, and Database Mail features. (Database Mail replaces SQL Server 2000 and earlier versions' SQL Mail, which SQL Server 2005 deprecates.) Your first encounter with SSB will probably be when you attempt to implement one of these three features. Understanding how messaging in the Database Engine works is important when you're designing and troubleshooting any SSB-based application.

So I'll begin by briefly describing SSB's feature set and demonstrating a simple messaging scenario with T-SQL scripts from the \ServiceBroker\SimpleSSBScripts folder of this article's sample code. Then I'll show you how to use Service Broker to implement query notifications, also known as database change notifications (DCN, see this PDF). DCN is today's most common Service Broker application because it lets Web developers refresh ASP.NET 2.0's page cache at the optimum interval to guarantee data currency. I'll also show you how to implement Event Notifications.

The article's sample code download includes SQL Server Management Studio (SSMS) projects that demonstrate simple and complex SSB applications, Query Notifications, Event Notifications, and Database Mail. You need SQL Server 2005 Developer Edition, or higher, to run most sample projects because SQL Server Express (SSX) includes the SSB client components only. SSX also lacks support for Database Mail. The SSB client receives messages from or sends messages to or through higher-level SQL Server 2005 editions' SSB only. However, SSX does support Query Notifications.

Service-oriented architecture (SOA) uses asynchronous messaging to support loosely-coupled applications, so Microsoft proposes SSB as the infrastructure for Service-Oriented Database Architecture (SODA). The SODA approach is reminiscent of simple Windows Communication Foundation (WCF, formerly Indigo), Microsoft Message Queue (MSMQ), and BizTalk Server applications. You can take advantage of SODA to orchestrate data-management workflows with stored procedure instances that activate upon receipt of the first of one or more messages, and then process each set of related messages within a transaction. Activating multiple stored-procedure instances delivers scalability as message traffic load increases; a single instance can handle only so many messages before performance deteriorates.

Inside a Service Broker App
A Service Broker application consists of three basic component groups: Conversations and dialogs, which exchange message sets between initiator and target queues at run time; service definitions, contracts, and queues, which specify the application's message types, conversation flow, and database storage at design time; and TCP/IP routes and endpoints, which specify the locations of services when sending messages between databases or SQL Server instances. Queues provide first-in-first-out (FIFO) message storage in hidden tables that restrict INSERT and DELETE operations to the SSB application, but support conventional SELECT queries. SSB offers validation of XML messages as being well-formed or conforming to an XML schema that you add to the database's XML SCHEMA COLLECTION.

Setting up a Service Broker application for a single SQL Server 2005 instance requires executing T-SQL statements to create MESSAGE TYPE, CONTRACT, QUEUE and SERVICE database objects for the initiating service's endpoint. Target endpoints within the same database share the MESSAGE TYPE and CONTRACT objects, but require their own QUEUE and SERVICE objects (see Figure 1 and Listing 1). SSMS offers simple templates for most SSB objects. Unfortunately, the templates don't include the full CREATE SSBOBJECT syntax that sets default values and shows you what options for SSB objects are available. You'll probably find writing SSB code from scratch is faster than modifying the rather cryptic templates.

You must GRANT CREATE permission for all SSB objects to users who need to create the objects, assuming these users aren't members of the sysadmin, ddl_admin, or db_owner role. Developing production SSB projects requires defining a substantial number of objects, so DBAs usually run the T-SQL scripts to create the service applications. Users with lower privileges typically need CREATE SCHEMA permission, VIEW DEFINITION permission on related SSB objects, REFERENCES permission for CONTRACTs, SEND permission on the target SERVICE initiator, and QUEUEs and RECEIVE permission on target QUEUEs. Sushil Chordia's Data Access Blog provides detailed SQL Server permission requirements for creating and running SSB projects.

Test Simple Examples
SSB applications that pass messages between service endpoints in the same database simplify coding and testing "Hello World"-style demos (see Figure 2, and Listings 2 and 3). However, such trivial examples are useful only for educational purposes. Implementing services that pass messages between databases in the same instance, or a remote server instance, is a much more involved process. Passing messages between databases requires a ROUTE object for each SERVICE. If dialog security isn't turned off, you must also use authentication CERTIFICATEs for the initiating and target users in both databases, as well as a REMOTE SERVICE BINDING for the initiating service and user. Certificate-based authentication also requires executing a CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'LongComplexPassword' command in each database. The REMOTE SERVICE BINDING specifies the user's certificate as the credential you use to initiate a conversation with the remote service and encrypt the message.

Services that pass messages between SQL Server instances need ENDPOINT objects, and also require the ROUTE objects to specify the target instance's DNS name, NetBIOS name or IP address, and port number (see Figure 3). Messages passed between server instances are encrypted. The sample code's InitiatorTargetSSB.ssmssln project uses certificate-based authentication for messaging between databases in the same SQL Server instance. Remus Rusanu's Service Listing Manager, released in mid-April 2006, reduces the effort needed to deploy inter-instance SSB projects significantly by managing certificate exchange and other setup operations for you (see get it here).

Alternatively, you can use SQL Server dialog security with Windows authentication for services in the same domain, or a trusted Active Directory domain, by executing this batch—with a different TCP port number—in the target and initiator databases:

CREATE ENDPOINT EndPointName
STATE = STARTED
AS TCP (LISTENER_PORT = {4023 | 4024})
FOR SERVICE_BROKER (AUTHENTICATION = 
WINDOWS);
CREATE LOGIN DomainName\UserName 
FROM WINDOWS;
GRANT CONNECT ON ENDPOINT :: EndPointName
TO DomainName\UserName;

You must create an AD Service Principal Name (SPN) for each Service Broker instance by executing SetSPN.exe if the SQL Server instances that use Windows authentication run under the LocalSystem or NETWORK SERVICE account, rather than an Active Directory user account:

SETSPN -A MSSqlSvc/MachineName:{4023 | 4024} 
MachineName

SetSPN.exe is one of the Windows XP Service Pack 2 and Windows Server 2003 SP1 Support Tools. You can install SetSPN.exe with \Support\Tools\SupTools.msi from the Windows Server 2003 CD ROM or download WindowsXP-KB838079-SupportTools-ENU.exe from MSDN. Some security configurations might prevent executing SetSPN.exe, so credential-based authorization is the more universal method.

SQL Server Management Studio's Object Explorer exposes most persistent SSB objects—including predefined SSB object types—under subnodes of the ...\InstanceName\DatabaseName\Service Broker node. It exposes custom service endpoints, if any, under the ...\InstanceName\Server Objects\Endpoints\Service Broker node (see Figure 4). Right-clicking on an SSB item generates a CREATE SSBOBJECT script to a Query Editor window, a file or the Clipboard. The script includes default option values when applicable.

Intercept SQL Server Events
SQL Server 2005 Event Notifications are the asynchronous alternative to DDL triggers, and enable SQLTrace events to perform programmed operations on databases or instances. Event Notifications, which aren't related to Notification Services, use an SSB target service and queue to receive notifications. SSB provides http://schemas.microsoft.com/SQL/Notifications/PostEventNotification as the standard contract and message type for initiating notifications.

Implementing a SQL Server Event Notifications target service requires creating activation PROCEDURE, QUEUE ... WITH ACTIVATION, and SERVICE objects in a new or existing database with statements similar to these:

CREATE PROCEDURE LogEventsProc AS ... ;
GO
CREATE QUEUE NotifyQueue WITH ACTIVATION
(STATUS = ON, 
PROCEDURE_NAME = LogEventsProc,
MAX_QUEUE_READERS = 2, 
EXECUTE AS SELF) ;
GO
CREATE SERVICE NotifyService
ON QUEUE NotifyQueue
([http://schemas.microsoft.com/SQL/ 
  Notifications/PostEventNotification]);
GO

The code for the LogEventsProc procedure that processes the Event Notification XML document is similar to that of Listing 3.

Adding an event notification to the target queue with a dialog conversation requires specifying the event name, scope (SERVER or DATABASE), event, event group or SQLTrace, and target service name:

CREATE EVENT NOTIFICATION 
CreateDatabaseNotification
ON SERVER
FOR CREATE_DATABASE
TO SERVICE 'NotifyService', 
'current database' ;

The sys.event_notification_event_types system view lists the 193 event and SQLTrace names you can substitute for CREATE_DATABASE. If you change the scope from SERVER to DATABASE and the target service is in a different database, you must substitute for 'current database' the SSB GUID for the target service that you obtain by executing this code:

SELECT service_broker_guid FROM sys.databases
WHERE name = 'TargetServiceDatabaseName';

Now look at this example of an XML message_body document for a typical CREATE DATABASE event:

<EventType>CREATE_DATABASE</EventType>
<PostTime>2006-04-07T13:12:45.410</PostTime>
<SPID>53</SPID>
<ServerName>OAKLEAF-MS15</ServerName>
<LoginName>OAKLEAF\RogerJ</LoginName>
<DatabaseName>TestNotification</DatabaseName>
<TSQLCommand>
<CommandText>
CREATE Database TestNotification ;
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>

The sample code includes a modified version of the EventLogging.ssmssln project from the SQL Server Database Engine samples. The modifications supply a missing note about -- and the query for -- substituting the service_broker_guid value for 'current database' for the CREATE TABLE event notification. The modifications also remove a duplicate CREATE DATABASE command.

Caching relational data by middle-tier applications is a common practice; caching in RAM greatly reduces database disk input/output (I/O) operations and delivers substantial performance improvement. The downside of caching is the potential for delivering stale data to the client tier and, ultimately, to the applications' users. Application architects and administrators must balance the requirement for up-to-the-minute data and the performance degradation that results from frequent cache refreshes. In most cases, conservative business practices dictate overly-frequent cache invalidation.

Production databases commonly contain three classes of tables: lookup, catalog, and transaction. Updates to the content of lookup tables occur either infrequently or at predictable intervals. For example, lookup tables of Western hemisphere and European political subdivisions—such as cities, counties, and states or provinces—usually need updating annually, or even less often. DBAs might refresh financial rollup tables every week, postal code tables every month, and taxation tables every quarter or year. Updates to semi-static lookup tables can be scheduled on holidays, at night, or during other low-demand periods when middle-tier cache regeneration affects the fewest users. Implementing query notification services for lookup-table updates seldom increases overall application efficiency.

Catalog tables—typically product descriptions, parts lists, bills of material, or vendor/brand data—change more frequently than lookup tables and usually at random intervals. Catalog tables tend to have greater field lengths and commonly include thumbnail or larger image columns. Query notifications provide the greatest bang for your buck with catalog tables, especially if you can partition the tables by the degree of content volatility. Transaction tables should be highly volatile and thus not cached on the middle tier, so query notifications don't apply to them.

The Query Notifications feature is based on a combination of SQL Server indexed view, SSB, and ADO.NET 2.0 technologies. All SQL Server 2005 editions support indexed views, which create a physical copy of a query's rowset (see Figure 6). Any change to the query's rowset—including altering or dropping participating table(s), and starting or stopping the server instance—signals the need to update the indexed view. Query Notifications rely on this signal to enqueue an SSB message that the data has changed. However, SQL Server doesn't build the indexed view, and the message doesn't include details on how the data changed. The SqlCommand object's query for the notification must meet the rules for indexed view queries. For example, the SELECT UnitsInStock, UnitsOnOrder, ReorderLevel FROM dbo.Products statement works because it has an explicit column list (not *) and the table name includes the schema prefix, dbo in this case. SELECT TOP 10 UnitsInStock, UnitsOnOrder, ReorderLevel FROM Products would fail because it includes a TOP n option and the table name's schema prefix is missing.

Query Notifications offers standard and custom implementations. The standard implementation for the System.Data.SqlClient.SqlDependency class employs a prebuilt SSB contract with .../PostQueryNotification as its name and .../QueryNotification as the message type:

CREATE CONTRACT
[http://schemas.microsoft.com/SQL/Notifications/ _
PostQueryNotification] AUTHORIZATION [dbo] 
([http://schemas.microsoft.com/SQL/Notifications/ _
QueryNotification] SENT BY INITIATOR)

Starting an ADO.NET 2.0 SqlDependency instance (see Listing 4) turns on a TCP or HTTP listener on the client and generates a service of the same name and a new SqlQueryNotificationService queue:

CREATE QUEUE [dbo].[
SqlQueryNotificationService-
7396fc73-768a-4b0e-8e66-37574125b7a3] 
WITH STATUS = ON , RETENTION = OFF, 
ACTIVATION (  STATUS = ON, 
PROCEDURE_NAME = 
[dbo].[SqlQueryNotificationStoredProcedure-
7396fc73-768a-4b0e-8e66-37574125b7a3], 
MAX_QUEUE_READERS = 1, 
EXECUTE AS OWNER  ) ON [PRIMARY]

CREATE SERVICE [
SqlQueryNotificationService- _
7396fc73-768a-4b0e-8e66-
37574125b7a3] 
AUTHORIZATION [dbo]  
ON QUEUE [dbo].[
SqlQueryNotificationService- _
7396fc73-768a-4b0e-8e66-
37574125b7a3] ([http://schemas.microsoft.com/
SQL/Notifications/ _
PostQueryNotification])

The queue specifies an associated activation stored procedure (see Listing 5) that drops the queue, procedure, and service on receipt of an http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer message to clean up unneeded instances.

Client-side ADO.NET Windows Form code creates an SqlCommand with the required SELECT query (see Listing 4 again), attaches the SqlDependency object to the command, adds a handler for the SqlDependency.OnChange event, and executes an SqlDataReader to turn on the QueryNotification service. Using the standard SqlDependency approach frees you from writing any T-SQL to create the SSB objects. Add code to the SqlDependency_OnChange event handler to invalidate the middle-tier cache, perform other operations, or both. The sample code opens a dialog that displays information about the event from the SqlNotificationEventArgs' Type property, and its Source and Info enumerations. The sample code also includes examples of custom Query Notifications for which you define the QUEUE, SERVICE and, optionally, PROCEDURE and ROUTE objects. The sample code also shows you how to replace the SqlDependency instance with SqlNotificationRequest. In this case, you must poll the queue to detect data changes.

ASP.NET 2.0 encapsulates SqlDependency with SqlCacheDependency. You can enable cache invalidation by calling this procedure from Page.Load:

Sub CreateSqlCacheDependency(cmdCache _
As SqlCommand)
Dim depCache As _
New SqlCacheDepedency(cmdCache)
Response.Cache.SetCacheability( _
HttpCacheability.Public)
Response.Cache.SetValidUntilExpires(True)
Response.AddCacheDependency(depCache)
End Sub

Alternatively, you can enable ASP.NET 2.0 cache invalidation for all SqlCommands on a page with this OutputCache directive <%@ OutputCache Duration="999999" SqlDependency="CommandNotification" VaryByParam="none" %>. Another option is to enable cache invalidation for the SELECT query of an SqlDataSource control with this directive:

<asp:SqlDataSource EnableCaching="true" 
SqlCacheDependency="CommandNotification" 
CacheDuration="Infinite" ... />.

"Loosely-coupled" and "service-oriented" compete with "Web 2.0" and "user-generated content" for the "most overused buzzwords du jour" title. But SSB, Query Notifications, Event Notifications, and Database Mail provide the tools you need to move a wide range of database projects to the brave, new world of asynchronous database messaging applications without requiring a Windows Communication Foundation.

More Information

- Visit the SQL Server Broker site

- “An Introduction to SQL Server Service Broker” by Roger Wolter

- “Building Distributed, Asynchronous Database Applications with the Service Broker” by Gerald Hinson and Roger Wolter (DAT303 from PDC 2005)

- Remus Rusanu’s blog: “Service Broker Conversations”

- Sushil Chordia’s blog

- Roger Wolters’ blog: “Have Data Will Travel”

- Rushi Desai’s blog: “Write Ahead”

- Check out the minimum database permission requirements for Service Broker applications

- Get peer support from the ServiceBroker newsgroup

- “Database Change Notifications: Primitives for Efficient Database Query Result Caching” by Cesar Galindo-Legaria, Torsten Grabs, Christian Kleinerman and Florian Waas

- Learn about SODA in the"Service Oriented Database Architecture: App Server-Lite?" PDF by David Campbell

- SQL Express users should download and install SQL Server Management Studio Express (SSMSE) from the SP1 Advanced Services update, Toolkit installer, or SSMSE installer

- Download Remus Rusanu's Service Listing Manager to simplify deploying SSB applications across multiple servers

- Download and execute T-SQL scripts to create the Northwind and pubs SQL Server 2005 sample databases: Search MSDN for "SQL2000SampleDb.msi"

comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.