Featured Database Articles

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
GO
GRANT SEND ON
SERVICE::[//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend]
TO Public
-- on srvEnt01
USE dbEnt01
GO
GRANT SEND ON
SERVICE::[//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv]
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):

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)

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.