Service Broker基礎(chǔ)應(yīng)用(下）
導(dǎo)讀：在上篇《SQL Server 2008中Service Broker基礎(chǔ)應(yīng)用(上）》中，簡要介紹了Service Broker的一般步驟，本文繼續(xù)介紹Service Broker的設(shè)置會(huì)話優(yōu)先級，存儲過程中實(shí)現(xiàn)。
一、Service Broker的設(shè)置會(huì)話優(yōu)先級
自SQL Server 2008起，對非?；钴S的Service Broker應(yīng)用程序，提供了設(shè)置優(yōu)先級的命令CREATE BROKER PRIORITY（http://msdn.microsoft.com/en-us/library/bb934170.aspx）。通過該命令，可以設(shè)置從1至10共10個(gè)等級的顆粒度來調(diào)試會(huì)話的優(yōu)先級，默認(rèn)為5。在此之前，你必須得首先打開HONOR_BROKER_PRIORITY開關(guān)。
-- 設(shè)置會(huì)話優(yōu)先級
--啟用會(huì)話優(yōu)先級選項(xiàng)
ALTER DATABASE BookStore
SET HONOR_BROKER_PRIORITY ON
--啟用會(huì)話優(yōu)先級選項(xiàng)
ALTER DATABASE BOOKDistribution
SET HONOR_BROKER_PRIORITY ON
--查看設(shè)置結(jié)果
SELECT name, is_honor_broker_priority_on
FROM sys.databases
WHERE name IN ('BookStore', 'BookDistribution')
/*
name is_honor_broker_priority_on
BookStore 1
BookDistribution 1
*/
USE BookStore
GO
CREATE BROKER PRIORITY Conv_Priority_BookOrderContract_BookOrderService
FOR CONVERSATION
SET (CONTRACT_NAME = [//SackConsulting/BookOrderContract],--特定的契約
LOCAL_SERVICE_NAME = [//SackConsulting/BookOrderService],--本地服務(wù)
REMOTE_SERVICE_NAME = ANY,--遠(yuǎn)程服務(wù)為ANY,即Service Broker端點(diǎn)的任何相關(guān)服務(wù)
PRIORITY_LEVEL = 10)--設(shè)置優(yōu)先級為10
通過sys.conversation_priorities目錄視圖,查詢優(yōu)先級
SELECT name, priority, service_contract_id,
local_service_id,remote_service_name
FROM sys.conversation_priorities cp
/*
name priority service_contract_id local_service_id remote_service_name
Conv_Priority_BookOrderContract_BookOrderService 10 65536 65536 NULL
*/
如果你希望包含服務(wù)和契約名稱，可以將服務(wù)和從sys.conversation_priorities（http://msdn.microsoft.com/zh-cn/library/bb895280%28v=sql.100%29.aspx）返回的契約ID與sys.service_contracts（http://msdn.microsoft.com/en-us/library/ms184378.aspx），sys.services（http://msdn.microsoft.com/en-us/library/ms174429.aspx）目錄視圖關(guān)聯(lián)起來。
USE BookDistribution
GO
--創(chuàng)建目標(biāo)服務(wù)的優(yōu)先級，與發(fā)起方的優(yōu)先級保持一致,
--以使會(huì)話的優(yōu)先級設(shè)置覆蓋整個(gè)會(huì)話的生命周期
CREATE BROKER PRIORITY Conv_Priority_BookOrderContract_BookDistributionService
FOR CONVERSATION
SET (CONTRACT_NAME = [//SackConsulting/BookOrderContract],
LOCAL_SERVICE_NAME = [//SackConsulting/BookDistributionService],
REMOTE_SERVICE_NAME = ANY,
PRIORITY_LEVEL = 10)
USE BookStore
GO
ALTER BROKER PRIORITY Conv_Priority_BookOrderContract_BookOrderService
FOR CONVERSATION
SET (REMOTE_SERVICE_NAME = '//SackConsulting/BookDistributionService')
--修改遠(yuǎn)程服務(wù)名稱
ALTER BROKER PRIORITY Conv_Priority_BookOrderContract_BookOrderService
FOR CONVERSATION
SET (PRIORITY_LEVEL = 9)
--設(shè)置優(yōu)先級
--刪除優(yōu)先級設(shè)置
DROP BROKER PRIORITY Conv_Priority_BookOrderContract_BookOrderService
二、Service Broker的存儲過程實(shí)現(xiàn)
在上文中，我們使用的臨時(shí)T-SQL來演示Service broker的步驟，事實(shí)上， 我們完全可以通過存儲過程或外部應(yīng)用程序自動(dòng)激活并處理隊(duì)列中的消息。使用Create Queue（http://msdn.microsoft.com/en-us/library/ms190495.aspx）和Alter Queue（http://msdn.microsoft.com/en-us/library/ms189529.aspx）選項(xiàng)，也可以指定可以激活并處理在同一隊(duì)列中傳入的消息的、同時(shí)執(zhí)行的相同服務(wù)程序的數(shù)量。
繼續(xù)上文的示例：
-- Creating the Bookstore Stored Procedure
USE BookDistribution
GO
CREATE PROCEDURE dbo.usp_SB_ReceiveOrders
AS
DECLARE @Conv_Handler uniqueidentifier
DECLARE @Conv_Group uniqueidentifier
DECLARE @OrderMsg xml
DECLARE @TextResponseMsg varchar(8000)
DECLARE @ResponseMsg xml
DECLARE @Message_Type_Name nvarchar(256);
DECLARE @OrderID int;
-- XACT_ABORT automatically rolls back the transaction when a runtime error occurs
SET XACT_ABORT ON
BEGIN TRAN;
RECEIVE TOP(1) @OrderMsg = message_body,
@Conv_Handler = conversation_handle,
@Conv_Group = conversation_group_id,
@Message_Type_Name = message_type_name
FROM dbo.BookDistributionQueue;
IF @Message_Type_Name = '//SackConsulting/SendBookOrder'
BEGIN
INSERT dbo.BookOrderReceived
(conversation_handle, conversation_group_id, message_body)
VALUES
(@Conv_Handler,@Conv_Group, @OrderMsg )
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]
(@ResponseMsg );
END
IF @Message_Type_Name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @Conv_Handler;
END
COMMIT TRAN
GO
解析：該存儲過程包含處理//SackConsulting/SendBookOrder和http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog消息類型的邏輯。如果發(fā)送發(fā)后者，特定會(huì)話的句柄的特定會(huì)話會(huì)結(jié)束。如果接收到圖書訂單消息類型，它的消息將插入到表中，并且返回訂單確認(rèn)信息。
可以使用Alter Queue命令修改既有的隊(duì)列。這個(gè)命令使用與Create Queue相同的選項(xiàng)，它允許改變隊(duì)列的狀態(tài)與保持期、待激活的存儲過程、隊(duì)列讀取存儲過程實(shí)例的最大數(shù)量以及過程的安全模式契約。
Alter Queue包括一個(gè)額外的參數(shù)Drop，它用來刪除隊(duì)列的所有存儲過程激活設(shè)置。
使用Alter Queue命令將存儲過程綁定到既有的隊(duì)列：
----使用Alter Queue命令將存儲過程綁定到既有的隊(duì)列
ALTER QUEUE dbo.BookDistributionQueue
WITH ACTIVATION (STATUS = ON,
PROCEDURE_NAME = dbo.usp_SB_ReceiveOrders,
MAX_QUEUE_READERS = 2,--獨(dú)立處理隊(duì)列中不同消息的同一存儲過程同時(shí)執(zhí)行的最大數(shù)量
EXECUTE AS SELF)--即存儲過程將以與執(zhí)行Alter Queue命令的主體的相同的權(quán)限來執(zhí)行
為了測試BookStore數(shù)據(jù)庫的新服務(wù)程序，開始一個(gè)會(huì)話并設(shè)置新順序：
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="3490" customer="29" orderdate="7/22/2008">
<LineItem ItemNumber="1" ISBN="1-59059-592-0" Quantity="2" />
</order>';
SEND ON CONVERSATION @Conv_Handler
MESSAGE TYPE [//SackConsulting/SendBookOrder]
(@OrderMsg);
當(dāng)隊(duì)列Status＝ON并且隊(duì)列中到到達(dá)新消息時(shí)，執(zhí)行存儲過程來處理傳入的消息?？梢允褂么鎯^程或外部程序，但使用存儲過程的好處是，它們提供了處理消息、自動(dòng)執(zhí)行所有需要的響應(yīng)和相關(guān)業(yè)務(wù)任務(wù)的簡單的封裝好的組件。
如果在目標(biāo)隊(duì)列上有存儲過程被執(zhí)行，并且激活新的接收到的消息，那么應(yīng)該已經(jīng)有訂單確認(rèn)消息返回到dbo.BookStoreQueue:
SELECT conversation_handle, CAST(message_body as xml) message
FROM dbo.BookStoreQueue
/*
conversation_handle message
A7B7FA73-5B5F-E011-8B4E-001C23FA56DD <orderreceived id="3439" />
*/
小結(jié)：
本文主要介紹Service Broker的設(shè)置會(huì)話優(yōu)先級，存儲過程中實(shí)現(xiàn)。下文將介紹Service broker的遠(yuǎn)程實(shí)現(xiàn)。
原文鏈接：http://www.cnblogs.com/downmoon/archive/2011/04/12/2013333.html
【編輯推薦】
