Service Broker Routing Examples
This section presents examples of the Service Broker routing process. Each example contains sample routing tables for AdventureWorks and msdb, and describes how Service Broker uses those routing tables to choose a route for the message.
The routing tables presented in this topic are simplified versions of the sys.routes catalog view. The route id and the owner are not important for the routing process, and all routes are considered to have an indefinite lifetime.
A value of NULL in the remote_service_name column matches any service name. A value of NULL in the broker_instance column matches any Service Broker identifier.
The examples for outgoing messages do not use the routing table in msdb, and the examples for incoming messages and message forwarding do not use the routing table for AdventureWorks.
Example 1: Default Configuration
This example describes the default configuration for Service Broker routing. By default, all databases except master contain the AutoCreatedLocal route. Therefore, the routing tables for AdventureWorks and msdb contain the following information.
AdventureWorks.sys.routes
| name | remote_service_name | broker_instance | address | mirror_address |
|---|---|---|---|---|
AutoCreatedLocal |
NULL |
NULL |
LOCAL |
NULL |
msdb.sys.routes
| name | remote_service_name | broker_instance | address | mirror_address |
|---|---|---|---|---|
AutoCreatedLocal |
NULL |
NULL |
LOCAL |
NULL |
In this case, all dialogs created in the AdventureWorks database are delivered to a service in the current instance. In addition, all dialogs arriving from outside the instance are delivered to a service in the current instance.
For conversations created in AdventureWorks, AutoCreatedLocal is the only route in AdventureWorks.sys.routes. That route is the only route in the set of matching routes, and Service Broker chooses that route. If the service for the message does not exist in the local instance, the message is dropped.
The process is the same for conversations that arrive from outside of the instance. For conversations that arrive from outside of the instance, AutoCreatedLocal is the only route in msdb.sys.routes. That route is the only route in the set of matching routes, and Service Broker chooses that route. If the service for the message does not exist in the local instance, the message is dropped.
Example 2: Network Route to a Specific Service
This example describes the typical routing configuration for services outside the current instance. To configure a route to an external service, create the route in the database that begins the conversation. In this example, AdventureWorks contains a route for the service OrderParts. The route contains a network address for the OrderParts service.
AdventureWorks.sys.routes
| name | remote_service_name | broker_instance | address | mirror_address |
|---|---|---|---|---|
AutoCreatedLocal |
NULL |
NULL |
LOCAL |
NULL |
OrderPartsRoute |
OrderParts |
NULL |
tcp://host2.Adventure-Works.com:4022/ |
NULL |
msdb.sys.routes
| name | remote_service_name | broker_instance | address | mirror_address |
|---|---|---|---|---|
AutoCreatedLocal |
NULL |
NULL |
LOCAL |
NULL |
In this case, all dialogs created in the AdventureWorks database to the service OrderParts match the route OrderPartsRoute. Service Broker sends these messages to the network address tcp://host2.Adventure-Works.com:4022/. All other conversations are delivered to services in the same instance.
For conversations created in AdventureWorks with a target service of OrderParts, the set of matching routes contains OrderPartsRoute, since this route exactly matches the service name. OrderPartsRoute is the only route in the set of matching routes, so Service Broker chooses that route.
For conversations created in AdventureWorks to a different target service, the set of matching routes contains AutoCreatedLocal. Since this is the only route in the set of matching routes, Service Broker chooses that route. If the service for the message does not exist in the local instance, Service Broker marks the conversation DELAYED.
For conversations that arrive from outside of the instance, AutoCreatedLocal is the only route in msdb.sys.routes. That route is the only route in the set of matching routes, and Service Broker chooses that route. If the service for the message does not exist in the local instance, the message is dropped.
Example 3: Network Route to a Mirrored Database
This example describes the typical routing configuration for a service hosted by a mirrored database outside the current instance. To configure a route to an external service, create the route in the database that begins the conversation. In this example, AdventureWorks contains a route for the service OrderParts. The route contains both a network address and a mirror address for the OrderParts service.
AdventureWorks.sys.routes
| name | remote_service_name | broker_instance | address | mirror_address |
|---|---|---|---|---|
AutoCreatedLocal |
NULL |
NULL |
LOCAL |
NULL |
OrderPartsRoute |
OrderParts |
NULL |
tcp://partner1.Adventure-Works.com:4022/ |
tcp://partner2.Adventure-Works.com:4022/ |
msdb.sys.routes
| name | remote_service_name | broker_instance | address | mirror_address |
|---|---|---|---|---|
AutoCreatedLocal |
NULL |
NULL |
LOCAL |
NULL |
For conversations created in AdventureWorks with a target service of OrderParts, the set of matching routes contains OrderPartsRoute, since this route exactly matches the service name. OrderPartsRoute is the only route in the set of matching routes, so Service Broker chooses that route. Service Broker checks both the address and the mirror address to determine which partner is the principal, and then sends the message to the principal.
For conversations created in AdventureWorks to a different target service, the set of matching routes contains AutoCreatedLocal. Since this is the only route in the set of matching routes, Service Broker chooses that route. If the service for the message does not exist in the local instance, Service Broker marks the conversation DELAYED.
For conversations that arrive from outside of the instance, AutoCreatedLocal is the only route in msdb.sys.routes. That route is the only route in the set of matching routes, and Service Broker chooses that route. If the service for the message does not exist in the local instance, the message is dropped.
Example 4: Network Route to All External Services
This example sends messages from services in AdventureWorks to a different instance unless the service exists in the local instance. Notice that messages for any services that are not in the local instance go to the same network address. This configuration may be useful if the SQL Server instance at that network address performs message forwarding.
In this example, the AdventureWorks database contains the AutoCreatedLocal route as well as a route to the address tcp://forwarding.Adventure-Works.com:4022/.
AdventureWorks.sys.routes
| name | remote_service_name | broker_instance | address | mirror_address |
|---|---|---|---|---|
AutoCreatedLocal |
NULL |
NULL |
LOCAL |
NULL |
ExternalRoute |
NULL |
NULL |
tcp://forwarding.Adventure-Works.com:4022/ |
NULL |
msdb.sys.routes
| name | remote_service_name | broker_instance | address | mirror_address |
|---|---|---|---|---|
AutoCreatedLocal |
NULL |
NULL |
LOCAL |
NULL |
For conversations created in AdventureWorks, the set of matching routes contains both AutoCreatedLocal and ExternalRoute, since the remote service name and broker instance is the same for both routes. Service Broker must choose between these two routes. Service Broker chooses routes with the address 'LOCAL' before routes that specify a network address, so Service Broker first chooses AutoCreatedLocal. If the target service exists in the local instance, Service Broker uses this route and delivers the message to that service. However, if the target service does not exist in the local instance, Service Broker chooses ExternalRoute.
For conversations that arrive from outside of the instance, AutoCreatedLocal is the only route in msdb.sys.routes. That route is the only route in the set of matching routes, and Service Broker chooses that route. If the service for the message does not exist in the local instance, the message is dropped.
Example 5: Network Route to Different Instances of a Service
This example shows a routing configuration where two different network addresses host different instances of the same service. This configuration may be useful for a load balancing configuration.
In this example, the AdventureWorks database contains the AutoCreatedLocal route as well as routes to the service BalancedService.
AdventureWorks.sys.routes
| name | remote_service_name | broker_instance | address | mirror_address |
|---|---|---|---|---|
AutoCreatedLocal |
NULL |
NULL |
LOCAL |
NULL |
BalancedRouteOne |
BalancedService |
5fb8d92b-ed69-4c80-afbb-2aa6a7d3cb2d |
tcp://server1.Adventure-Works.com:4022/ |
NULL |
BalancedRouteTwo |
BalancedService |
81b1d3d0-288e-4d2c-b1d3-456cbb944b4f |
tcp://server2.Adventure-Works.com:4022/ |
NULL |
msdb.sys.routes
| name | remote_service_name | broker_instance | address | mirror_address |
|---|---|---|---|---|
AutoCreatedLocal |
NULL |
NULL |
LOCAL |
NULL |
For conversations created in the AdventureWorks database to the service BalancedService that do not specify a Service Broker identifier, the set of matching routes contains either BalancedRouteOne and BalancedRouteTwo. Since the routes contain different Service Broker identifiers, the matching process arbitrarily selects a Service Broker identifier and matches that route. Since only one route matches, Service Broker chooses that route for the conversation. The result is that some conversations route to tcp://server1.Adventure-Works.com:4022/, and other conversations route to tcp://server2.Adventure-Works.com:4022/. However, once Service Broker receives an acknowledgement for a message in a conversation, Service Broker uses the Service Broker identifier contained in the acknowledgement for other messages in the conversation. Once the first acknowledgement is received, all future messages on the conversation are routed using the Service Broker identifier in the acknowledgement.
For conversations created in the AdventureWorks database to the service BalancedService that specify one of the Service Broker identifiers in the routing table, the set of matching routes contains the route that matches the Service Broker identifier. The conversation routes to the address in the route with that Service Broker identifier.
For conversations created in AdventureWorks to a different target service, the set of matching routes contains only AutoCreatedLocal. Service Broker chooses that route. If the service for the message does not exist in the local instance, Service Broker marks the conversation DELAYED.
For conversations that arrive from outside of the instance, AutoCreatedLocal is the only route in msdb.sys.routes. That route is the only route in the set of matching routes, and Service Broker chooses that route. If the service for the message does not exist in the local instance, the message is dropped.
Example 6: Message Forwarding for a Specific Service
This example forwards messages from outside the local instance to the service ElsewhereService to the network address tcp://elsewhere.Adventure-Works.com:4022/. For all other services, Service Broker delivers the messages to a service in the local instance or marks the conversation DELAYED if the service does not exist in the local instance.
AdventureWorks.sys.routes
| name | remote_service_name | broker_instance | address | mirror_address |
|---|---|---|---|---|
AutoCreatedLocal |
NULL |
NULL |
LOCAL |
NULL |
msdb.sys.routes
| name | remote_service_name | broker_instance | address | mirror_address |
|---|---|---|---|---|
AutoCreatedLocal |
NULL |
NULL |
LOCAL |
NULL |
ForwardingRoute |
ElsewhereService |
NULL |
tcp://elsewhere.Adventure-Works.com:4022/ |
NULL |
For conversations created in AdventureWorks, AutoCreatedLocal is the only route in AdventureWorks.sys.routes. That route is the only route in the set of matching routes, and Service Broker chooses that route. If the service for the message does not exist in the local instance, Service Broker marks the conversation DELAYED. Notice that a conversation created in AdventureWorks to the service ElsewhereService does not route to tcp://elsewhere.Adventure-Works.com:4022/.
For conversations that arrive from outside of the instance to the service ElsewhereService, the route ForwardingRoute exactly matches the service name. Therefore, ForwardingRoute is the only route in the set of matching routes, and Service Broker chooses that route when message forwarding is on. Service Broker chooses this route even if the local instance contains the service ElsewhereService. If message forwarding is off, Service Broker drops the message.
For conversations that arrive from outside of the instance to all other services, AutoCreatedLocal is the only matching route in msdb.sys.routes. Service Broker chooses that route. If the service for the message does not exist in the local instance, the message is dropped.
Example 7: Message Forwarding for All Services Not in the Instance
This example sends messages from outside the local instance to a different instance unless the service exists in the local instance. Notice that messages for all external services go to the same network address. This configuration may be useful for message forwarding.
AdventureWorks.sys.routes
| name | remote_service_name | broker_instance | address | mirror_address |
|---|---|---|---|---|
AutoCreatedLocal |
NULL |
NULL |
LOCAL |
NULL |
msdb.sys.routes
| name | remote_service_name | broker_instance | address | mirror_address |
|---|---|---|---|---|
AutoCreatedLocal |
NULL |
NULL |
LOCAL |
NULL |
ForwardingRoute |
NULL |
NULL |
tcp://forwarding.Adventure-Works.com:4022/ |
NULL |
For conversations created in AdventureWorks, AutoCreatedLocal is the only route in AdventureWorks.sys.routes. That route is the only route in the set of matching routes, and Service Broker chooses that route. If the service for the message does not exist in the local instance, Service Broker marks the conversation DELAYED.
For conversations that arrive from outside the database, the set of matching routes contains both AutoCreatedLocal and ForwardingRoute, since both routes specify the same remote service name and Service Broker identifier. Service Broker must choose between these two routes. Service Broker chooses routes with the address 'LOCAL' before routes that specify a network address, so Service Broker first chooses AutoCreatedLocal. If the target service exists in the local instance, Service Broker uses this route and delivers the message to that service. However, if the target service does not exist in the local instance, and message forwarding is on, Service Broker chooses ForwardingRoute. If message forwarding is not on, Service Broker drops the message if the target service does not exist in the local instance.
See Also
Concepts
Other Resources
CREATE ROUTE (Transact-SQL)
sys.routes (Transact-SQL)