SQL Server 2005 Express Edition - Part 31 - Distributed Service Broker Environment - Routing

Monday Sep 22nd 2008 by Marcin Policht

The previous installment of our series began an overview of a distributed Service Broker environment. This article describes the remaining steps necessary to facilitate communication between services residing on separate computers in the same Windows domain, including assigning permissions to execute Service Broker-specific tasks and defining Adjacent Broker Protocol routes.

In the previous installment of our series covering features available in SQL Server 2005 Express Edition, we began an overview of a distributed Service Broker environment, by describing the most essential requirements for its sample implementation. For the sake of simplicity, we decided to exclude (for the time being) more advanced functionality such as encryption or certificate-based authentication, focusing instead on a configuration that relies on Active Directory Kerberos protocol to control access to endpoints. In this article, we will continue our presentation, describing the remaining steps necessary to facilitate communication between services residing on separate computers in the same Windows domain, including assigning permissions to execute Service Broker-specific tasks and defining Adjacent Broker Protocol routes.

As mentioned earlier, in the absence of dialog-level security (dependent on use of certificates, which we intentionally excluded from the scope of our current discussion), operations performed on behalf of a remote service in a local database run in the security context of the Public fixed database role. Effectively, we will have to ensure that this role has the ability to invoke the SEND statement. This can be accomplished by running the following:

-- on srvExp01
USE dbExp01
 TO Public
-- on srvEnt01
USE dbEnt01
 TO Public

Note that the equivalent requirement applies to permissions necessary to launch the RECEIVE statement against a local queue, if its execution is triggered by a remote initiator service. This is a fairly common scenario that involves the use of the activation feature, which associates the queue with a stored procedure or an application automatically retrieving newly delivered messages (we will be covering it in one of our upcoming articles). In our case, however, such a provision is not necessary, since we will be running RECEIVE interactively (as in the single-database example we have presented previously).

In order for a message to be delivered to a remote queue, Service Broker has to be able to identify its location. This need is facilitated by defining a route, which provides a mapping between a service associated with that queue (identified by its name, as part of the SERVICE_NAME clause) and its respective network destination (incorporated into the ADDRESS clause). The latter takes the format of a string composed of the protocol designation (TCP), server DNS or NetBIOS name or IP address, and the Service Broker endpoint port (in our case, these should match the entry that was registered earlier as the servicePrincipalName attribute of the computer's Active Directory account):

-- on srvExp01
USE dbSBExp01
 SERVICE_NAME = '//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv',
 ADDRESS = 'TCP://srvEnt01.DatabaseJournal.com:4022'
-- on srvEnt01
USE dbSBEnt01
 SERVICE_NAME = '//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend',
 ADDRESS = 'TCP://srvExp01.DatabaseJournal.com:4022'

When creating a route, it is possible to point to a specific broker instance (using its GUID, which you can obtain by querying value of the service_broker_guid column of sys.databases view present in the row corresponding to a database hosting the target service) to prevent any potential misdirection of messages (resulting, for example, from accidental use of duplicate service names). You can verify the outcome of the commands listed above by examining the content of the sys.routes tables in both databases. You should notice that each of them, in addition to the newly created routes, also contains an entry named AutoCreatedLocal, with address field set to LOCAL and the NULL value as its remote_service_name. Its presence is important, since it automatically enables conversations between services residing on the same SQL Server 2005 instance, without the need for defining any routes manually. (The search starts in the current database and, if the match is not found, continues in others, according to built-in precedence rules). The ADDRESS parameter can also be set to TRANSPORT (by running the CREATE ROUTE statement), which results in the value of remote_service_name being used to route messages to their network destination (with the assumption that both are equivalent). This considerably simplifies configuration of scenarios where large numbers of initiators communicate with a single target, by restricting the amount of entries you have to keep track of (limiting them to IP addresses or computer names and ports used by Service Broker endpoints).

Once a message arrives at the target endpoint (as determined based on the information contained in the route definition), its remote service entry is compared against rows in the sys.routes table in the msdb database. Depending on the results of this comparison, the message can be redirected to one of the local databases or forwarded to another SQL Server 2005 instance (the latter needs to be enabled on the Service Broker endpoint level using MESSAGE_FORWARDING = ENABLED clause). Note that Service Broker offers a number of additional enhancements that further strengthen its routing capabilities. For example, it supports load balancing (on per-conversation basis) if there are multiple routes defined for the same service. It also protects against routing loops and can accommodate redundancy provided by database mirroring (with MIRROR_ADDRESS clause) or clustering (when communicating with SQL Server 2005 Enterprise Edition).

The last task that remains to be completed before we can initiate a conversation between two services is the creation of two auxiliary tables that will be used to store the content of messages exchanged between them. As before, this is not required for a Service Broker dialog to take place, but is used strictly as an example of a custom action that can be fairly easily incorporated into our generic implementation (in addition to helping us track its course). The table structure remains the same as in the earlier presented, single-database example, however, this time, it needs to be created in both databases (dbSBExp01 and dbSBEnt01) on their respective servers (srvExp01 and srvEnt01)

 msgTypeName SYSNAME,
 status TINYINT,
 srvName NVARCHAR(512),
 srvConName NVARCHAR(256),
 msgTypeValidation NCHAR(2),
 msgBody NVARCHAR(50),

Note that we could also leverage the RETENTION property of qSend and qRecv queues to monitor progress of our sample conversation (although you should keep in mind space and performance implications of such a decision). Once enabled (by specifying WITH RETENTION = ON clause of the ALTER QUEUE statement), all messages are retained until the dialog is over, with the value of status column in the respective queue indicating their current state:

  • 1 - message has arrived in the queue and is ready to be processed (with RECEIVE T-SQL statement). Messages with this status will appear in a queue regardless of the RETENTION setting, however with its value set to OFF, they are removed from the queue as soon as their processing completes.
  • 2 - message has arrived in the queue out of order, so processing can not be take place until all of its predecessors arrive.
  • 3 - message has been sent. Entries with this status accumulate in the sending queue only if RETENTION is turned ON.

With a pair of tbMsgs tables created, we are ready to initiate a sample Service Broker conversation, which will be described in the next article of our series.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved