Create Route

Hi

I have a very simple and probably stupid question. I am new to SB. My question is when I create a service and queue do I have to create a route always?. whats the purpose of creating a route?. what happens if i dont create a route. As I understand creating a route creates a routing table in the database but i am perplexed as to what is the actual use of this routing table and in what way it helps.

Thanks

AK

In the BEGIN DIALOG statement you provide a string as the TO service name:

begin dialog conversation @.handle

from service [initiator]

to service 'target'

on contract [contract]

with encryption = off;

The [initiator] and [contract] are names of actual objects in the database (identifiers), but the 'target' is just any name, a string. What a route does it instructs Service Broker where the service named 'target' is actually located. You create a route like this:

create route [route_to_target]

with service_name = 'target',

address 'tcp://someserver:4022';

which maps the name 'target' to the address 'tcp://someserver:4022'. So a route is always required when you want to reach a service that is located on a different SQL Server instance. You'll see that normally you never have to create a route for cases when both [initiator] and 'target' services are in the same database or the same SQL Server instance. This is because all databases by default contain a route named AutoCreatedLocal that allows any service within the local SQL Server instance to be addressed.

A secondary role for routes is indirection between logical named and physical location. An alternative approach would had been to specify somehow the location in the BEGIN DIALOG, like this:

begin dialog conversation @.handle

from service [initiator]

to service 'tcp://someserver:4022/target'

on contract [contract]

with encryption = off;

but in this moment the application has hardcoded the location of the 'target' service in it's code. In practice once an application gets deployed the physical location of machines hosting services often changes, say the machine 'someserver' gets upgraded to a machine named 'superdome', so the location of the the service 'target' has in fact moved to 'tcp://superdome:4022'. In this case, the administrator can change the route in the database, w/o an application code change beeing necessary (the application still initiates the dialogs with the service 'target', not knowing the actual physical location). Same is true for the case when the database hosting the 'target' service is moved (sp_detach/sp_attach) to a different SQL Server instance.

A third role for routes is to provide support for scale-out load balancing scenarios, by allowing multiple routes for the same service.

A fourth role for routes is to provide support for services hosted in mirrored database (the MIRROR_ADDRESS parameter of CREATE/ALTER ROUTE).