SQL Server 2008中SQL應用系列--目錄索引
導讀：本文主要涉及Service Broker的基本概念及建立一個Service Broker應用程式的基本步驟。
一、前言：
Service Broker為SQL Server提供消息隊列，這提供了從資料庫中發送異步事務性消息隊列的方法。Service Broker消息可以保證以适當的順序或原始的發送順序不重複地一次性接收。并且因為内建在SQL Server中，這些消息在資料庫發生故障時是可以恢複的，也可以随資料庫一起備份。在SQL Server 2008中，還引入了使用Create Broker Priority指令對會話設定優先級，可以對重要的或不重要的會話進行優先級設定，以保證消息合理地處理。
本文假定一個線上資料庫BookStore中存儲了一些業務訂單。我們使用Service Broker應用程式将消息發送到另一個資料庫BookDistribution，該資料庫是分離的應用程式調用，該應用程式控制倉庫入庫和出庫傳遞， 并傳回消息給BookStore。
建立Service Broker應用程式大體步驟如下：
1、定義希望應用程式執行的異步任務。
2、确定Service Broker的發起方服務和目标服務是否建立在同一個SQL Server執行個體中。如果是兩個執行個體，執行個體間的通信還需要建立經過證書認證或NT安全的身份認證，并且要建立端點、路由以及對話安全模式。
3、如果沒有啟用，則在多方參與的資料庫中使用Alter Database指令設定Enable_broker以及Truseworthy資料庫選項。
4、為所有多方參與的資料庫建立資料庫主密鑰。
5、建立希望在服務之間發送的消息類型。
6、建立契約（Contract）來定義可以由發起方發送的各種消息以及由目标發送的消息類型的種類。
7、同時在兩方參與的資料庫中建立用于儲存消息的隊列。
8、同時在綁定特定約定到特定隊列的多方參與的資料庫中建立服務。
二、執行個體
下面我們通過一個示例來實作以上步驟：
（一）、啟用資料庫的Service Broker活動
-- Enabling Databases for Service Broker Activity
USE master
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'BookStore')
CREATE DATABASE BookStore
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'BookDistribution')
CREATE DATABASE BookDistribution
GO
ALTER DATABASE BookStore SET ENABLE_BROKER
GO
ALTER DATABASE BookStore SET TRUSTWORTHY ON
GO
ALTER DATABASE BookDistribution SET ENABLE_BROKER
GO
ALTER DATABASE BookDistribution SET TRUSTWORTHY ON
（二）、建立資料庫主密鑰
-- Creating the DatabaseMaster Key for Encryption
USE BookStore
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'I5Q7w1d3'
GO
USE BookDistribution
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'D1J3q5z8X6y4'
GO
（三）、管理消息類型
使用CREATE MESSAGE TYPE（http://msdn.microsoft.com/en-us/library/ms187744.aspx）指令,
-- Managing Message Types
Use BookStore
GO
-- 發送圖書訂單的消息類型
CREATE MESSAGE TYPE [//SackConsulting/SendBookOrder]
VALIDATION = WELL_FORMED_XML
GO
--目标資料庫發送的消息類型
CREATE MESSAGE TYPE [//SackConsulting/BookOrderReceived]
VALIDATION = WELL_FORMED_XML
GO
--執行同樣的定義
Use BookDistribution
GO
-- 發送圖書訂單的消息類型
CREATE MESSAGE TYPE [//SackConsulting/SendBookOrder]
VALIDATION = WELL_FORMED_XML
GO
--目标資料庫發送的消息類型
CREATE MESSAGE TYPE [//SackConsulting/BookOrderReceived]
VALIDATION = WELL_FORMED_XML
GO
--注意，此處沒有定義消息的内容。實際的消息是消息類型的執行個體。
（四）、建立契約（Contract）
使用Create Contract（http://msdn.microsoft.com/en-us/library/ms178528.aspx）
-- Creating Contracts
Use BookStore
GO
CREATE CONTRACT
[//SackConsulting/BookOrderContract]
( [//SackConsulting/SendBookOrder]
SENT BY INITIATOR,
[//SackConsulting/BookOrderReceived]
SENT BY TARGET
)
GO
USE BookDistribution
GO
CREATE CONTRACT
[//SackConsulting/BookOrderContract]
( [//SackConsulting/SendBookOrder]
SENT BY INITIATOR,
[//SackConsulting/BookOrderReceived]
SENT BY TARGET
)
GO
--發起方和目标的定義必須相同
（五）、建立隊列
隊列用來儲存資料。使用指令Create queue（http://msdn.microsoft.com/en-us/library/ms190495.aspx）
-- Creating Queues
Use BookStore
GO
--儲存BookDistribution過來的消息
CREATE QUEUE BookStoreQueue
WITH STATUS=ON
GO
USE BookDistribution
GO
--儲存BookStore過來的消息
CREATE QUEUE BookDistributionQueue
WITH STATUS=ON
GO
（六）、建立服務
服務定義端點，然後使用它來将消息隊列綁定到一個或多個契約上。服務使用隊列和契約來定義一個或一組任務。有點拗口，是不是？
服務是消息的發起方和接收方強制約定的規則，并将消息路由到正确的序列。
使用Create Service（http://msdn.microsoft.com/en-us/library/ms190332.aspx）指令。
-- Creating Services
Use BookStore
GO
CREATE SERVICE [//SackConsulting/BookOrderService]
ON QUEUE dbo.BookStoreQueue--指定的隊列綁定到契約
([//SackConsulting/BookOrderContract])
GO
USE BookDistribution
GO
CREATE SERVICE [//SackConsulting/BookDistributionService]
ON QUEUE dbo.BookDistributionQueue--指定的隊列綁定到契約
([//SackConsulting/BookOrderContract])
GO
（七）、啟動對話
對話會話（dialog conservation）是在服務之間進行消息交換的操作。
使用Begin Dialog Conversation（http://msdn.microsoft.com/en-us/library/ms187377.aspx） 指令建立新的會話。使用Send（http://msdn.microsoft.com/en-us/library/ms188407.aspx）來發送消息。使用End Conversation指令（http://msdn.microsoft.com/en-us/library/ms177521.aspx）結束會話。
-- Initiating a Dialog
Use BookStore
GO
--儲存會話句柄和訂單資訊
DECLARE @Conv_Handler uniqueidentifier
DECLARE @OrderMsg xml;
BEGIN DIALOG CONVERSATION @Conv_Handler--建立會話
FROM SERVICE [//SackConsulting/BookOrderService]
TO SERVICE '//SackConsulting/BookDistributionService'
ON CONTRACT [//SackConsulting/BookOrderContract];
SET @OrderMsg =
'<order id="3439" customer="22" orderdate="2/15/2011">
<LineItem ItemNumber="1" ISBN="1-59059-592-0" Quantity="1" />
</order>';
SEND ON CONVERSATION @Conv_Handler--發送到BookDistribution資料庫的隊列中
MESSAGE TYPE [//SackConsulting/SendBookOrder]
(@OrderMsg);
（八）、查詢隊列中傳入的消息
-- Querying the Queue for IncomingMessages
USE BookDistribution
GO
SELECT message_type_name, CAST(message_body as xml) message,
queuing_order, conversation_handle, conversation_group_id
FROM dbo.BookDistributionQueue
查詢結果：
（九）、檢索并響應消息
使用Receive語句（http://msdn.microsoft.com/en-us/library/ms186963.aspx）從隊列中讀取行（消息），也可以删除已經讀取的消息。Receive的結果可以填充到正常表中，也可以在局部變量中執行其他操作，或發送到其他service Broker消息。如果消息是XML資料類型的消息，則可以直接借助TSQL的XQuery來操作。
-- Receiving and Responding to aMessage
USE BookDistribution
GO
--建立一個表存放接收到的訂單資訊
CREATE TABLE dbo.BookOrderReceived
(BookOrderReceivedID int IDENTITY (1,1) NOT NULL,
conversation_handle uniqueidentifier NOT NULL,
conversation_group_id uniqueidentifier NOT NULL,
message_body xml NOT NULL)
GO
-- 聲明變量
DECLARE @Conv_Handler uniqueidentifier
DECLARE @Conv_Group uniqueidentifier
DECLARE @OrderMsg xml
DECLARE @TextResponseMsg varchar(8000)
DECLARE @ResponseMsg xml
DECLARE @OrderID int;
--從隊列中擷取消息，将接收值賦于局部變量
RECEIVE TOP(1) @OrderMsg = message_body,--TOP指定最多一條消息
@Conv_Handler = conversation_handle,
@Conv_Group = conversation_group_id
FROM dbo.BookDistributionQueue;
-- 将變量值插入表中
INSERT dbo.BookOrderReceived
(conversation_handle, conversation_group_id, message_body)
VALUES
(@Conv_Handler,@Conv_Group, @OrderMsg )
-- 使用XQuery進行抽取以響應消息訂單
SELECT @OrderID = @OrderMsg.value('(/order/@id)[1]', 'int' )
SELECT @TextResponseMsg =
'<orderreceived id= "' +
CAST(@OrderID as varchar(10)) +
'"/>';
SELECT @ResponseMsg = CAST(@TextResponseMsg as xml);
-- 使用既有的會話句柄，發送響應消息到發起方
SEND ON CONVERSATION @Conv_Handler
MESSAGE TYPE [//SackConsulting/BookOrderReceived](@OrderMsg)
（十）、結束會話
-- Ending a Conversation
USE BookStore
GO
-- 建立訂單确認表
CREATE TABLE dbo.BookOrderConfirmation
(BookOrderConfirmationID int IDENTITY (1,1) NOT NULL,
conversation_handle uniqueidentifier NOT NULL,
DateReceived datetime NOT NULL DEFAULT GETDATE(),
message_body xml NOT NULL)
DECLARE @Conv_Handler uniqueidentifier
DECLARE @Conv_Group uniqueidentifier
DECLARE @OrderMsg xml
DECLARE @TextResponseMsg varchar(8000);
RECEIVE TOP(1) @Conv_Handler = conversation_handle,
@OrderMsg = message_body
FROM dbo.BookStoreQueue
INSERT dbo.BookOrderConfirmation
(conversation_handle, message_body)
VALUES (@Conv_Handler,@OrderMsg );
END CONVERSATION @Conv_Handler;
GO
USE BookDistribution
GO
DECLARE @Conv_Handler uniqueidentifier
DECLARE @Conv_Group uniqueidentifier
DECLARE @OrderMsg xml
DECLARE @message_type_name nvarchar(256);
RECEIVE TOP(1) @Conv_Handler = conversation_handle,
@OrderMsg = message_body,
@message_type_name = message_type_name
FROM dbo.BookDistributionQueue
-- 雙方必須都結束會話
IF
@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @Conv_Handler;
END
--查詢會話狀态
SELECT state_desc, conversation_handle
FROM sys.conversation_endpoints
三、小結
本文通過一個執行個體示範了一個用來發送圖書訂單消息分發控制資料庫的簡單的消息交換應用程式。發起方發送圖書訂單，發回一個響應，并在兩個資料庫上使用END Conservation結束會話。現實場景中可以轉換為其他消息類型、契約、服務和隊列。合理運用Service Broker應用程式的異步特性可以防止因應用程式挂起而導緻業務系統産生瓶頸。
本文參考：
1、SQL Server 2005 Service Broker 初探
http://msdn.microsoft.com/zh-cn/library/ms345108%28v=sql.90%29.aspx
2、SQL Server 2008 Transact-SQL Recipes: A Problem-Solution Approach
http://www.amazon.com/Server-2008-Transact-SQL-Recipes-Problem-Solution/dp/1590599802
邀月注：本文版權由邀月和CSDN共同所有，轉載請注明出處。
助人等于自助! [email protected]