So how does one deploy Service Broker services in a large enterprise? Hundreds of services that change location every now and then (just enough to create a major outage exactly at the wrong moment!), and each database requiring a route to any service it wishes to interact with. Creating hundreds and thousands of routes and maintaining them as each new service is deployed, is retired or is moved is a difficult task and obviously prone to operational mistakes, pretty much a disaster waiting to happen.
One solution to this problem is to use a ‘dynamic routing service’, as described in the Service Broker routing algorithm http://technet.microsoft.com/en-us/library/ms166052.aspx:
4. If a route to a dynamic routing service is present, and no request for a route to the service is pending, mark the conversation delayed and request routing information from that service.
What is a ‘dynamic routing service’? Is just an ordinary Service Broker service, implemented by your enterprise/environment and responsible to provide routing information for all the services in the environment is deployed in. Whenever the Service Broker needs to route message to a service with an unknown location it will request the ‘dynamic routing service’ to find the location of the unknown service and provide the appropriate routing information. The request for routing information is, obviously, a Service Broker message delivered to the ‘dynamic routing service’ queue. The ‘dynamic routing service’ is expected to actually create a route in the database and then ‘respond’ to the request by ending the dialog.
Example
Let’s see how the ‘dynamic routing’ behaves in practice. So we’ll create a ‘dynamic routing service’ and watch it in action. For this example we’ll consider that a newly deployed ‘MyDepartament/MyService’ application/service needs to exchange messages with the ‘MyEnterprise/Accounting’ service. So go ahead and create this ‘application’:
create queue [MyDepartament/MyService/Queue];
create service [MyDepartament/MyService]
on queue [MyDepartament/MyService/Queue];
go
When this application initiates a dialog with the ‘MyEnterprise/Accounting’ service, messages will just sit in sys.transmission_queue because Service Broker needs to know the location of this service, it needs a route. Let’s have the ‘application’ send a message (for simplicity will use the implicit [DEFAULT] contract and message type):
begin transaction;
declare @h uniqueidentifier;
begin dialog conversation @h
from service [MyDepartament/MyService]
to service N'MyEnterprise/Accounting'
with encryption = off;
send on conversation @h (N'<request account="FOO" information="credit limit"></request>');
commit;
go
If we check the sys.transmission_queue, we found the message there delayed because there is no routing information about the location of the service:
select transmission_status from sys.transmission_queue;
transmission_status
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied.
(1 row(s) affected)
Let’s see how things change if we introduce a ‘dynamic routing service’. Note that the service name must be SQL/ServiceBroker/BrokerConfiguration:
create queue [SQL/ServiceBroker/BrokerConfiguration/Queue];
create service [SQL/ServiceBroker/BrokerConfiguration]
on queue [SQL/ServiceBroker/BrokerConfiguration/Queue]
([http://schemas.microsoft.com/SQL/ServiceBroker/BrokerConfigurationNotice]);
go
This doesn’t change anything so far, because the routing algorithm described at http://technet.microsoft.com/en-us/library/ms166052.aspx says that a route to this service has to exist in order that ‘dynamic routing’ functionality is used. So let’s go ahead and create this route:
create route [SQL/ServiceBroker/BrokerConfiguration]
with service_name = N'SQL/ServiceBroker/BrokerConfiguration',
address = N'LOCAL';
As soon as we add this route, two messages appear in the ‘dynamic routing service’ queue:
select cast(message_body as xml), message_type_name
from [SQL/ServiceBroker/BrokerConfiguration/Queue]
message_type_name
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
<MissingRemoteServiceBinding xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/BrokerConfigurationNotice/MissingRemoteServiceBinding"><SERVICE_NAME>MyEnterprise/Accounting</SERVICE_NAME></MissingRemoteServiceBinding> http://schemas.microsoft.com/SQL/ServiceBroker/BrokerConfigurationNotice/MissingRemoteServiceBinding
<MissingRoute xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/BrokerConfigurationNotice/MissingRoute"><SERVICE_NAME>MyEnterprise/Accounting</SERVICE_NAME></MissingRoute> http://schemas.microsoft.com/SQL/ServiceBroker/BrokerConfigurationNotice/MissingRoute
(2 row(s) affected)
We’ll ignore the message type [http://schemas.microsoft.com/SQL/ServiceBroker/BrokerConfigurationNotice/MissingRemoteServiceBinding] for the moment, so let’s focus on the ‘MissingRoute’ one:
<MissingRoute xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/BrokerConfigurationNotice/MissingRoute">
<SERVICE_NAME>MyEnterprise/Accounting</SERVICE_NAME>
</MissingRoute>
So the ‘dynamic routing service’ was requested to provide routing information about the service ‘MyEnterprise/Accounting’. The ‘dynamic routing service’ has to find the location of this service, create a route to it and end the conversation on which this request was sent. We’ll cheat for the moment and create route manually, then end the conversation manually:
create route [MyEnterprise/Accounting]
with
service_name = N'MyEnterprise/Accounting',
address = N'tcp://accountingserver.myenterprisedomain.org:4022';
go
begin transaction
declare @h uniqueidentifier;
receive
@h = conversation_handle
from [SQL/ServiceBroker/BrokerConfiguration/Queue];
if @h is not null
begin
end conversation @h;
end
commit;
go
This example shows how the ‘dynamic routing service’ functionality works and how the Service Broker routing algorithm will use this service whenever it needs to find the location of an unknown service. Of course, the difficult part is actually implementing this service correctly J
Dynamic Routing Service requirements
- Has to be bound to the ([http://schemas.microsoft.com/SQL/ServiceBroker/BrokerConfigurationNotice] contract in order to accept the MissingRoute messages
- Has to be able to actually find the information about the location of any service in the enterprise
- Has to create the ‘missing route’ with the information retrieved at the above step
- Needs to end incoming request conversations
- Has to adapt to any changes in the location of service as services are moved, retired or new services are added
Although the Service Broker algorithm only looks up a route to the ‘dynamic routing service’ (meaning the service could be located anywhere), the requirement to create the actual ROUTE in the database means that the normal implementation of this service is in the same database where the application is. That is, every deployed database in the enterprise contains a copy of this service. One can think of this service much like an automation script to provide ‘dynamic routing’. This leaves the question on how is the ‘dynamic routing service’ capable to find the location of any service? Usually this implies a central repository where all services are registered and the ‘dynamic routing service’ interrogates this central repository.
As for the requirement to adapt to services being moved and decommissioned, the best approach is simply to rely on a expiration time associated with each request. In fact, the ‘dynamic routing service’ doesn’t even have to implement and track this expiration time; it can simply use the CREATE ROUTE statement itself and provide a lifetime after which the route expires:
create route [MyEnterprise/Accounting]
with
service_name = N'MyEnterprise/Accounting',
address = N'tcp://accountingserver.myenterprisedomain.org:4022',
LIFETIME = 3600;
When the newly created route expires, a new request will be sent to the ‘dynamic routing service’. The actual expiration lifetime value is dependent on how fast is desired that the system reacts to a service being relocated and how often are such relocation expected to occur. Reasonable lifetimes are in the range of 5-10 minutes up to hours. Days would probably be too long and cause long times of unavailability if an unexpired route keeps sending messages to a service at the old location, sorter times will result in too many lookups on the central repository and cause unnecessary contention.
The Central Repository
So how is the ‘dynamic routing service’ supposed to interrogate the central repository about the location of the ‘unknown’ services? Here are some alternatives:
- Use Service Broker. Although it seems like a chicken and egg problem, it is not J If Service Broker is used to interrogate the central repository, then the necessary routes to reach the ‘central repository have to be pre-deployed with each database, because otherwise the ‘dynamic routing service’ will not be able to find the location … of the central repository service.
- Use Active Directory. The ‘dynamic routing service’ could use an ADSI linked server to query the Active Directory using LDAP. Of course, this means that your Active Directory environment has to contain new schema objects for Service Broker services. The Windows 2003 Active Directory schema contains objects like ‘SQL Server Instance’ and ‘SQL Server Database’, but it does not contain ‘SQL Server Service Broker Service’. Another approach is to use the Active Directory ‘Service-Connection-Point’ objects and provide the service address as the ‘Service-Binding-Information’ property.
- Use a file share, simply lookup a well known share where a each known service contains a describing its location (perhaps a ‘service listing’ created with the Service Listing Manager tool)
- Use a Web Service
If I would do such a Central Repository, of course, I would choose option 1 J. The Active Directory option is also interesting, but I have to recon that I could not implement it when I tried. But my knowledge and understanding of AD is quite minimal, and I had to deal with an environment where I was restricted severely in what I could do/modify in the AD (the actual Redmond MS domain). If you implement this successfully, drop me a note about it, I’m actually interested how it’s done.
Another subject of interest is how to keep this Central Repository up to date. One could use Event Notifications for this! Even more, the ‘dynamic routing service’ itself can be extended to accept the [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] contract and subscribe to the CREATE/ALTER/DROP SERVICE events:
create event notification [SQL/ServiceBroker/BrokerConfiguration/Services]
on database
for create_service, alter_service, drop_service
to service N'SQL/ServiceBroker/BrokerConfiguration', 'current database';
This event notification subscription will send a message to our service each time a service is created, altered or dropped in the database. Since the ‘dynamic routing service’ has the means to communicate with the Central Repository in the first place, it can use these means to add, modify and drop service information in the repository. The service would also have to deal with services being moved around, either by databases that are detached/attached to a different instance, or by SQL Server host machines being renamed, but I will not drill further into this topic.
Gotcha!
Whenever a route to the ‘dynamic routing service’ is added, a pretty unexpected thing happens: all of the services in the database become all of the sudden unreachable! This is because, according to the routing algorithm described at http://technet.microsoft.com/en-us/library/ms166052.aspx they are usually reached via the default ‘AutoCreatedLocal’ route at step 5 in the algorithm:
5. Find a route that does not specify either the service name or the service broker identifier
But when a ‘dynamic routing service’ is added, the step 4 takes precedence and thus all local services are suddenly ‘delayed’ expecting an answer from the ‘dynamic routing service’! To prevent this from happening, whenever one adds the route to the ‘dynamic routing service’ it should also add an explicit ‘LOCAL’ route to the built-in services:
-- Because we enabled dynamic routing, all of the existing
-- services in the database are sudenly unreacheable, unless an
-- explicit route is created for them. this includes the built-in services
-- like Event Notifications and Query Notifications, as well as the
-- 'ServiceBroker' service itself.
--
create route [http://schemas.microsoft.com/SQL/Notifications/EventNotificationService]
with service_name = N'http://schemas.microsoft.com/SQL/Notifications/EventNotificationService',
address = N'LOCAL';
create route [http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService]
with service_name = N'http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService',
address = N'LOCAL';
create route [http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker]
with service_name = N'http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker',
address = N'LOCAL';
go