#眉標=SQL Server 2008、Service Broker、Message
#副標=實用SQL Server 2008（5）
#大標=資料庫訊息交換平台Service Broker(中)
#作者=文／圖		胡百敬



==<box>===========
程式1

--定義訊息型態只要符合 XML 規範即可
CREATE MESSAGE TYPE [EventMessage]
　　VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [ReplyEventMessage]
　　VALIDATION = WELL_FORMED_XML;
GO
--定義合約，要求對話的發起者和接收者各可以傳遞何種訊息
CREATE CONTRACT [EventCollectContract]
　　([EventMessage]
     SENT BY INITIATOR,
     [ReplyEventMessage]
     SENT BY TARGET);
GO

--建立佇列，以存放各種訊息
CREATE QUEUE InitiatorQueue;

--建立服務，服務的擁有者在上期專欄的範例中建立
--該擁有者同時擁有憑證
CREATE SERVICE [EventInitiatorService]
　　AUTHORIZATION InitiatorUser
　　ON QUEUE InitiatorQueue;
==<end>==============






==<box>===========
程式2

USE master;
GO
CREATE MASTER KEY
　　ENCRYPTION BY PASSWORD = N'<StrongPassword>';
GO

--使用在起始端所建立的憑證來彼此驗證身分
CREATE CERTIFICATE AuthCertificate
　　AUTHORIZATION DBO
　　FROM FILE=N'C:\Certs\AuthCertificate.cer'
　　WITH PRIVATE KEY(FILE='C:\Certs\AuthCertificate',
　　DECRYPTION BY PASSWORD='<StrongPassword>')
GO
IF EXISTS (SELECT * FROM sys.endpoints
　　WHERE name = N'TargetEndpoint')
　　DROP ENDPOINT EventTargetEndpoint;
GO     

--一個SQL Server 執行個體只能有一個Service Broker類型的端點
CREATE ENDPOINT EventTargetEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022)
FOR SERVICE_BROKER (AUTHENTICATION  = CERTIFICATE AuthCertificate, ENCRYPTION=SUPPORTED);
GO

--建立提供目的服務的資料庫
USE master;
GO
IF EXISTS (SELECT * FROM sys.databases
　　WHERE name = N'TargetDB')
　　DROP DATABASE TargetDB;
GO
CREATE DATABASE TargetDB;
GO
USE TargetDB;
GO
CREATE MASTER KEY
　　ENCRYPTION BY PASSWORD = N'<EnterStrongPassword1Here>';
GO
CREATE USER TargetUser WITHOUT LOGIN;
GO

--建立目的端使用的憑證，需要裝到發起端
CREATE CERTIFICATE TargetCertificate 
　　AUTHORIZATION TargetUser
    WITH SUBJECT = 'Target Certificate',
     EXPIRY_DATE = N'12/31/2010';
BACKUP CERTIFICATE TargetCertificate
  TO FILE = 
N'C:\Certs\TargetCertificate.cer';
CREATE MESSAGE TYPE [EventMessage]
    VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [ReplyEventMessage]
    VALIDATION = WELL_FORMED_XML;
GO
CREATE CONTRACT [EventCollectContract]
    ([EventMessage]
      SENT BY INITIATOR,
    [ReplyEventMessage]
      SENT BY TARGET);
GO
CREATE QUEUE TargetQueue;
CREATE SERVICE [EventTargetService]
   AUTHORIZATION TargetUser
   ON QUEUE TargetQueue
   ([EventCollectContract]);
==<end>==============




===<反灰>=============
CREATE ROUTE 路由名稱
[ AUTHORIZATION 擁有者名稱 ]
WITH  
   [ SERVICE_NAME = '服務名稱', ]
   [ BROKER_INSTANCE = 'Broker 執行個體識別碼' , ]
   [ LIFETIME = 路由到期時間, ]
   ADDRESS =  '網路位址'
==<end>==============




===<反灰>=============
TCP://{ dns_name | netbios_name | ip_address } :port_number
==<end>==============




===<反灰>=============
CREATE ROUTE [AutoCreatedLocal] WITH ADDRESS = N'LOCAL'
==<end>==============





==<box>===========
程式3

DECLARE @Cmd NVARCHAR(4000);
--此處的埠號碼就是當初定Service Broker端點的埠號碼
--因為要換不同的資料庫執行，改以組 SQL 語法的方式執行
SET @Cmd = N'USE InitiatorDB;
CREATE ROUTE TargetRoute
WITH SERVICE_NAME =
　　N''EventTargetService'',
　　ADDRESS = N''TCP://sql2k8:4022'';';
EXEC (@Cmd);

--在msdb定義跨執行個體間交換訊息時，
--收到本機的訊息要如何轉給本機上的服務
SET @Cmd = N'USE msdb
CREATE ROUTE InitiatorRoute
WITH SERVICE_NAME =
　　N''EventInitiatorService'',
　　ADDRESS = N''LOCAL''';
EXEC (@Cmd);
==<end>==============





==<box>===========
程式4

CREATE USER TargetUser WITHOUT LOGIN;
CREATE CERTIFICATE TargetCertificate 
   AUTHORIZATION TargetUser
   FROM FILE = 
N'C:\Certs\TargetCertificate.cer'

--遠端服務繫結以User擁有的憑證，
--讓Service Broker知道如何與遠端服務執行加解密
CREATE REMOTE SERVICE BINDING TargetBinding
   TO SERVICE
   N'EventTargetService'
   WITH USER = TargetUser;
GO

--SQL Server 2008 後，可以設定會談的優先權
--此處設定低於預設的5
CREATE BROKER PRIORITY InitiatorToTargetPriority
   FOR CONVERSATION
   SET (CONTRACT_NAME = [EventCollectContract],
     LOCAL_SERVICE_NAME = [EventInitiatorService],
     REMOTE_SERVICE_NAME = N'EventTargetService',
     PRIORITY_LEVEL = 3);
==<end>==============






==<box>===========
程式5

USE TargetDB
GO
CREATE USER InitiatorUser WITHOUT LOGIN;

CREATE CERTIFICATE InitiatorCertificate
   AUTHORIZATION InitiatorUser
   FROM FILE = 
N'C:\Certs\InitiatorCertificate.cer';

DECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N'USE TargetDB;
CREATE ROUTE InitiatorRoute
WITH SERVICE_NAME =
　　N''EventInitiatorService'',
　　ADDRESS = N''TCP://BYRONSUB:4022'';';
EXEC (@Cmd);

SET @Cmd = N'USE msdb
CREATE ROUTE TargetRoute
WITH SERVICE_NAME =
　　N''EventTargetService'',
　　ADDRESS = N''LOCAL''';
EXEC (@Cmd);
GO

--需要確定 msdb 有啟動Service Broker服務
ALTER DATABASE msdb SET ENABLE_BROKER

--允許擁有InitiatorCertificate憑證的使用者送訊息到此服務
--在起始端有個使用者同時擁有相同憑證，以及起始端服務
GRANT SEND
　　ON SERVICE::[EventTargetService]
　　TO InitiatorUser;
GO
CREATE REMOTE SERVICE BINDING InitiatorBinding
　　TO SERVICE N'EventInitiatorService'
　　WITH USER = InitiatorUser;
CREATE BROKER PRIORITY TargetToInitiatorPriority
    FOR CONVERSATION
    SET (CONTRACT_NAME = [EventCollectContract],
      LOCAL_SERVICE_NAME = [EventTargetService],
      REMOTE_SERVICE_NAME = N'EventInitiatorService',
      PRIORITY_LEVEL = 3);
==<end>==============






==<box>===========
程式6

USE InitiatorDB
GO
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(MAX);

--開始服務之間的對話，在兩個服務之間進行精確單次循序傳訊的交談
BEGIN TRANSACTION;
　　BEGIN DIALOG @InitDlgHandle
	 FROM SERVICE [EventInitiatorService]
	 TO SERVICE N'EventTargetService'
	 ON CONTRACT [EventCollectContract]
	 WITH
		ENCRYPTION = ON;

　　SET @RequestMsg = N'<EVENT_INSTANCE>
	  <EventType>CREATE_TABLE</EventType>
	  <PostTime>2009-04-15T13:24:57.977</PostTime>
	  …
　　</EVENT_INSTANCE>';

　　SEND ON CONVERSATION @InitDlgHandle
	  MESSAGE TYPE [EventMessage]
	  (@RequestMsg);
COMMIT TRANSACTION;

--檢視我們傳遞的資訊，以及Service Broker自動產生的ConversationHandle值
SELECT @RequestMsg AS SentRequestMsg, @InitDlgHandle AS ConversationHandle;
==<end>==============




===<反灰>=============
ALTER DATABASE [資料庫名稱] SET HONOR_BROKER_PRIORITY ON/OFF
==<end>==============




===<反灰>=============
select * from dbo.TargetQueue for xml auto,elements,BINARY BASE64
==<end>==============





==<box>===========
程式7

<dbo.TargetQueue>
  <status>1</status>
  <priority>3</priority>
  <queuing_order>0</queuing_order>
<conversation_group_id>A7356386-6E3C-DE11-B527-00155D016A06</conversation_group_id>
<conversation_handle>A8356386-6E3C-DE11-B527-00155D016A06</conversation_handle>
  <message_sequence_number>0</message_sequence_number>
  <service_name>EventTargetService</service_name>
  <service_id>65536</service_id>
  <service_contract_name>EventCollectContract</service_contract_name>
  <service_contract_id>65536</service_contract_id>
  <message_type_name>EventMessage</message_type_name>
  <message_type_id>65536</message_type_id>
  <validation>X</validation>
  <message_body>…</message_body>
</dbo.TargetQueue>
==<end>==============