CREATE ROUTE (Transact-SQL)

Aplica-se a: SQL Server Instância Gerenciada de SQL do Azure

Adiciona uma nova rota à tabela de roteamento para o banco de dados atual. Por mensagens de saída, o Agente de Serviço determina o roteamento verificando a tabela de roteamento no banco de dados local. Para mensagens sobre conversas que se originam em outra instância, incluindo mensagens a serem encaminhadas, o Agente de Serviço verifica as rotas em msdb.

Convenções de sintaxe de Transact-SQL

Sintaxe

CREATE ROUTE route_name  
[ AUTHORIZATION owner_name ]  
WITH    
   [ SERVICE_NAME = 'service_name', ]  
   [ BROKER_INSTANCE = 'broker_instance_identifier' , ]  
   [ LIFETIME = route_lifetime , ]  
   ADDRESS =  'next_hop_address'  
   [ , MIRROR_ADDRESS = 'next_hop_mirror_address' ]  
[ ; ]  

Argumentos

route_name
É o nome da rota a ser criada. Uma rota nova é criada no banco de dados atual e é de propriedade do principal especificado na cláusula AUTHORIZATION. Os nomes de servidor, banco de dados e esquema não podem ser especificados. O route_name deve ser um sysname válido.

AUTHORIZATION owner_name
Define o proprietário da rota para o usuário ou função de banco de dados especificados. O owner_name pode ser o nome de qualquer usuário ou função válida quando o usuário atual é um membro da função de banco de dados fixa db_owner ou da função de servidor fixa sysadmin. Caso contrário, owner_name deve ser o nome do usuário atual, o nome de um usuário para o qual o usuário atual tenha a permissão IMPERSONATE ou o nome de uma função à qual o usuário atual pertença. Quando esta cláusula é omitida, a rota pertence ao usuário atual.

WITH
Introduz as cláusulas que definem a rota que é criada.

SERVICE_NAME = 'service_name'
Especifica o nome do serviço remoto ao qual essa rota aponta. O service_name precisa corresponder exatamente ao nome que o serviço remoto usa. O Agente de Serviço usa uma comparação byte a byte para corresponder ao service_name. Em outras palavras, a comparação diferencia maiúsculas de minúsculas e não considera a ordenação atual. Se o SERVICE_NAME for omitido, essa rota corresponderá a qualquer nome de serviço, mas terá uma prioridade menor para correspondência que uma rota que especifique um SERVICE_NAME. Uma rota com o nome de serviço 'SQL/ServiceBroker/BrokerConfiguration' é uma rota para um serviço Broker Configuration Notice. Uma rota para esse serviço pode não especificar uma instância do agente.

BROKER_INSTANCE = 'broker_instance_identifier'
Especifica o banco de dados que hospeda o serviço de destino. O parâmetro broker_instance_identifier deve ser o identificador de instância do agente para o banco de dados remoto, que pode ser obtido com a execução ad seguinte consulta no banco de dados selecionado:

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

Quando a cláusula BROKER_INSTANCE é omitida, essa rota corresponde a qualquer instância do agente. Uma rota que corresponde a qualquer instância do agente tem prioridade maior para correspondência do que rotas com uma instância do agente explícita quando a conversa não especifica a instância. Para conversas que especificam uma instância do agente, uma rota com uma instância do agente tem prioridade maior do que uma rota que corresponde a qualquer instância do agente.

LIFETIME =route_lifetime
Especifica a hora, em segundos, que o SQL Server retém a rota na tabela de roteamento. No fim do tempo de vida, a rota expira e o SQL Server não a considera mais ao escolher uma rota para uma nova conversa. Se essa cláusula for omitida, a route_lifetime será NULL e a rota nunca expirará.

ADDRESS ='next_hop_address'
Para a Instância Gerenciada de SQL, ADDRESS precisa ser local.

Especifica o endereço de rede para essa rota. O next_hop_address especifica um endereço TCP/IP no seguinte formato:

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

O port_number especificado precisa corresponder ao número da porta do ponto de extremidade do Agente de Serviço de uma instância do SQL Server no computador especificado. Isso pode ser obtido executando a seguinte consulta no banco de dados selecionado:

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';  

Quando o serviço é hospedado em um banco de dados espelho, é necessário também especificar o MIRROR_ADDRESS para a outra instância que hospeda um banco de dados espelho. Caso contrário, essa rota não realizará failover no espelho.

Quando uma rota especifica 'LOCAL' para o next_hop_address, a mensagem é entregue a um serviço na instância atual do SQL Server.

Quando uma rota especifica 'TRANSPORT' para o next_hop_address, o endereço de rede é determinado com base no endereço de rede no nome do serviço. Uma rota que especifica 'TRANSPORT' pode não especificar um nome de serviço ou uma instância de agente.

MIRROR_ADDRESS ='next_hop_mirror_address'
Especifica o endereço de rede para um banco de dados espelhado com um banco de dados espelhado hospedado no next_hop_address. O next_hop_mirror_address especifica um endereço TCP/IP no seguinte formato:

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

O port_number especificado precisa corresponder ao número da porta do ponto de extremidade do Agente de Serviço de uma instância do SQL Server no computador especificado. Isso pode ser obtido executando a seguinte consulta no banco de dados selecionado:

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';  

Quando MIRROR_ADDRESS é especificado, a rota deve especificar as cláusulas SERVICE_NAME e BROKER_INSTANCE. Uma rota que especifica 'LOCAL' ou 'TRANSPORT' para o next_hop_address pode não especificar um endereço espelho.

Comentários

A tabela de roteamento que armazena as rotas é uma tabela de metadados que pode ser lida por meio da exibição do catálogo sys.routes. Essa exibição do catálogo pode ser atualizada somente pelas instruções CREATE ROUTE, ALTER ROUTE e DROP ROUTE.

Por padrão, a tabela de roteamento em cada banco de dados de usuário contém uma rota. Essa rota é chamada AutoCreatedLocal. A rota especifica 'LOCAL' para o next_hop_address e faz a correspondência de qualquer nome de serviço e o identificador de instância do agente.

Quando uma rota especifica 'TRANSPORT' para o next_hop_address, o endereço de rede é determinado com base no nome do serviço. O SQL Server pode processar com êxito nomes de serviço que começam com um endereço de rede em um formato válido para um next_hop_address.

A tabela de roteamento pode conter qualquer quantidade de rotas que especifiquem o mesmo serviço, endereço de rede e identificador de instância do agente. Nesse caso, o Agente de Serviço escolhe uma rota usando um procedimento criado para encontrar a correspondência mais exata entre as informações especificadas na conversa e as informações da tabela de roteamento.

O Agente de Serviço não remove rotas expiradas da tabela de roteamento. Uma rota expirada pode se tornar ativa usando a instrução ALTER ROUTE.

Uma rota não pode ser um objeto temporário. Nomes de rotas que começam com # são permitidos, mas são objetos permanentes.

Permissões

A permissão para criar uma rota usa como padrão os membros das funções de banco de dados fixas db_ddladmin ou db_owner e da função de servidor fixa sysadmin.

Exemplos

a. Criando uma rota TCP/IP com o uso de um nome DNS

O exemplo a seguir cria uma rota para o serviço //Adventure-Works.com/Expenses. A rota especifica que as mensagens para esse serviço passam pelo TCP na porta 1234 do host identificado com o nome DNS www.Adventure-Works.com. O servidor de destino entrega as mensagens na chegada para a instância de corretor identificada pelo identificador exclusivo D8D4D268-00A3-4C62-8F91-634B89C1E315.

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. Criando uma rota TCP/IP com o uso de um nome NetBIOS

O exemplo a seguir cria uma rota para o serviço //Adventure-Works.com/Expenses. A rota especifica que as mensagens para esse serviço passam pelo TCP na porta 1234 do host identificado com o nome NetBIOS SERVER02. Na chegada, o SQL Server de destino entrega a mensagem à instância de banco de dados identificada pelo identificador exclusivo D8D4D268-00A3-4C62-8F91-634B89C1E315.

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

C. Criando uma rota TCP/IP com o uso de um endereço IP

O exemplo a seguir cria uma rota para o serviço //Adventure-Works.com/Expenses. A rota especifica que as mensagens para esse serviço passam pelo TCP na porta 1234 do host identificado com o endereço IP 192.168.10.2. Na chegada, o SQL Server de destino entrega a mensagem à instância do agente identificada pelo identificador exclusivo D8D4D268-00A3-4C62-8F91-634B89C1E315.

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. Criando uma rota para um agente de encaminhamento

O exemplo a seguir cria uma rota para o corretor de encaminhamento no servidor dispatch.Adventure-Works.com. Como o nome de serviço e o identificador de instância do agente não são especificados, o SQL Server usa essa rota para serviços que não têm nenhuma outra rota definida.

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

E. Criando uma rota para um serviço local

O exemplo a seguir cria uma rota para o serviço //Adventure-Works.com/LogRequests na mesma instância que a rota.

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

F. Criando uma rota com um tempo de vida especificado

O exemplo a seguir cria uma rota para o serviço //Adventure-Works.com/Expenses. O tempo de vida para a rota é de 259200 segundos, que é igual a 72 horas.

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

G. Criando uma rota para um banco de dados espelhado

O exemplo a seguir cria uma rota para o serviço //Adventure-Works.com/Expenses. O serviço é hospedado em um banco de dados espelho. Um dos bancos de dados espelhados está localizado no endereço services.Adventure-Works.com:1234 e o outro, no endereço services-mirror.Adventure-Works.com:1234.

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. Criando uma rota que usa o nome do serviço para roteamento

O exemplo a seguir cria uma rota que usa o nome de serviço para determinar o endereço de rede ao qual a mensagem será enviada. Observe que uma rota que especifica 'TRANSPORT' como o endereço de rede tem prioridade menor de correspondência que outras rotas.

CREATE ROUTE TransportRoute  
    WITH ADDRESS = 'TRANSPORT' ;  

Consulte Também

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