Service broker dynamic routing for return routes

Question

I am configuring service broker dynamic routing to dynamically create routes between services in all SQL servers in our enterprise. I can already create new routes or refresh expired routes to route any sent message into the correct target service. However when the target service responds to the received messages, a 2nd route is needed for each unique initiator: the route back from the target service into the initiator service. Since initiators in any of the databases can start a conversation into a single central target service, a return route with the same service name needs to be created for every unique initiator service, the only thing different (apart from the route name) on the routes is the database guid. In the MissingRoute message however only the name of the initiator service is specified, not the database guid. Service broker knows which copy of the initiator service it needs to answer, but it doesn't include this information in the MissingRoute message, so I don't know into which server + database I need to create a route for these return paths.

Is there a way to make Service Broker pass the service broker guid in the "http://schemas.microsoft.com/SQL/ServiceBroker/BrokerConfigurationNotice/MissingRoute" message too? Or do I need to find the service broker guid for the initiator service elsewhere? Please advise as I have kind of already sold the concept to our dba thinking I had it all figured out. And he's now expecting it any day now...

---

SQL expert for JF Hillebrand IT BV - The Netherlands.

Answer 1

anyone can help?

Answer 2

I think I can get at the instance id for the missing return/reply-route(s) via the sys.transmission_queue. Does anyone have any suggestions wether I'm missing something or doing anyhting wrong if I follow this path?

declare @msg xml;
declare @xmlRequestRoute xml;


declare @MissingServices table (
   service_name sysname collate Latin1_General_BIN not null
);

-- Remove previous results.
delete @MissingServices;

select @msg = convert(xml, @binMessageBody);

-- Retrieve the service name(s) we need to create a route for from the MissingRoute message.
with xmlnamespaces( 'http://schemas.microsoft.com/SQL/ServiceBroker/BrokerConfigurationNotice/MissingRoute' as c)
insert @MissingServices( service_name)
select m.r.value('c:SERVICE_NAME[1]','sysname') as SERVICE_NAME
from @msg.nodes('c:MissingRoute') m(r);

-- Now lookup in the transmission_queue which service instance(s) we need to 
-- provide route(s) for: for a reply route the broker instance is explicity specified
-- and a route(s) should exist having the exact same service_name plus broker_instance_id
-- combination, for an initial message normally a request is sent out with a null broker_
-- instance_id and a route(s) with the specified service name and a null 
-- broker_instance_id is needed.
-- Plural route(s) is specified because for specific purposes multiple routes may be 
-- configured for a single service instance. This code should be prepared to handle these 
-- multiple route situations too.
--insert @MissingRoutes( service_name, broker_instance)
insert dr.RequestedRoutes( conversation_handle, service_name, broker_instance)
select @conversation_handle,
   ms.service_name,
   inst.to_broker_instance
from @MissingServices ms
   left outer join (
         -- Look for the instance(s) of the given services
         -- in the transmission_queue
         select tq.to_service_name, tq.to_broker_instance
         from sys.transmission_queue tq
         group by tq.to_service_name, tq.to_broker_instance
      ) inst on (inst.to_service_name = ms.service_name);

-- Generate an xml message to send to the central repository.
with xmlnamespaces( default 'http://schemas.hillebrandgroup.com/DynamicRouting/RequestRoute/1/0')
select @xmlRequestRoute = (
      select @from_server_name as from_server_name,
         @from_db_name as from_db_name,
         @from_broker_instance as from_broker_instance,
         getutcdate() as request_time,
         (
            select
               mr.service_name as [service_name],
               mr.broker_instance as [broker_instance]
            from dr.RequestedRoutes mr
            where mr.conversation_handle = @conversation_handle
            for xml path('To'), type, elements xsinil
         )
      for xml path('RequestRoute'), type
   );

The above code intends to read the "MissingRoute" message received from Service Broker to get the names for the services that a route is needed for. Then it looks up in the sys.transmission_queue all outstanding conversations directed at this service name, grouping by the to_service_name and to_broker_instance. For all unique combinations a route must be made available: my script aims to generate an xml message with all available information so a central repository can look up the needed route.

Please comment.

---

SQL expert for JF Hillebrand IT BV - The Netherlands.

Answer 3

Even if the above proposal works, I still have some follow up questions. Here's the context that I am working from:

Since the request for a route to the central repository is an asynchronous call I will have to wait for an answer to come back before I can actually create the new route. Also I need to end the conversation on the MissingRoute conversation at some point: as far as I've read Service Broker will retry the routing as soon as I end the MissingRoute conversation, so I should only end that conversation after I've created the new route(s).

To write proper handler routines for this I need to know some more details about the behaviour of the SQL/ServiceBroker/BrokerConfiguration service:

• Can the SQL/ServiceBroker/BrokerConfiguration conversations ever be ended by the initiator side? (for example by an Error message?)
• Does it do any harm if I end the BrokerConfiguration conversation before the missing route(s) are created?
• Does it do any harm if the BrokerConfiguration conversation is not ended for a long time (f.e. due to the central service not (yet) responding)?

---

SQL expert for JF Hillebrand IT BV - The Netherlands.

Answer 4

I created a bug  report on Connect for the broker guid missing in the MissingRoute message sent to the BCS:

BCS MissingRoute message should include service broker guid for established conversations

However, MS think we can do without this and have closed it already, without providing any information on if my work around would be correct or not or any other information. Please let them know you think they are wrong by voting 'UP' on the above Connect item.

---

SQL expert for JF Hillebrand IT BV - The Netherlands.

Answer 5

More information on Dynamic Routing for Service Broker using the Broker Configuration Service (BCS) can be found at Remus Rusanu's site: Dynamic Routing.
And at MS technet: Dynamic Routing and Service Broker routing in general.

---

SQL expert for JF Hillebrand IT BV - The Netherlands.