route_name

Is the name of the route to create. A new route is created in the current database and owned by the principal specified in the AUTHORIZATION clause. Server, database, and schema names cannot be specified. The route_name must be a valid sysname.

AUTHORIZATION owner_name

Sets the owner of the route to the specified database user or role. The owner_name can be the name of any valid user or role when the current user is a member of either the db_owner fixed database role or the sysadmin fixed server role. Otherwise, owner_name must be the name of the current user, the name of a user that the current user has IMPERSONATE permission for, or the name of a role to which the current user belongs. When this clause is omitted, the route belongs to the current user.

WITH

Introduces the clauses that define the route being created.

SERVICE_NAME = 'service_name'

Specifies the name of the remote service that this route points to. The service_name must exactly match the name the remote service uses. Service Broker uses a byte-by-byte comparison to match the service_name. In other words, the comparison is case sensitive and does not consider the current collation. If the SERVICE_NAME is omitted, this route matches any service name, but has lower priority for matching than a route that specifies a SERVICE_NAME. A route with a service name of 'SQL/ServiceBroker/BrokerConfiguration' is a route to a Broker Configuration Notice service. A route to this service might not specify a broker instance.

BROKER_INSTANCE = 'broker_instance_identifier'

Specifies the database that hosts the target service. The broker_instance_identifier parameter must be the broker instance identifier for the remote database, which can be obtained by running the following query in the selected database:

SELECT service_broker_guid
FROM sys.databases
WHERE database_id = DB_ID()

When the BROKER_INSTANCE clause is omitted, this route matches any broker instance. A route that matches any broker instance has higher priority for matching than routes with an explicit broker instance when the conversation does not specify a broker instance. For conversations that specify a broker instance, a route with a broker instance has higher priority than a route that matches any broker instance.

LIFETIME =route_lifetime

Specifies the time, in seconds, that SQL Server retains the route in the routing table. At the end of the lifetime, the route expires, and SQL Server no longer considers the route when choosing a route for a new conversation. If this clause is omitted, the route_lifetime is NULL and the route never expires.

ADDRESS ='next_hop_address'

Specifies the network address for this route. The next_hop_address specifies a TCP/IP address in the following format:

    TCP://{ dns_name | netbios_name | ip_address } : port_number

The specified port_number must match the port number for the Service Broker endpoint of an instance of SQL Server at the specified computer. This can be obtained by running the following query in the selected database:

SELECT tcpe.port
FROM sys.tcp_endpoints AS tcpe
INNER JOIN sys.service_broker_endpoints AS ssbe
   ON ssbe.endpoint_id = tcpe.endpoint_id
WHERE ssbe.name = N'MyServiceBrokerEndpoint';

When the service is hosted in a mirrored database, you must also specify the MIRROR_ADDRESS for the other instance that hosts a mirrored database. Otherwise, this route does not fail over to the mirror.

When a route specifies 'LOCAL' for the next_hop_address, the message is delivered to a service within the current instance of SQL Server.

When a route specifies 'TRANSPORT' for the next_hop_address, the network address is determined based on the network address in the name of the service. A route that specifies 'TRANSPORT' might not specify a service name or broker instance.

MIRROR_ADDRESS ='next_hop_mirror_address'

Specifies the network address for a mirrored database with one mirrored database hosted at the next_hop_address. The next_hop_mirror_address specifies a TCP/IP address in the following format:

TCP://{ dns_name | netbios_name | ip_address } : port_number

The specified port_number must match the port number for the Service Broker endpoint of an instance of SQL Server at the specified computer. This can be obtained by running the following query in the selected database:

SELECT tcpe.port
FROM sys.tcp_endpoints AS tcpe
INNER JOIN sys.service_broker_endpoints AS ssbe
   ON ssbe.endpoint_id = tcpe.endpoint_id
WHERE ssbe.name = N'MyServiceBrokerEndpoint';

When the MIRROR_ADDRESS is specified, the route must specify the SERVICE_NAME clause and the BROKER_INSTANCE clause. A route that specifies 'LOCAL' or 'TRANSPORT' for the next_hop_address might not specify a mirror address.

The routing table that stores the routes is a metadata table that can be read through the sys.routes catalog view. This catalog view can only be updated through the CREATE ROUTE, ALTER ROUTE, and DROP ROUTE statements.

By default, the routing table in each user database contains one route. This route is named AutoCreatedLocal. The route specifies 'LOCAL' for the next_hop_address and matches any service name and broker instance identifier.

When a route specifies 'TRANSPORT' for the next_hop_address, the network address is determined based on the name of the service. SQL Server can successfully process service names that begin with a network address in a format that is valid for a next_hop_address.

The routing table can contain any number of routes that specify the same service, network address, and broker instance identifier. In this case, Service Broker chooses a route using a procedure designed to find the most exact match between the information specified in the conversation and the information in the routing table. For more information about how Service Broker chooses a route, see Service Broker Routing.

Service Broker does not remove expired routes from the routing table. An expired route can be made active using the ALTER ROUTE statement.

A route cannot be a temporary object. Route names that start with # are allowed, but are permanent objects.

Permission for creating a route defaults to members of the db_ddladmin or db_owner fixed database roles and the sysadmin fixed server role.

A. Creating a TCP/IP route by using a DNS name

CREATE ROUTE ExpenseRoute
    WITH
    SERVICE_NAME = '//Adventure-Works.com/Expenses',
    BROKER_INSTANCE = 'D8D4D268-00A3-4C62-8F91-634B89C1E315',
    ADDRESS = 'TCP://www.Adventure-Works.com:1234' ;

B. Creating a TCP/IP route by using a NetBIOS name

CREATE ROUTE ExpenseRoute
    WITH 
    SERVICE_NAME = '//Adventure-Works.com/Expenses',
    BROKER_INSTANCE = 'D8D4D268-00A3-4C62-8F91-634B89C1E315',
    ADDRESS = 'TCP://SERVER02:1234' ;

C. Creating a TCP/IP route by using an IP address

CREATE ROUTE ExpenseRoute
    WITH
    SERVICE_NAME = '//Adventure-Works.com/Expenses',
    BROKER_INSTANCE = 'D8D4D268-00A3-4C62-8F91-634B89C1E315',
    ADDRESS = 'TCP://192.168.10.2:1234' ;

D. Creating a route to a forwarding broker

CREATE ROUTE ExpenseRoute
    WITH
    ADDRESS = 'TCP://dispatch.Adventure-Works.com' ; 

E. Creating a route to a local service

CREATE ROUTE LogRequests
    WITH
    SERVICE_NAME = '//Adventure-Works.com/LogRequests',
    ADDRESS = 'LOCAL' ;

F. Creating a route with a specified lifetime

CREATE ROUTE ExpenseRoute
    WITH
    SERVICE_NAME = '//Adventure-Works.com/Expenses',
    LIFETIME = 259200,
    ADDRESS = 'TCP://services.Adventure-Works.com:1234' ;

G. Creating a route to a mirrored database

CREATE ROUTE ExpenseRoute
    WITH
    SERVICE_NAME = '//Adventure-Works.com/Expenses',
    BROKER_INSTANCE = '69fcc80c-2239-4700-8437-1001ecddf933',
    ADDRESS = 'TCP://services.Adventure-Works.com:1234', 
    MIRROR_ADDRESS = 'TCP://services-mirror.Adventure-Works.com:1234' ;

H. Creating a route that uses the service name for routing

CREATE ROUTE TransportRoute
    WITH ADDRESS = 'TRANSPORT' ;

Reference

Concepts