ALTER ROUTE (Transact-SQL)

Modifies route information for an existing route.

Transact-SQL Syntax Conventions

Syntax

ALTER ROUTE route_name
WITH  
  [ SERVICE_NAME = 'service_name' [ , ] ]
  [ BROKER_INSTANCE = 'broker_instance' [ , ] ]
  [ LIFETIME = route_lifetime [ , ] ]
  [ ADDRESS =  'next_hop_address' [ , ] ]
  [ MIRROR_ADDRESS = 'next_hop_mirror_address' ]
[ ; ]

Arguments

• route_name
  Is the name of the route to change. Server, database, and schema names cannot be specified.

• WITH
  Introduces the clauses that define the route being altered.

• 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 take into account the current collation. A route with a service name of 'SQL/ServiceBroker/BrokerConfiguration' is a route to a Broker Configuration Notice service. A route to this service may not specify a broker instance.

  If the SERVICE_NAME clause is omitted, the service name for the route is unchanged.

• BROKER_INSTANCE ='broker_instance'
  Specifies the database that hosts the target service. The broker_instance 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, the broker instance for the route is unchanged.

• LIFETIME **=**route_lifetime
  Specifies the amount of 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 lifetime of the route is unchanged.

• 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 a SQL Server instance 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 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' may specify a service name or broker instance.

  When the next_hop_address is the principal server for a database mirror, you must also specify the MIRROR_ADDRESS for the mirror server. Otherwise, this route does not automatically failover to the mirror server.

• MIRROR_ADDRESS ='next_hop_mirror_address'
  Specifies the network address for the mirror server of a mirrored pair whose principal server is 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 a SQL Server instance 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 may not specify a mirror address.

Remarks

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

Clauses that are not specified in the ALTER ROUTE command remain unchanged. Therefore, there is no way to ALTER a route to specify that the route does not time out, that the route matches any service name, or that the route matches any broker instance. To change these characteristics of a route, you must drop the existing route and create a new route with the new information.

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. Services with names that contain valid network addresses will route to the network address in the service name.

The routing table may contain any number of routes that specify the same service, network address, and/or 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 and Networking.

To alter the AUTHORIZATION for a service, use the ALTER AUTHORIZATION statement.

Permissions

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

Examples

A. Changing the service for a route

The following example modifies the ExpenseRoute route to point to the remote service //Adventure-Works.com/Expenses.

ALTER ROUTE ExpenseRoute
   WITH 
     SERVICE_NAME = '//Adventure-Works.com/Expenses'

B. Changing the target database for a route

The following example changes the target database for the ExpenseRoute route to the database identified by the unique identifier D8D4D268-00A3-4C62-8F91-634B89B1E317.

ALTER ROUTE ExpenseRoute
   WITH 
     BROKER_INSTANCE = 'D8D4D268-00A3-4C62-8F91-634B89B1E317'

C. Changing the address for a route

The following example changes the network address for the ExpenseRoute route to TCP port 1234 on the host with the IP address 10.2.19.72.

ALTER ROUTE ExpenseRoute 
   WITH 
     ADDRESS = 'TCP://10.2.19.72:1234'

D. Changing the database and address for a route

The following example changes the network address for the ExpenseRoute route to TCP port 1234 on the host with the DNS name www.Adventure-Works.com and changes the target database to the database identified by the unique identifier D8D4D268-00A3-4C62-8F91-634B89B1E317.

ALTER ROUTE ExpenseRoute
   WITH 
     BROKER_INSTANCE = 'D8D4D268-00A3-4C62-8F91-634B89B1E317',
     ADDRESS = 'TCP://www.Adventure-Works.com:1234'

See Also

Reference

CREATE ROUTE (Transact-SQL)
DROP ROUTE (Transact-SQL)
EVENTDATA (Transact-SQL)

Other Resources

Service Broker Routing

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
15 September 2007	Changed content: Added example queries showing how to find endpoint port addresses for the ADDRESS and MIRROR_ADDRESS arguments.