SQL Server 2008中Service Broker基础应用(下 )
时间:2011-05-09 来源:Lipton
【转载】http://blog.csdn.net/downmoon/archive/2011/04/12/6317360.aspx
导读:在上篇《SQL Server 2008中Service Broker基础应用(上) 》中,简要介绍了Service Broker的一般步骤,本文继续介绍Service Broker的设置会话优先级,存储过程中实现。
一、Service Broker的设置会话优先级
自SQL Server 2008起,对非常活跃的Service Broker应用程序,提供了设置优先级的命令CREATE BROKER PRIORITY(http://msdn.microsoft.com/en-us/library/bb934170.aspx )。通过该命令,可以设置从1至10共10个等级的颗粒度来调试会话的优先级,默认为5。在此之前,你必须得首先打开HONOR_BROKER_PRIORITY开关。
view plaincopy to clipboardprint?
- -- 设置会话优先级
- --启用会话优先级选项
- ALTER DATABASE BookStore
- SET HONOR_BROKER_PRIORITY ON
- --启用会话优先级选项
- ALTER DATABASE BOOKDistribution
- SET HONOR_BROKER_PRIORITY ON
- --查看设置结果
- 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],--本地服务
- REMOTE_SERVICE_NAME = ANY,--远程服务为ANY,即Service Broker端点的任何相关服务
- PRIORITY_LEVEL = 10)--设置优先级为10
通过sys.conversation_priorities目录视图,查询优先级
view plaincopy to clipboardprint?
- 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
- */
如果你希望包含服务和契约名称,可以将服务和从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 )目录视图关联起来。
view plaincopy to clipboardprint?
- USE BookDistribution
- GO
- --创建目标服务的优先级,与发起方的优先级保持一致,
- --以使会话的优先级设置覆盖整个会话的生命周期
- 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')
- --修改远程服务名称
- ALTER BROKER PRIORITY Conv_Priority_BookOrderContract_BookOrderService
- FOR CONVERSATION
- SET (PRIORITY_LEVEL = 9)
- --设置优先级
- --删除优先级设置
- DROP BROKER PRIORITY Conv_Priority_BookOrderContract_BookOrderService
二、Service Broker的存储过程实现
在上文中,我们使用的临时T-SQL来演示Service broker的步骤,事实上, 我们完全可以通过存储过程或外部应用程序自动激活并处理队列中的消息。使用Create Queue(http://msdn.microsoft.com/en-us/library/ms190495.aspx )和Alter Queue(http://msdn.microsoft.com/en-us/library/ms189529.aspx )选项,也可以指定可以激活并处理在同一队列中传入的消息的、同时执行的相同服务程序的数量。
继续上文的示例:
view plaincopy to clipboardprint?
- -- 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消息类型的逻辑。如果发送发后者,特定会话的句柄的特定会话会结束。如果接收到图书订单消息类型,它的消息将插入到表中,并且返回订单确认信息。
可以使用Alter Queue命令修改既有的队列。这个命令使用与Create Queue相同的选项,它允许改变队列的状态与保持期、待激活的存储过程、队列读取存储过程实例的最大数量以及过程的安全模式契约。
Alter Queue包括一个额外的参数Drop,它用来删除队列的所有存储过程激活设置。
使用Alter Queue命令将存储过程绑定到既有的队列:
view plaincopy to clipboardprint?
- ----使用Alter Queue命令将存储过程绑定到既有的队列
- ALTER QUEUE dbo.BookDistributionQueue
- WITH ACTIVATION (STATUS = ON,
- PROCEDURE_NAME = dbo.usp_SB_ReceiveOrders,
- MAX_QUEUE_READERS = 2,--独立处理队列中不同消息的同一存储过程同时执行的最大数量
- EXECUTE AS SELF)--即存储过程将以与执行Alter Queue命令的主体的相同的权限来执行
为了测试BookStore数据库的新服务程序,开始一个会话并设置新顺序:
view plaincopy to clipboardprint?
- 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);
当队列Status=ON并且队列中到到达新消息时,执行存储过程来处理传入的消息。可以使用存储过程或外部程序,但使用存储过程的好处是,它们提供了处理消息、自动执行所有需要的响应和相关业务任务的简单的封装好的组件。
如果在目标队列上有存储过程被执行,并且激活新的接收到的消息,那么应该已经有订单确认消息返回到dbo.BookStoreQueue:
view plaincopy to clipboardprint?
- SELECT conversation_handle, CAST(message_body as xml) message
- FROM dbo.BookStoreQueue
- /*
- conversation_handle message
- A7B7FA73-5B5F-E011-8B4E-001C23FA56DD <orderreceived id="3439" />
- */
小结:本文主要介绍Service Broker的设置会话优先级,存储过程中实现。
相关阅读 更多 +