【SQL Server学习笔记】Service Broker创建异步的、数据驱动的消息应用程序

Service Broker 可以创建异步的、数据驱动的消息应用程序，它允许一个数据库发送消息到其他数据库，而不需要等待响应，即使远程数据库不能立即处理这些消息，发送数据库也可以继续其他操作。通过使用T-SQL对象和命令，就可以完成管理Service Broker。

 

Service Broker 为SQL Server提供消息队列，这样就可以从数据库中发送异步事务性消息到队列，在队列中这些消息将会被其他服务获取和处理，该服务可能运行在其他数据库、或服务器上。另外，对于异步程序，发送一条消息，并且应用程序不需要等待原始消息已被接收、或处理的确认信息，就可以处理其他相关的任务。一旦完成特定任务，两个Service Broker 服务之间的会话就可以显式的结束。

 

 

Service Broker包含了一些开箱即用的特性，它处理当试图创建自己的异步消息系统时，可能经常遇到的复杂问题。

比如：

首先，Service Broker 消息要保证以适当的顺序，或者以他们发送的原始顺序进行接收，而且这些消息也只能被接收一次(调度程序保证不会重复的读取)，并且可以作为同一个会话的一部分发送，与任务的同一个实例相关；

 

其次，Service Broker 保证了消息的发送，当尝试发送第一个消息时，目标数据库(消息的接收者)不可用，消息将加入到发送方的数据库的队列中，当目标数据库变为可用时，发送者将会尝试发送这个消息；

 

再次，由于Service Broker 是内建在SQL Server数据库中的，可以与数据库的其余部分仪器备份，所以这些消息在数据库发生故障的情况下也是可以恢复的；

 

最后使用Service Broker 的事件通知功能，可以跟踪数据库和SQL Server实例的事件，这个与SQL Trace相似，但事件通知是异步的，并且对SQL Server实例整体的性能的影响最小，而SQL Trace对性能的影响较大。

use mastergoif not exists(select 1 from sys.databases where name = 'BOOKSTORE')CREATE DATABASE bookstoregoif not exists(select 1 from sys.databases where name = 'bookDistribution')CREATE DATABASE bookDistributiongo--1.启动Service brokeralter database bookstore set enable_broker   --disable_broker可以禁用alter database bookstore set trustworthy on  --指明SQL Server实例是否信任该数据库以及其中的内容alter database bookDistribution set enable_brokeralter database bookDistribution set trustworthy on--2.创建数据库主密钥use bookstoregocreate master keyencryption by password = '123abc'use bookDistributiongocreate master keyencryption by password = 'abc123'--3.创建消息类型，定义了从Service Broker端点发送的消息中包含的数据类型use bookstoregocreate message type [//SackConsulting/SendBookOrder]validation = WELL_FORMED_XMLgocreate message type [//SackConsulting/BookOrderReceived]validation = WELL_FORMED_XMLuse bookDistributiongocreate message type [//SackConsulting/SendBookOrder]validation = well_formed_xmlgocreate message type [//SackConsulting/BookOrderReceived]validation = well_formed_xmlgo--4.创建约定,定义了在任务级别可以发送或者接收的消息类型use bookstoregocreate contract [//SackConsulting/BookOrderContract]([//SackConsulting/SendBookOrder] sent by initiator,  --可以由会话的发起方发送的消息类型[//SackConsulting/BookOrderReceived] sent by target  --可以由会话的目标方发送的消息类型)use bookDistributiongocreate contract [//SackConsulting/BookOrderContract]([//SackConsulting/SendBookOrder] sent by initiator,[//SackConsulting/BookOrderReceived] sent by target )go--5.创建队列,队列用来保存数据--通过select语句来查询队列，或者用receive命令从队列检索一条、多条消息--检索程序可以是外部的.net程序，不过通过存储过程来实现更方便use bookstoregocreate queue bookStoreQueuewith status = on go--在创建queue时可以把自动处理消息的程序,绑定到队列的激活选项,--此处通过手动控制队列中的信息交换use bookDistributiongocreate queue bookStoreDistributionQueuewith status = on go--6.创建服务这样就可以把消息队列绑定到一个或者多个约定上--服务使用队列和约定来定义一个或一组任务--服务是消息的发起方和接收方强制约定的规则,并将消息路由到正确的队列use bookstoregocreate service [//SackConsulting/BookOrderService]on queue dbo.bookStoreQueue ([//SackConsulting/BookOrderContract])use bookDistributiongocreate service [//SackConsulting/BookStoreDistributionService]on queue dbo.bookStoreDistributionQueue ([//SackConsulting/BookOrderContract])--7.1开始会话,发送消息use bookstoregodeclare @conversation_handler uniqueidentifier;declare @order_msg xml;begin dialog conversation @conversation_handlerfrom service [//SackConsulting/BookOrderService]to service '//SackConsulting/BookStoreDistributionService'on contract [//SackConsulting/BookOrderContract];set @order_msg = '<order id="1234" customer="22" orderdate="2012-10-01"><LineItem ItemNumber="1" ISBN="1-12345-123-0" Quantity="1" /></order>';--send语句可以发送消息send on conversation @conversation_handlermessage type [//SackConsulting/SendBookOrder](@order_msg);--7.2.1检索消息use bookDistributiongoselect message_type_name,      --消息类型名       CAST(message_body as xml) as message,  --消息       queuing_order,                         --队列顺序,从0开始              conversation_handle,                   --会话句柄       conversation_group_id                  --会话组idfrom dbo.bookstoreDistributionQueuecreate 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)--7.2.2receive语句会从队列中读取消息，并且把已经读取的消息删除declare @conversation_handler uniqueidentifierdeclare @conversation_group uniqueidentifierdeclare @order_msg xmldeclare @Text_response_msg varchar(max)declare @response_msg xmldeclare @orderID int;receive top(1)@order_msg = message_body,@conversation_handler = conversation_handle,@conversation_group = conversation_group_idfrom dbo.bookStoreDistributionQueue;insert into dbo.bookOrderReceived(conversation_handle,conversation_group_id,message_body)values(@conversation_handler,       @conversation_group,       @order_msg)select @orderID = @order_msg.value('(/order/@id)[1]','int')select @Text_response_msg = '<orderreceived id="' + CAST(@orderID as varchar(10)) + '"/>'select @response_msg = CAST(@Text_response_msg as xml);--7.2.3发送回复消息send on conversation @conversation_handlermessage type [//SackConsulting/BookOrderReceived](@response_msg);--7.3查看返回的消息,结束会话use bookstoregocreate 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 @conversation_handler uniqueidentifierdeclare @conversation_group uniqueidentifierdeclare @order_msg xmldeclare @text_response_msg varcahr(max);receive top(1)@conversation_handler = conversation_handle,@order_msg = message_bodyfrom dbo.bookstorequeueinsert into dbo.bookOrderConfirmation(conversation_handle,message_body)values(@conversation_handler,@order_msg)end conversation @conversation_handler;  --结束会话--7.4取出消息,判断是否是结束会话消息类型,如果是,那么结束会话use bookDistributiongodeclare @conversation_handler uniqueidentifierdeclare @conversation_group uniqueidentifierdeclare @order_msg xmldeclare @message_type_name nvarchar(256);receive top(1)@conversation_handler =  conversation_handle,@order_msg = message_body,@message_type_name = message_type_namefrom dbo.bookstoredistributionqueue/*======================================================结束会话会自动发送:http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog的消息类型到目标数据库，双方，包括发起方和目标，必须都结束会话========================================================*/if @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'beginend conversation @conversation_handler;end--7.5查看会话端点的状态select *from sys.conversation_endpoints--8.1设定Serivce Broker会话的优先级ALTER DATABASE BOOKSTOREset honor_broker_priority onalter database bookdistributionset honor_broker_priority on--8.2查看数据库的属性select name,       is_honor_broker_priority_onfrom sys.databaseswhere name in ('bookstore','bookdistribution')--8.3创建Service Broker优先级use bookstoregocreate broker priority conversation_priority_bookordercontract_bookorderservicefor conversationset (contract_name = [//SackConsulting/BookOrderContract],local_service_name = [//SackConsulting/BookOrderService],remote_service_name = any,priority_level = 10)select cp.name,       cp.priority,             --优先级              cp.service_contract_id,       sc.name,                 --约定名称              cp.local_service_id,       s.name,                  --服务名称       cp.remote_service_name       from sys.conversation_priorities cpinner join sys.service_contracts sc        on cp.service_contract_id = sc.service_contract_id        inner join sys.services s        on s.service_id = cp.local_service_iduse bookDistributiongocreate broker priority conversation_priority_bookordercontract_bookstoredistributionservicefor conversationset(contract_name = [//SackConsulting/BookOrderContract],local_service_name = [//SackConsulting/BookStoreDistributionService],remote_service_name = any,priority_level = 10)select cp.name,       cp.priority,             --优先级              cp.service_contract_id,       sc.name,                 --约定名称              cp.local_service_id,       s.name,                  --服务名称       cp.remote_service_name       from sys.conversation_priorities cpinner join sys.service_contracts sc        on cp.service_contract_id = sc.service_contract_id        inner join sys.services s        on s.service_id = cp.local_service_id--8.4修改优先级use bookstoregoalter broker priority conversation_priority_bookordercontract_bookorderservicefor conversationset(remote_service_name = '//SackConsulting/BookStoreDistributionService')use bookDistributiongoalter broker priority conversation_priority_bookordercontract_bookstoredistributionservicefor conversationset(priority_level = 9)--8.5删除优先级drop broker priority conversation_priority_bookordercontract_bookstoredistributionservice

 创建处理消息的存储过程

前面使用了临时的T-SQL来处理从队列传入的消息，也可以通过存储过程或外部应用程序创建服务程序，来自动的激活并处理队列中的消息的服务程序，同时还可以指定同时执行的服务程序的数量。

use bookDistributiongocreate procedure dbo.usp_service_broker_ReceiveOrdersasdeclare @conversation_handler uniqueidentifierdeclare @conversation_group uniqueidentifierdeclare @order_msg xmldeclare @text_response_msg varchar(8000)declare @response_msg xmldeclare @message_type_name nvarchar(156)declare @orderID int--当发生运行时错误时，会自动回滚事务set xact_abort onbegin tran;--接收消息receive top(1) @order_msg = message_body,    @conversation_handler = conversation_handle,    @conversation_group = conversation_group_id,    @message_type_name = message_type_namefrom dbo.bookStoreDistributionQueue--消息类型if @message_type_name = '//SackConsulting/SendBookOrder'begininsert into dbo.bookOrderReceived(conversation_handle,conversation_group_id,message_body)values(@conversation_handler,       @conversation_group,       @order_msg)       select @orderID = @order_msg.value('(/order/@id)[1]','int')select @text_response_msg = '<orderreceived id="' +                              CAST(@orderID as varchar(10)) +                             '"/>';select @response_msg = CAST(@text_response_msg as XML);send on conversation @conversation_handlermessage type [//SackConsulting/BookOrderReceived](@response_msg);end--如果收到结束会话的消息if @message_type_name =        'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'    beginend conversation @conversation_handler;endcommit trango--修改队列设置alter queue dbo.BookstoreDistributionQueuewith activation (status = on,procedure_name = dbo.usp_service_broker_receiveOrders,max_queue_readers = 2,   --存储过程执行的最大数量execute as self)/*==========================================通过drop：删除与队列关联的所有激活信息alter queue dbo.BookstoreDistributionQueuewith activation (drop)============================================*/   use bookstoregodeclare @conversation_handler uniqueidentifierdeclare @order_msg xmlbegin dialog conversation @conversation_handlerfrom service [//SackConsulting/BookOrderService]to service '//SackConsulting/BookStoreDistributionService'on contract [//SackConsulting/BookOrderContract];set @order_msg = '<order id="1234" customer="22" orderdate="2012-10-01"><LineItem ItemNumber="1" ISBN="1-12345-123-0" Quantity="1" /></order>';--send语句可以发送消息send on conversation @conversation_handlermessage type [//SackConsulting/SendBookOrder](@order_msg);--可以接收到一个消息，这个消息是对方在接受到下消息后，激活存储过程后，由存储过程回复的消息select conversation_handle,       CAST(message_body as xml)from dbo.bookStoreQueue        

 实现远程服务器的Service Broker

前面的例子是同一个SQL Server实例的两个数据库，但大部分情况会将Service Broker设置为，使用在两个或多个SQL Server实例上的数据库。为了实现跨越服务器通信，可以通过Windows身份验证或基于证书的身份验证，启用传输安全模式、启用对话安全模式、创建路由、创建远程绑定。

--在实例1上use mastergoif exists(select 1 from sys.databases where name = 'bookstore')drop database bookstoreelsecreate database bookstoregoalter database bookstore set enable_brokeralter database bookstore set trustworthy onuse bookstoregocreate message type [//BookConsulting/SendBookOrder]validation = well_formed_xmlgocreate message type [//BookConsulting/BookOrderReceived]validation = well_formed_xmlgocreate contract [//BookConsulting/BookOrderContract]([//BookConsulting/SendBookOrder] sent by initiator,[//BookConsulting/BookOrderReceived] sent by target)gocreate queue BookStoreQueuewith status = oncreate service [//BookConsulting/BookOrderService]on queue dbo.BookStoreQueue([//BookConsulting/BookOrderContract])--在实例2上use mastergoif exists(select 1 from sys.databases where name = 'bookdistribution')drop database bookdistributionelsecreate database bookdistributiongoalter database bookdistribution set enable_brokeralter database bookdistribution set trustworthy onuse bookdistributiongocreate message type [//BookConsulting/SendBookOrder]validation = well_formed_xmlgocreate message type [//BookConsulting/BookOrderReceived]validation = well_formed_xmlgocreate contract [//BookConsulting/BookOrderContract]([//BookConsulting/SendBookOrder] sent by initiator,[//BookConsulting/BookOrderReceived] sent by target)gocreate queue BookDistributionQueuewith status = oncreate service [//BookConsulting/BookDistributionService]on queue dbo.BookDistributionQueue([//BookConsulting/BookOrderContract])--启用传输安全模式,只是限制其他实例是否能访问本地服务器的端点--实例1use master go--1.删除已经存在的数据库主密钥drop master key--2.创建数据库主密钥create master key encryption by password = '123456!@#'--3.创建证书create certificate bookMasterCertwith subject = 'book Transport Security Service Broker', expiry_date = '2012-12-31' --4.备份证书backup certificate bookMasterCertto file = 'c:\bookMasterCert.cer'go--5.创建端点create endpoint service_broker_book_endpointstate = startedas tcp (listener_port = 4020)for service_broker (authentication = certificate bookMasterCert,encryption = required                   )--6.创建SQL Server的登录名create login service_broker_loginwith password = 'service_broker_login123'--7.创建数据库用户名create user service_broker_userfor login service_broker_login--8.授予数据库用户可以连接端点grant connect on endpoint::service_broker_book_endpoint               to service_broker_login--9.通过另一个实例复制到本地服务器上的证书文件,来创建证书create certificate bookDistributionMasterCertauthorization service_broker_userfrom file = 'c:\bookDistributionMasterCert.cer'go--实例2use master go--删除数据库主密钥drop master keycreate master key encryption by password = '123456&^%'create certificate bookDistributionMasterCertwith subject = 'bookDistribution Transport Security Service Broker', expiry_date = '2012-12-31' backup certificate bookDistributionMasterCertto file = 'c:\bookDistributionMasterCert.cer'create endpoint service_broker_bookdistribution_endpointstate = startedas tcp (listener_port = 4021)for service_broker (authentication = certificate bookDistributionMasterCert,encryption = required                   )create login service_broker_loginwith password = 'service_broker_login123'create user service_broker_userfor login service_broker_logingrant connect on endpoint::service_broker_bookdistribution_endpoint               to service_broker_logincreate certificate bookMasterCertauthorization service_broker_userfrom file = 'c:\bookMasterCert.cer'go--启用对话安全模式--实例1use bookstorego--1.创建数据库主密钥create master key encryption by password = '123456!@#'  --2.创建证书,这里可以给当前数据库用户创建多个证书,不会有影响--当接收到其他服务器传送过来的消息时,可以用这个证书来解密消息create certificate BookStoreCertwith subject = 'BookStore service broker cert',     expiry_date = '2012-12-31'--3.备份证书backup certificate bookstorecertto file = 'c:\bookstorecert.cer'go--4.创建数据库用户,此用户只可以有一个证书create user bookDistributionUserwithout logingo--5.通过从另一个实例复制过来的证书,来创建证书,并指定所有者为此用户create certificate bookDistributionCertauthorization bookDistributionUser        --此用户只能拥有一个证书,                                          --在发送消息时会用这个证书来加密消息from file = 'c:\bookDistributionCert.cer'--6.授予此用户名在某个服务上发送的权限grant send on service::[//BookConsulting/BookOrderService] to bookDistributionUsergo--7.创建路由create route route_bookDistributionwith service_name = '//BookConsulting/BookDistributionService',     address = 'tcp://192.168.1.16:4021'--8.创建远程绑定create remote service binding bookDistributionBindingto service '//BookConsulting/BookDistributionService'with user = bookDistributionUser--9.开始会话,发送消息declare @conversation_handler uniqueidentifierdeclare @order_msg xml;begin dialog conversation @conversation_handlerfrom service [//BookConsulting/BookOrderService]to service '//BookConsulting/BookDistributionService'on contract [//BookConsulting/BookOrderContract]set @order_msg = '<order id="1234" customer="22" orderdate="2012-10-01"><LineItem ItemNumber="1" ISBN="1-12345-123-0" Quantity="1" /></order>';--send语句可以发送消息send on conversation @conversation_handlermessage type [//BookConsulting/SendBookOrder](@order_msg);--启用对话安全模式--实例2use bookdistributiongocreate master key encryption by password = '123456&^%'  --当接收到对方发送的消息后,用此证书来解密create certificate BookDistributionCertwith subject = 'BookDistribution service broker cert',     expiry_date = '2012-12-31'backup certificate bookDistributioncertto file = 'c:\bookDistributioncert.cer'create user bookStoreUserwithout login--在发送之前,用此证书来加密消息create certificate bookStoreCertauthorization bookStoreUserfrom file = 'c:\bookStoreCert.cer'grant send on service::[//BookConsulting/BookDistributionService] to bookStoreUsercreate route route_bookStorewith service_name = '//BookConsulting/BookOrderService',     address = 'tcp://192.168.9.67:4020'create remote service binding bookStoreBindingto service '//BookConsulting/BookOrderService'with user = bookStoreUser--查询消息SELECT *FROM dbo.bookdistributionqueue          

事件通知
事件通知是集成到Service Broker的功能，这样可以在SQL Server实例中异步捕获SQL事件，将事件信息路由到特定的队列中。只需要最小的系统开销，就可以跟踪发生在SQL Server实例的事件，比如用户登录，存储过程重新编译，权限修改，对象处理（包括：对数据库、程序集、角色、表的create/alter/drop事件）。

使用事件通知只需要创建队列和Service Broker组件，在SQL Server中已经内建了，用来捕捉和发送事件通知的消息类型、约定。

IF NOT exists(select 1              from sys.databases              where name = 'EventTracking')              create database EventTrackingelsedrop database EventTrackinggouse eventtrackinggo--1.创建队列create queue SQLEventQueuewith status = ongo--2.在队列上创建服务,关联到内建的事件通知约定create service [//EventTracking/TrackLoginAlterService]on queue SQLEventQueue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);--3.查询guidselect service_broker_guid --6AE234DF-C4BB-4C1B-9E08-F4EA66359B6Afrom sys.databaseswhere name = 'EventTracking'--4.创建server范围的事件通知,来跟踪SQL Server实例中所有登录名的创建,修改,删除create event notification EVENT_LoginEventon serverfor create_login,alter_login,drop_loginto service '//EventTracking/TrackLoginAlterService',   '6AE234DF-C4BB-4C1B-9E08-F4EA66359B6A'   --service_broker_guid--5.创建一个登录名,事件通知,把消息放入队列create login login_ggg with password = '123456!@'--6.查询消息select CAST(message_body as xml) as event_Infofrom dbo.sqleventqueue