CREATE ROUTE (Transact-SQL)

  • [アーティクル]

適用対象: SQL ServerAzure SQL Managed Instance

新しいルートを現在のデータベース用のルーティング テーブルに追加します。 送信メッセージについては、Service Broker がローカル データベース内のルーティング テーブルを調べてルーティングを決定します。 転送されるメッセージなど、別のインスタンスで発生するメッセージ交換でのメッセージについては、Service Broker が msdb 内のルートを調べます。

Transact-SQL 構文表記規則

構文

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

引数

route_name
作成するルートの名前です。 新しいルートが現在のデータベースで作成され、AUTHORIZATION 句で指定されるプリンシパルによって所有されます。 サーバー名、データベース名、スキーマ名は指定できません。 route_name は有効な sysname とする必要があります。

AUTHORIZATION owner_name
ルートの所有者を、指定したデータベース ユーザーまたはロールに設定します。 現在のユーザーが db_owner 固定データベース ロールまたは sysadmin 固定サーバー ロールのメンバーである場合、owner_name には、任意の有効なユーザー名またはロール名を指定できます。 それ以外の場合、owner_name には、現在のユーザーの名前、現在のユーザーが IMPERSONATE 権限を持つユーザーの名前、または現在のユーザーが属するロールの名前を指定する必要があります。 この句を省略すると、ルートは現在のユーザーに属します。

WITH
作成されるルートを定義するための句を、WITH の後に指定します。

SERVICE_NAME = 'service_name'
このルートが示すリモート サービスの名前を指定します。 service_name はリモート サービスで使用される名前と正確に一致する必要があります。 Service Broker は service_name をバイト単位で照合します。 つまり、この比較では大文字と小文字が区別され、現在の照合順序は考慮されません。 SERVICE_NAME が省略されると、このルートはすべてのサービス名と照合されることになりますが、SERVICE_NAME が指定されたルートより照合の優先度が下がります。 'SQL/ServiceBroker/BrokerConfiguration' というサービス名を持つルートは、Broker Configuration Notice サービスへのルートです。 このサービスへのルートは、ブローカー インスタンスを指定できない場合があります。

BROKER_INSTANCE = 'broker_instance_identifier'
発信先サービスをホストするデータベースを指定します。 broker_instance_identifier パラメーターは、リモート データベースのブローカー インスタンス識別子である必要があります。この識別子は選択したデータベースで、次のクエリを実行して取得できます。

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

BROKER_INSTANCE 句を省略すると、このルートはすべてのブローカー インスタンスと照合されます。 ブローカー インスタンスを指定しないメッセージ交換では、すべてのブローカー インスタンスと照合されるルートは、明示的なブローカー インスタンスを持つルートよりも照合の優先度が高くなります。 ブローカー インスタンスを指定するメッセージ交換では、ブローカー インスタンスを持つルートの方が、すべてのブローカー インスタンスと照合されるルートより優先度が高くなります。

LIFETIME =route_lifetime
SQL Server がルーティング テーブルにルートを保持する時間を秒単位で指定します。 有効期間が終了するとルートは期限切れとなり、SQL Server では、新しいメッセージ交換用のルートを選択するときに、そのルートは考慮されなくなります。 この句を省略した場合、route_lifetime は NULL になり、ルートの有効期限が切れることはありません。

ADDRESS ='next_hop_address'
SQL Managed Instance の場合、ADDRESS はローカルである必要があります。

このルート用のネットワーク アドレスを指定します。 next_hop_address の次の形式で TCP/IP アドレスを指定します。

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

指定した port_number は、指定したコンピューターにおける Service Broker インスタンスの SQL Server エンドポイント用のポート番号と一致する必要があります。 これは選択したデータベースで次のクエリを実行することにより取得できます。

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

ミラー化されたデータベースでサービスがホストされる場合、ミラー化されたデータベースをホストする別のインスタンスに対して、MIRROR_ADDRESS を指定する必要もあります。 それ以外の場合は、このルートはミラーに対してフェールオーバーされません。

ルートの next_hop_address が 'LOCAL' になっている場合、メッセージは現在の SQL Server インスタンス内のサービスに配信されます。

ルートの next_hop_address が 'TRANSPORT' になっている場合、ネットワーク アドレスは、サービス名の中にあるネットワーク アドレスに基づいて決まります。 'TRANSPORT' を指定するルートは、サービス名やブローカー インスタンスを指定できない場合があります。

MIRROR_ADDRESS ='next_hop_mirror_address'
next_hop_address でホストされているミラー化されたデータベースを 1 つ含んでいる、ミラー化されたデータベースのネットワーク アドレスを指定します。 next_hop_mirror_address は、次の形式で TCP/IP アドレスを指定します。

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

指定した port_number は、指定したコンピューターにおける Service Broker インスタンスの SQL Server エンドポイント用のポート番号と一致する必要があります。 これは選択したデータベースで次のクエリを実行することにより取得できます。

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

MIRROR_ADDRESS が指定されている場合、ルートには SERVICE_NAME 句と BROKER_INSTANCE 句を指定する必要があります。 next_hop_address に 'LOCAL' または 'TRANSPORT' を指定するルートでは、ミラー アドレスが指定されない場合があります。

解説

ルートを格納するルーティング テーブルは、sys.routes カタログ ビューを介して読み取ることができるメタデータ テーブルです。 このカタログ ビューを更新できるのは、CREATE ROUTE、ALTER ROUTE、および DROP ROUTE ステートメントだけです。

既定では、各ユーザー データベースにあるルーティング テーブルには 1 つのルートが含まれています。 このルートには AutoCreatedLocal という名前が付いています。 ルートは next_hop_address に 'LOCAL' を指定し、任意のサービス名およびブローカー インスタンス識別子と一致します。

ルートが next_hop_address に 'TRANSPORT' を指定した場合、ネットワーク アドレスはサービスの名前に基づいて決定されます。 SQL Server では、next_hop_address のネットワーク アドレスが有効な形式であれば、このネットワーク アドレスで始まるサービス名が適切に処理されます。

ルーティング テーブルには、同じサービス、ネットワーク アドレス、ブローカー インスタンス識別子を指定する、ルートをいくつでも含めることができます。 このような場合、Service Broker でルートを選択するときには、メッセージ交換で指定された情報とルーティング テーブル内の情報を照合して、最も正確に一致する情報を取得するためのプロシージャが使用されます。

Service Broker では、ルーティング テーブルから期限の切れたルートは削除されません。 期限の切れたルートは、ALTER ROUTE ステートメントを使用してアクティブにすることができます。

ルートは一時オブジェクトとして指定できません。 # で始まるルート名は許可されますが、パーマネント オブジェクトになります。

アクセス許可

ルートを作成する権限は、既定では db_ddladmin 固定データベース ロールまたは db_owner 固定データベース ロールのメンバー、および sysadmin 固定サーバー ロールのメンバーに与えられています。

例

A. DNS 名を使用して TCP/IP ルートを作成する

次の例では、サービス //Adventure-Works.com/Expenses へのルートを作成します。 このルートでは、このサービスへのメッセージが、TCP 経由で DNS 名 1234 に対応するホスト上のポート www.Adventure-Works.com に伝達されるように指定します。 ターゲット サーバーにメッセージが到着すると、一意識別子 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. NetBIOS 名を使用して TCP/IP ルートを作成する

次の例では、サービス //Adventure-Works.com/Expenses へのルートを作成します。 このルートでは、このサービスへのメッセージが、TCP 経由で NetBIOS 名 1234 に対応するホスト上のポート SERVER02 へ伝達されるように指定します。 対象の SQL Server にメッセージが到着すると、一意識別子 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. IP アドレスを使用して TCP/IP ルートを作成する

次の例では、サービス //Adventure-Works.com/Expenses へのルートを作成します。 このルートでは、このサービスへのメッセージが、TCP 経由で IP アドレス 1234 にあるホスト上のポート 192.168.10.2 へ伝達されるように指定します。 対象の SQL Server にメッセージが到着すると、一意識別子 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. 転送ブローカーへのルートを作成する

次の例では、サーバー dispatch.Adventure-Works.com の転送ブローカーへのルートを作成します。 サービス名とブローカー インスタンス識別子が両方とも指定されていないため、SQL Server では、他のルートが定義されていないサービスに対してこのルートが使用されます。

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

E. ローカル サービスへのルートを作成する

次の例では、ルートと同じインスタンスにあるサービス //Adventure-Works.com/LogRequests へのルートを作成します。

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

F. 有効期間が指定されたルートを作成する

次の例では、サービス //Adventure-Works.com/Expenses へのルートを作成します。 ルートの有効期間は、259200 秒、つまり 72 時間です。

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

G. ミラー化されたデータベースへのルートを作成する

次の例では、サービス //Adventure-Works.com/Expenses へのルートを作成します。 サービスは、ミラー化されたデータベースでホストされています。 ミラー化されたデータベースのうちの 1 つが置かれているアドレスは services.Adventure-Works.com:1234、もう 1 つが置かれているアドレスは 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. ルーティング用のサービス名を使用するルートを作成する

次の例では、メッセージの送信先となるネットワーク アドレスを決定するために、サービス名を使用するルートが作成されます。 ネットワーク アドレスとして 'TRANSPORT' を指定するルートは、他のルートより照合の優先度が低いことに注意してください。

CREATE ROUTE TransportRoute  
    WITH ADDRESS = 'TRANSPORT' ;

参照

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