[SQL Server 2012]Service Broker を使用して SQL Server インスタンス間でメッセージ交換をしてみた

handcraft 11/4/2012 15008 N/A SQL Server

Service Broker を使用すると SQL Server のインスタンス間で順番保障されたメッセージの交換を行えます。単一DB内でのメッセージ交換は[SQL Server 2012] Service Brokerで単一データベース内でメッセージ交換してみたでやってみました。今回はSQL Server インスタンスが異なる DB 間でメッセージを交換する処理を下記チュートリアルに従ってやってみました。その覚書を記載します。ほぼチュートリアルと同じですが、ENDPOINT間の認証がうまくいかなかったので、CONNECT権の付与を追加しています。

インスタンス間のメッセージ交換の完了
http://msdn.microsoft.com/ja-jp/library/bb839483(v=sql.105).aspx

冒頭にも記載しましたが、基本的にチュートリアルのまんまです。

動作確認環境

ADサーバー1 台, SQL Server 2012 がインストールされたメンバサーバー 2台
OS はすべて SQL Server 2012
SQL Server 2012 は既定のインスタンスとしてインストールしています

前提条件

配信側、配信先 SQL Server のマシン上で次の設定が行われていることとします。

ファイアウォール(FW) の受信設定、具体的には検証時にはTCP 1433,1434,4022 ポートを受信許可しました。
(名前付きSQL Server インスタンスの場合は sqlserverのexe に受信許可設定をしてください。)
SQL Server の通信プロトコルで TCP/IP が有効になっている

本サンプルでは、発信先のDBは TargetDB, 発信側のDB を InitiatorDB として作成しています。そのほかマスターキーに使用するパスワードなど適宜自分の環境に合うように置き換えて読んでください。

1. 発信先でService のエンドポイントの作成からサービスの作成まで

発信先のSQL Server のインスタンスで次のことをします。

master DB で Service Broker 用のエンドポイントを作成
ポートは 4022, WIndows 認証を使用するようにしています。(この場合 SQL Server インスタンスのユーザーになります。)
発信先DB(TargetDB)を作成
発信先DBにマスターキーの作成
発信先サービス用のユーザー作成(ログインにマップしない)。そのユーザー(TargetUser)に証明書を関連付けます。
公開鍵証明書は発信側で作成するTargetUserに関連付けるためにファイルに退避しています。
メッセージ交換用にメッセージ型、コントラクト、受信キュー、サービスを定義します。サービスの所有者を TargetUser にしています。

サービスの所有者を TargetUser にし、 TargetUserに証明書を関連付けるのはなんでだろうとおもったのですが、CREATE REMOTE SERVICE BINDINGの説明で納得しました。CREATE REMOTE SERVICE BINDING はリモートサービスに接続するユーザーを必須でWITH USER 句で指定するようですが、その説明で次のように記載されています。

USER = user_name

TO SERVICE 句のリモートサービスに関連付けられた証明を所有しているデータベースプリンシパルを指定します。この証明は、リモートサービスと交換されるメッセージの暗号化と認証に使用されます。

-- 発信先SQL Serverでの作業
-- 前提：FW 1433,1434,4022 受信許可設定済み
--       SQL Server で TCP接続プロトコルを有効化済み

use master;
GO

-- エンドポイントの作成
IF EXISTS (SELECT * FROM master.sys.endpoints
           WHERE name = N'TestTargetEndpoint')
     DROP ENDPOINT TestTargetEndpoint;
GO

-- AUTHENTICATIONがWINDOWSなので SQL Server サービスインスタンスのユーザー
CREATE ENDPOINT TestTargetEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO

-- DB の作成, Service Broker は既定で有効化されている
IF EXISTS (SELECT * FROM sys.databases
           WHERE name = N'TargetDB')
     DROP DATABASE TargetDB;
GO
-- DB の作成
CREATE DATABASE TargetDB;
GO

USE TargetDB;
GO

-- TargetDBにマスターキーの作成
IF NOT EXISTS(SELECT * FROM sys.symmetric_keys
                      WHERE name = N'##MS_DatabaseMasterKey##')
  BEGIN
    CREATE MASTER KEY
       ENCRYPTION BY PASSWORD = N'passwordXXX!';
  END;
GO

-- 発信先サービス用のユーザー作成
CREATE USER TargetUser WITHOUT LOGIN;
GO


-- サービス用のユーザーに証明書を関連付ける
CREATE CERTIFICATE TargetUserCertificate 
     AUTHORIZATION TargetUser
     WITH SUBJECT = 'Target Certificate',
          EXPIRY_DATE = N'12/31/2099';


-- 発信側で参照するために公開鍵証明書を退避
BACKUP CERTIFICATE TargetUserCertificate
  TO FILE = N'C:\work\ServiceBrokerSample01\TargetUserCertificate.cer';
GO

-- メッセージ型の作成
CREATE MESSAGE TYPE [//Test/Sample1/RequestMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//Test/Sample1/ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
GO

-- コントラクトの作成
CREATE CONTRACT [//Test/Sample1/SimpleContract]
      ([//Test/Sample1/RequestMessage]
         SENT BY INITIATOR,
       [//Test/Sample1/ReplyMessage]
         SENT BY TARGET
      );
GO

-- 発信先のキュー作成
CREATE QUEUE TestTargetQueue;
GO

-- 発信先のサービス作成(所有者を TargetUser)にする
-- 後ほどの処理で発信側から発信先サービスへのRemote Binding
-- を作成するが、このときに使用するユーザーとして指定するのが
-- (退避した証明書を関連付けた)発信側で作成されたTargetUserとなる。
CREATE SERVICE [//TestTargetDB/Sample1/TargetService]
       AUTHORIZATION TargetUser
       ON QUEUE TestTargetQueue
       ([//Test/Sample1/SimpleContract]);
GO

2. 発信側でエンドポイントの作成からサービスの作成まで

発信側のSQL Server のインスタンスで次のことをします。

master DB で Service Broker 用のエンドポイントを作成
ポートは 4022, WIndows 認証を使用するようにしています。(この場合 SQL Server インスタンスのユーザーになります。)
発信側DB(InitiatorDB)を作成
発信側DBにマスターキーの作成
発信側サービス用のユーザー作成(ログインにマップしない)。そのユーザー(InitiatorUser)に証明書を関連付けます。
公開鍵証明書は発信先で作成するInitiatorUserに関連付けるためにファイルに退避しています。
メッセージ交換用にメッセージ型、コントラクト、受信キュー、サービスを定義します。サービスの所有者を InitiatorUser にしています。

メッセージ型、コントラクトは発信先,発信側で同じになるように定義してください。

-- 発信側SQL Serverでの作業
-- 前提：FW 1433,1434,4022 受信許可設定済み
--       SQL Server で TCP接続プロトコルを有効化済み

use master;
GO

-- エンドポイントの作成
IF EXISTS (SELECT * FROM master.sys.endpoints
           WHERE name = N'TestInitiatorEndpoint')
     DROP ENDPOINT TestInitiatorEndpoint;
GO

-- AUTHENTICATIONがWINDOWSなので SQL Server サービスインスタンスのユーザー
-- ポートは4022 を使用する
CREATE ENDPOINT TestInitiatorEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO

-- DB の作成, Service Broker は既定で有効化されている
IF EXISTS (SELECT * FROM sys.databases
           WHERE name = N'InitiatorDB')
     DROP DATABASE InitiatorDB;
GO

CREATE DATABASE InitiatorDB;
GO

USE InitiatorDB;
GO

-- InitiatorDBにマスターキーの作成
IF NOT EXISTS(SELECT * FROM sys.symmetric_keys
                      WHERE name = N'##MS_DatabaseMasterKey##')
  BEGIN
    CREATE MASTER KEY
       ENCRYPTION BY PASSWORD = N'passwordxxxx!';
  END;
GO

-- 発信側サービス用のユーザー作成
CREATE USER InitiatorUser WITHOUT LOGIN;
GO


-- サービス用のユーザーに証明書を関連付ける
CREATE CERTIFICATE InitiatorUserCertificate 
     AUTHORIZATION InitiatorUser
     WITH SUBJECT = 'Initiator Certificate',
          EXPIRY_DATE = N'12/31/2099';


-- 発信先で参照するために公開鍵証明書を退避
BACKUP CERTIFICATE InitiatorUserCertificate
  TO FILE = N'C:\work\ServiceBrokerSample01\InitiatorUserCertificate.cer';
GO

-- メッセージ型の作成(発信先と同じであること)
CREATE MESSAGE TYPE [//Test/Sample1/RequestMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//Test/Sample1/ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
GO

-- コントラクトの作成(発信先と同じであること)
CREATE CONTRACT [//Test/Sample1/SimpleContract]
      ([//Test/Sample1/RequestMessage]
         SENT BY INITIATOR,
       [//Test/Sample1/ReplyMessage]
         SENT BY TARGET
      );
GO

-- 発信側のキュー作成
CREATE QUEUE TestInitiatorQueue;
GO

-- 発信側のサービス作成(所有者をInitiatorUser)にする
-- 後ほどの処理で発信先から発信側サービスへのRemote Binding
-- を作成するが、このときに使用するユーザーとして指定するのが
-- (退避した証明書を関連付けた)発信先で作成されたInitiatorUserとなる。
-- コントラクトを指定しないことで発信先としてサービスが使用される
-- ことを明示的に指定している。
CREATE SERVICE [//TestInitiatorDB/Sample1/InitiatorService]
       AUTHORIZATION InitiatorUser
       ON QUEUE TestInitiatorQueue;
GO

ここまででサービスの定義まで終わりました。

3. 発信側で発信先サービスへの参照設定

インスタンスが異なる DB でメッセージを交換する場合は、発信先、発信側でルーティングの設定が必要になります。発信先、発信側サービスで認証が各サービスのユーザーで認証されるようにリモートバインディングの設定も行います。

発信側DBで次のことを行います。

発信先サービス接続用のユーザー(TargetUser)を作成し、退避した公開鍵証明書と関連付ける
発信先、発信側サービスへのルーティング設定を登録する
発信先サービスへのリモートバインディングを定義しユーザーを発信先サービス接続用のユーザー TargetUser にします。

ルーティングの設定は、発信先サービスへは InitiatorDB, ローカルの配信側サービスのルーティングは msdb に作成するので注意してください。発信先のサービスのADDRESSのポートはService Broker のエンドポイント作成時に指定したポートになります。

-- 発信側での作業
USE InitiatorDB;
GO

-- 発信先Service接続用のユーザー作成
CREATE USER TargetUser WITHOUT LOGIN;

-- 発信先で退避した公開先証明書を使用して証明書を作成し
-- 発信側で作成した TargetUser に関連付ける
CREATE CERTIFICATE TargetCertificate 
   AUTHORIZATION TargetUser
   FROM FILE = N'C:\work\ServiceBrokerSample01\TargetUserCertificate.cer'
GO

-- ルートの作成
-- 発信先サービスへのRouteを作成。発信先DBのエンドポイントアドレスをADDRSSに指定する
CREATE ROUTE TestTargetRoute
  WITH SERVICE_NAME = N'//TestTargetDB/Sample1/TargetService'
     , ADDRESS = 'TCP://SBW2012SQL02:4022';


use msdb;
GO

-- 発信側サービスへのRouteを作成。ADDRESSはLOCALを指定する
CREATE ROUTE TestInitiatorRoute
  WITH SERVICE_NAME = N'//TestInitiatorDB/Sample1/InitiatorService'
     , ADDRESS = 'LOCAL';

use InitiatorDB;
GO

-- リモートサービスバインディングの作成
-- WITH USER で指定するのが 発信先サービスのユーザーとして
-- 認証されるように作成した公開鍵証明書が関連付けられた
-- InitiatorDBのTargetUser
CREATE REMOTE SERVICE BINDING TestTargetBinding
      TO SERVICE
         N'//TestTargetDB/Sample1/TargetService'
      WITH USER = TargetUser;

4. 発信先で発信側サービスへの参照設定

発信側で実施したのと同じく、発信先からも発信側サービスへの参照、ルーティングの設定、リモートバインディングの作成を行います。

発信先DBで次のことを行います。

発信側サービスへの接続用のユーザー(InitiatorUser)を作成し、退避した公開鍵証明書と関連付ける
発信側、発信先サービスへのルーティング設定を登録する
発信側サービスへのリモートバインディングを定義しユーザーを発信側サービス接続用のユーザー InitiatorUserにします。

ルーティングの設定は、発信側サービスへの登録は TargetDB, ローカルの発信先サービスのルーティングは msdb に作成するので注意してください。発信側のサービスのADDRESSのポートは発信側でService Broker のエンドポイント作成時に指定したポートになります。

-- 発信先での作業

USE TargetDB;
GO

-- 発信側Service接続用のユーザー作成
CREATE USER InitiatorUser WITHOUT LOGIN;

-- 発信側で退避した公開鍵証明書を使用して証明書を作成する
-- 発信先で作成した InitiatorUser に関連付ける
CREATE CERTIFICATE InitiatorCertificate 
   AUTHORIZATION InitiatorUser
   FROM FILE = N'C:\work\ServiceBrokerSample01\InitiatorUserCertificate.cer'
GO

-- リモートサービスへのルートを作成

-- 発信側サービスへのRouteを作成。ADDRSSに発信側SQL Serverのエンドポイントを指定する
CREATE ROUTE TestInitiatorRoute
  WITH SERVICE_NAME = N'//TestInitiatorDB/Sample1/InitiatorService'
     , ADDRESS = 'TCP://SBW2012SQL01:4022';

use msdb;
GO

-- 発信先サービスへのRouteを作成。ADDRESSはLOCALを指定する
CREATE ROUTE TestTargetRoute
  WITH SERVICE_NAME = N'//TestTargetDB/Sample1/TargetService'
     , ADDRESS = 'LOCAL';
GO

use TargetDB;
GO

-- 発信先サービスでInitiator に SEND 権限を付与する
-- ないと以下のエラー
-- Service Broker received an error message on this conversation. Service Broker will not transmit the message; 
-- it will be held until the application ends the conversation.
-- D  29975 //TestTargetDB/Sample1/TargetService TargetDB
--        ユーザー InitiatorUser は、サービス //TestTargetDB/Sample1/TargetService で SEND 権限がありません
GRANT SEND
      ON SERVICE::[//TestTargetDB/Sample1/TargetService]
      TO InitiatorUser;


-- リモートサービスバインディングの作成
-- WITH USER で指定するのが 発信側サービスのユーザーとして
-- 認証されるように作成した公開鍵証明書が関連付けられた
-- TargetDBのInitiatorUser
CREATE REMOTE SERVICE BINDING TestInitiatorBinding
      TO SERVICE
         N'//TestInitiatorDB/Sample1/InitiatorService'
      WITH USER = InitiatorUser;

以上でチュートリアルまでの準備完了。

このまま実行すると次のようなエラーが発生しました。

An error occurred while receiving data: '10054(既存の接続はリモートホストに強制的に切断されました。)'.
とか
Connection handshake failed. The login 'DomainName\SQLServiceインスタンスの実行ユーザー' does not have CONNECT permission on the endpoint. State 84.

なので、発信先、発信側でSQL Server インスタンスの実行ユーザーのログインを作成し、各エンドポイントへの接続許可を設定しました。

5. CONNECT 権限の付与

今回は、発信先、発信側の各SQL Server で次のコマンドを実行します。

-- 発信先での操作

CREATE LOGIN [DomainName\SQLServiceのユーザー] FROM WINDOWS;
 GO
GRANT CONNECT ON ENDPOINT::[TestTargetEndpoint] TO [DomainName\SQLServiceのユーザー];
 GO

-- 発信先での操作
CREATE LOGIN [DomainName\SQLServiceのユーザー] FROM WINDOWS;
 GO
GRANT CONNECT ON ENDPOINT::[TestInitiatorEndpoint] TO [DomainName\SQLServiceのユーザー];
 GO

ここまででメッセージ交換が行えるようになりました。

6. メッセージ交換のテスト

あとはチュートリアルに従って発信側でメッセージ交換の開始、発信先でメッセージの受信と応答メッセージの送信とメッセージ交換終了処理、発信側で応答メッセージの受信とメッセージ交換終了処理を実施します。

6.1 発信側からメッセージ交換開始

USE InitiatorDB;
GO

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle
     FROM SERVICE [//TestInitiatorDB/Sample1/InitiatorService]
     TO SERVICE N'//TestTargetDB/Sample1/TargetService'
     ON CONTRACT [//Test/Sample1/SimpleContract]
     WITH
         ENCRYPTION = ON;

SELECT @RequestMsg = N'<RequestMsg>Message for Target service.</RequestMsg>';

SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE [//Test/Sample1/RequestMessage]
     (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;
GO

6.2 発信先でのメッセージ受信と応答メッセージ送信とメッセージ交換終了処理

-- 発信先処理
-- メッセージを受信し応答メッセージ送信

USE TargetDB;
GO

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM TestTargetQueue
), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsgName = N'//Test/Sample1/RequestMessage'
BEGIN
     DECLARE @ReplyMsg NVARCHAR(100);
     SELECT @ReplyMsg =
        N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';

     SEND ON CONVERSATION @RecvReqDlgHandle
          MESSAGE TYPE [//Test/Sample1/ReplyMessage]
          (@ReplyMsg);

     END CONVERSATION @RecvReqDlgHandle;
END

SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;
GO

6.3 発信側での応答メッセージ受信とメッセージ交換終了処理

USE InitiatorDB;
GO

DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReplyDlgHandle = conversation_handle,
    @RecvReplyMsg = message_body
  FROM TestInitiatorQueue
), TIMEOUT 1000;

If @RecvReplyDlgHandle is not null
	END CONVERSATION @RecvReplyDlgHandle;

-- Display recieved request.
SELECT @RecvReplyMsg AS ReceivedReplyMsg;

COMMIT TRANSACTION;
GO

メッセージはXML形式で送信しています。xmlに対する処理は[SQL Server 2012] Service Brokerで単一データベース内でメッセージ交換してみたの発信先のメッセージ応答処理や次のMSDNドキュメントを参考にして下さい。

xml データ型のメソッド
http://msdn.microsoft.com/ja-jp/library/ms190798.aspx

7 まとめ

説明は以上です。私の環境ではメッセージ交換が行えるようになりました。エンドポイント間の認証は Windows 認証を使用していますが証明書を使うパターンも時間があればやってみようと思います。

もしうまくいかない場合は、マスターキーや証明書の作成先やルートの作成先などが間違っていないことなどを確認してください。またそのほか転送キュー(sys.transmission_queue) や各受信キューの確認、SQL Service Broker や受信キューが無効になっていないことを確認してください。

そのほか、 SQL Service Profier で Broker 関連のイベントと Audito のBroker 関連のイベントをチェックしてメッセージを確認してください。ssbdiagnose ツールを使用してメッセージ交換中にエラーが発生しないことを確認してください。次のようなコマンドを実行してメッセージ交換処理を行うだけでも簡単な診断を行えます。

ssbdiagnose　runtime connect to -S SQLServerインスタンスA connect to -S SQLServerインスタンスB

// Net planet-es V2