SQL Server can successfully process service names that begin with a network address in a format that is valid for a next_hop_address. When a route specifies 'TRANSPORT' for the next_hop_address, the network address is determined based on the name of the service. The route specifies 'LOCAL' for the next_hop_address and matches any service name and broker instance identifier. This catalog view can only be updated through the CREATE ROUTE, ALTER ROUTE, and DROP ROUTE statements.īy default, the routing table in each user database contains one route. The routing table that stores the routes is a metadata table that can be read through the sys.routes catalog view. A route that specifies 'LOCAL' or 'TRANSPORT' for the next_hop_address might not specify a mirror address. When the MIRROR_ADDRESS is specified, the route must specify the SERVICE_NAME clause and the BROKER_INSTANCE clause. WHERE ssbe.name = N'MyServiceBrokerEndpoint' INNER JOIN sys.service_broker_endpoints AS ssbe This can be obtained by running the following query in the selected database: SELECT tcpe.port The specified port_number must match the port number for the Service Broker endpoint of an instance of SQL Server at the specified computer. The next_hop_address specifies a TCP/IP address in the following format: Specifies the network address for this route. If this clause is omitted, the route_lifetime is NULL and the route never expires.įor SQL Managed Instance, ADDRESS must be local. 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. Specifies the time, in seconds, that SQL Server retains the route in the routing table. For conversations that specify a broker instance, a route with a broker instance has higher priority than a route that 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. When the BROKER_INSTANCE clause is omitted, this route matches any broker instance. 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 Specifies the database that hosts the target service. A route to this service might not specify a broker instance.īROKER_INSTANCE = ' broker_instance_identifier ' A route with a service name of 'SQL/ServiceBroker/BrokerConfiguration' is a route to a Broker Configuration Notice service. 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. In other words, the comparison is case sensitive and does not consider the current collation. Service Broker uses a byte-by-byte comparison to match the service_name. The service_name must exactly match the name the remote service uses. Specifies the name of the remote service that this route points to. Introduces the clauses that define the route being created. When this clause is omitted, the route belongs to the current user. 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. 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. Sets the owner of the route to the specified database user or role. Server, database, and schema names cannot be specified. A new route is created in the current database and owned by the principal specified in the AUTHORIZATION clause. Transact-SQL Syntax Conventions Syntax CREATE ROUTE route_name For messages on conversations that originate in another instance, including messages to be forwarded, Service Broker checks the routes in msdb. For outgoing messages, Service Broker determines routing by checking the routing table in the local database. Adds a new route to the routing table for the current database.