[SQL Server 2012] Service Brokerで単一データベース内でメッセージ交換してみた

handcraft 10/21/2012 26063 5 SQL Server

SQL Server 2012 で Service Broker のことを調べていて、試しに動かしてみようということで、下記Service Broker のチュートリアルで紹介されている単一データベース内でのメッセージ交換のサンプルをほとんどそのまんまですが動かしてみました。その覚書を記載しておきます。私が勉強したかっただけなんで、今回はリンク先のチュートリアルとほぼ同じです。

単一のデータベース内でのメッセージ交換の完了
http://msdn.microsoft.com/ja-jp/library/bb839495(v=sql.105).aspx

ちなみに、Service Broker とは SQL Server インスタンス間で信頼性の高い(メッセージの発信先に順番通りに重複なしに1回のみ送信する)、非同期のメッセージ交換を実現するテクノロジです。SQL Server 2012 のページには2008 R2 以降に大幅な変更がないということで新機能(マルチキャスト送信など)　や簡単な説明のみが記載されています。それ以外は2008R2 のドキュメントを参照してくださいと記載があります。

SQL Server Service Broker
http://msdn.microsoft.com/ja-jp/library/bb522893.aspx
上記URLに"Service Broker の概念、開発作業、および管理作業については、以前に公開されたドキュメントを参照してください。 Service Broker は SQL Server 2012 においてわずかな変更しか加えられていないため、SQL Server 2012 用のドキュメントは作成されていません。"と記載があります。

パフォーマンスに関して検証したわけではないですが、非同期メッセージングといっても、発信先キューが利用できる場合はリアルタイムにメッセージを送ってくれるっぽいです。参考URLを掲載しておきます。

Real Time Data Integration with Service Broker and Other SQL Techniques
http://blogs.msdn.com/b/sql_service_broker/archive/2008/07/09/real-time-data-integration-with-service-broker-and-other-sql-techniques.aspx
Service Broker: Performance and Scalability Techniques
http://msdn.microsoft.com/en-us/library/dd576261(v=sql.100).aspx

検証環境

SQL Server 2012 Developer Edition (ドメインのメンバサーバー)

1. メッセージ交換オブジェクトの作成

Service Broker を使用して単一のデータベース内でメッセージを交換する場合、次の定義が必要です。

送受信メッセージに使用するデータ型
送受信に使用するメッセージの組み合わせを定義したコントラクト
メッセージ受信に使用するキュー
メッセージ交換で使用する受信用のキューとメッセージのコントラクトの組み合わせを定義したサービス

異なるSQL Server インスタンス間でメッセージを交換する場合はルートやエンドポイントの定義や証明書の設定などさらに必要ですが今回は不要なので定義しません。

冒頭のURLで紹介したチュートリアルとほぼ同じですが次のように定義します。コメントで記載していますが、サービスブローカーが対象データベースで有効になっているかは sys.databases ビューのis_broker_enabled で確認できます。サービスブローカーは既定で有効になっていますが、有効関する場合は、コメントにしたサンプルスクリプトのように alter database に set enable_broker を設定します。CREATE MESSAGE_TYPE などはメッセージに使用る型の定義やコントラクト、キュー、サービスを定義しています。下部の sys.service_message_types 等のビューをselectしているのはCREATE 結果の確認用です。

use master
go

-- サービスブローカーは既定で有効
--select is_broker_enabled
--  from sys.databases
-- where name = N'TestDb'
--;

-- サービスブローカーを有効化する
--alter database TestDb
--  set enable_broker
--;

-- サービスブローカーを無効化する
--alter database TestDb
--  set disable_broker
--;

use TestDb
go

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

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

-- 発信先キュー(TARGET)とサービスの作成
CREATE QUEUE TestDb1TargetQueue;

CREATE SERVICE [//Sample01/TestDb/TargetService]
    ON QUEUE TestDb1TargetQueue
	([//Sample01/TestDb/SampleContract]);
GO
-- 発信側キュー(INITIATOR)とサービスの作成
-- サービスの定義でコントラクトを指定していないため
-- 他のサービスからの発信先とならない
CREATE QUEUE TestDb1InitiatorQueue;

CREATE SERVICE [//Sample01/TestDb/InitiatorService]
    ON QUEUE TestDb1InitiatorQueue;
GO
-- メッセージ一覧を表示
select *
  from sys.service_message_types
-- コントラクト一覧を表示
select *
  from sys.service_contracts
-- キュー一覧を表示
select *
  from sys.service_queues
-- サービス一覧を表示
select *
  from sys.services

同一データベース内でメッセージ交換する場合でも発信側と発信先のサービス定義とキューが必要です。

2. 発信側からメッセージ交換の開始とメッセージの送信

メッセージ交換を開始するために BEGIN DIALOG 文を使用します。FROM, TO で　メッセージ交換に使用するサービスを指定し、ON でメッセージ交換に使用するコントラクトを指定します。send 文で作成したDialog に対してメッセージを送信します。その際メッセージの型を指定しています。

USE TestDb;
GO

-- メッセージ交換開始
declare @initDlgHandle uniqueidentifier;
declare @requestMsg nvarchar(100);

begin tran;

-- ダイアログ開始
begin dialog @initDlgHandle
      from service [//Sample01/TestDb/InitiatorService]
	  to   service N'//Sample01/TestDb/TargetService'
	  on   contract [//Sample01/TestDb/SampleContract]
	  with encryption = off;

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

-- メッセージ送信
send on conversation @initDlgHandle
        message type [//Sample01/TestDb/RequestMessage]
		(@requestMsg)

select @requestMsg

commit tran

トランザクションを使用しているのでロールバックした場合、メッセージ交換もロールバックされることが保障されます。発信先に送信されたメッセージは select * from TestDb1TargetQueue ステートメントを使用することでキューを確認できます。

3. 発信先でのメッセージの受信と返信とメッセージ交換の終了処理

発信先のキューTestDb1TargetQueueに受信したメッセージが格納されているためそのメッセージの受信処理とメッセージ交換終了のメッセージ(END CONVERSATION)を送ります。

下記に受信に使用したT-SQLを記載します。 RECEIVE 文でメッセージを受信します。トランザクションがコミットすると RECEIVEされたメッセージがキューから削除されます。サンプルでは受信したメッセージを xml 型に変換して value 関数でテキストを取り出しています。発信側で行ったと同じように SEND ON 文で応答メッセージを送信しています。その際メッセージの型も指定しています。続いて END CONVERSATION 文で終了のメッセージ(http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog型)を送信しています。

USE TestDb;
GO

-- 要求の受信
declare @receiveReqDlgHandle uniqueidentifier;
declare @receiveReqMsg nvarchar(100);
declare @receiveReqMsgName sysname;

begin tran;

waitfor
( receive top(1)
    @receiveReqDlgHandle = conversation_handle,
    @receiveReqMsg = message_body,
    @receiveReqMsgName = message_type_name
  from TestDb1TargetQueue
), timeout 1000;

select @receiveReqMsg as ReceiveRequestMessage

---- 5回ロールバックするとキューが無効化されるので注意
--rollback tran;

if @receiveReqMsgName = N'//Sample01/TestDb/RequestMessage'
  begin
    -- メッセージの取り出し
    select CONVERT(xml, @receiveReqMsg).value('(/RequestMsg)[1]', 'nvarchar(100)')
    
	declare @replyMsg nvarchar(100);
	set @replyMsg = N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';
	send on conversation @receiveReqDlgHandle
	  message type [//Sample01/TestDb/ReplyMessage](@replyMsg);

    end conversation @receiveReqDlgHandle;

    select @replyMsg as sentreplymsg	    
  end
-- 受信操作コミット
commit tran;
GO

xml 型のデータに使用できる関数は下記URL参照

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

受信テスト時に何度(5回)もロールバックをしているとキューが使用不可になります。その場合は次のスクリプトのようにキューを有効化します。

-- キューの有効/無効
ALTER QUEUE TestDb1TargetQueue WITH
 STATUS = ON;

4.発信側から応答メッセージの受信とメッセージ交換の終了処理

発信側では応答メッセージを確認して、メッセージを終了(END CONVERSATION) します。通常メッセージは発信先、発信側ともに終了(END CONVERSATION) を行って終了します。

USE TestDb;
GO

-- 応答の受信とメッセージ交換の終了
declare @receiveReplyMsg nvarchar(100);
declare @receiveReplyDlgHandle uniqueidentifier;

begin tran;
waitfor
(
  receive top (1)
    @receiveReplyDlgHandle = conversation_handle,
    @receiveReplyMsg = message_body  
	from TestDb1InitiatorQueue
), timeout 1000;

select @receiveReplyMsg as receiveReplyMsg;

end conversation @receiveReplyDlgHandle;

commit tran;

以上で　同一のデータベース間でのメッセージ交換が終了しました。

5.まとめ

説明は以上です。Service Broker 素人なので、ほぼ参考URLのチュートリアルのままでしたがシンプルなケース(単一データベース間のメッセージ交換) で動作確認できました。

メッセージ交換用に作成したオブジェクトは次のスクリプトで削除できます。

Use TestDb;

-- 配信側サービスの削除
IF EXISTS (SELECT * FROM sys.services WHERE name = N'//Sample01/TestDb/TargetService')
     DROP SERVICE [//Sample01/TestDb/TargetService];

-- 配信側キューの削除
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'TestDb1TargetQueue')
     DROP QUEUE TestDb1TargetQueue;

-- 配信先サービスの削除
IF EXISTS (SELECT * FROM sys.services WHERE name = N'//Sample01/TestDb/InitiatorService')
     DROP SERVICE [//Sample01/TestDb/InitiatorService];

-- 配信先キューの削除
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'TestDb1InitiatorQueue')
     DROP QUEUE TestDb1InitiatorQueue;

-- コントラクトの削除
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = N'//Sample01/TestDb/SampleContract')
     DROP CONTRACT [//Sample01/TestDb/SampleContract];

-- メッセージ型の削除
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'//Sample01/TestDb/RequestMessage')
     DROP MESSAGE TYPE [//Sample01/TestDb/RequestMessage];

IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = N'//Sample01/TestDb/ReplyMessage')
     DROP MESSAGE TYPE [//Sample01/TestDb/ReplyMessage];
GO

他に参考になりそうなページも載せておきます。

Service Broker のステートメント
http://msdn.microsoft.com/ja-jp/library/ff848765.aspx
Service Broker カタログビュー (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms173780.aspx
Service Broker 関連の動的管理ビュー (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms176110.aspx
ssbdiagnose ユーティリティ (Service Broker)
http://msdn.microsoft.com/ja-jp/library/bb934450.aspx

// Net planet-es V2