SQLSERVER2005数据库对象_ServiceBroker

1、Server Broker是 SQL Server 2005 中新增加功能。一、简介（来自SQL 2K05的帮助文档）Microsoft SQL Server 2005 Service Broker可以帮助开发人员生成可伸缩的、安全的数据库应用程序。此项新技术是 数据库引擎 的一部分，它提供一个基于消息的通信平台，使独立的应用程序组件可以作为一个整体来运行。 Service Broker 包含用于异步编程的基础结构，可用于单个数据库或单个实例中的应用程序，也可用于分布式应用程序。Service Broker 提供了生成分布式应用程序所需的大部分基础结构，从而减少了应用程序的开发时间。利用 Se

2、rvice Broker ? 还可以轻松缩放应用程序，以容纳应用程序接收的通信流量。Service Broker是 Microsoft SQL Server 2005中的新技术，它可帮助数据库开发人员构建安全、可靠且可伸缩的应用程序。由于 Service Broker 是数据库引擎 的组成部分，因此管理这些应用程序就成为数据库日常管理的一部分。Service Broker 为 SQL Server 提供队列和可靠的消息传递。 Service Broker 对使用单个 SQL Server 实例的应用程序和在多个实例间分配工作的应用程序都适用。在单个 SQL Server实例中， Service

3、 Broker提供了可靠的异步编程模型。数据库应用程序通常使用异步编程来缩短交互式响应时间，并增加应用程序总吞吐量。二、 Server Broker实现消息的发送( 一同一实例同一数据库下的消息发送1. 准备工作：1.1 创建数据库 ssbDemouse mastergoif exists( select top 1 1from sys.sysdatabases with(nolockwhere name=N'ssbDemo'begindrop database ssbDemoendcreate database ssbDemogo1.2 允许 Server Broker-ch

4、eck database broker statesdeclare is_broker_enabled intselect top 1 is_broker_enabled=is_broker_enabled from sys.databases with (nolock where database_id=db_id(N'ssbDemo'if is_broker_enabled<>1begin-Enable SSBalter database ssbDemoset enable_brokeralter database ssbDemoset trustworthy

5、onend1.3 创建 Master Keyuse ssbDemogocreate master keyencryption by password = 'Cw24$g'2. 创建消息use ssbDemogo-Messageif exists (select top 1 1from sys.service_message_types with(nolock where name='HelloWorldRequest' begindrop message type HelloWorldRequest;endgocreate message type HelloW

6、orldRequest VALIDATION=NONEif exists (select top 1 1from sys.service_message_types with(nolock where name='HelloWorldRespone' begindrop message type HelloWorldRespone;endgocreate message type HelloWorldRespone VALIDATION=NONE3. 创建合约-Contractif exists (select top 1 1from sys.service_contracts

7、 with(nolockwhere name='HelloWorldContract'begindrop contract HelloWorldContract;endgocreate contract HelloWorldContract(HelloWorldRequest sent by initiator,HelloWorldRespone sent by target4. 创建队列和基于队列的服务-Queueif object_id('dbo.HelloWorldTargerQueue' is not null and exists(select top

8、 1 1from sys.objects with (nolockwhere object_id=object_id('dbo.HelloWorldTargerQueue'and type='SQ'begindrop queue dbo.HelloWorldTargerQueueendgocreate queue dbo.HelloWorldTargetQueueif exists (select top 1 1from sys.services with(nolockwhere name='HelloWorldRequestService'be

9、gindrop service HelloWorldRequestService;endgocreate service HelloWorldRequestService on queue HelloWorldTargetQueue(HelloWorldContractif object_id('dbo.HelloWorldInitiatorQueue' is not null and exists(select top 1 1from sys.objects with (nolockwhere object_id=object_id('dbo.HelloWorldIn

10、itiatorQueue'and type='SQ'begindrop queue dbo.HelloWorldInitiatorQueueendgoCreate queue dbo.HelloWorldInitiatorQueue if exists (select top 1 1from sys.services with(nolockwhere name='HelloWorldResponseService'begindrop service HelloWorldResponseService;endgocreate service HelloWo

11、rldResponeService on queue HelloWorldInitiatorQueue(HelloWorldContract5. 发送消息（ Server Broker 的消息发送，必须在事务中实现）-send messageuse ssbDemogoset nocount ondeclare conversionHandle uniqueidentifierbegin trybegin tran-begin dialog to serverbegin dialog conversionHandlefrom service HelloWorldResponeServiceto

12、service N'HelloWorldRequestService'on contract HelloWorldContractwith encryption=off,lifetime=600;-Send messagesend on conversation conversionHandle message type HelloWorldRequest (N'Hello world'commitend trybegin catch-get the the error infoselect error_message(end catch6. 接受消息（同样消息

13、的接受也必须在事务中）-receive message from raget queueset nocount ondeclare conversionHandle uniqueidentifierdeclare message_body nvarchar(maxdeclare message_type_name sysname-begin transactionbegin transactionwaitfor (receive top (1-only a piece of messagemessage_type_name = message_type_name-Receiving messa

14、ge type,conversionHandle=conversation_Handle-Dialog identifier ,message_body=message_body from HelloWorldTargetQueue-it is a HelloWorldRequsetif message_type_name=N'HelloWorldRequest' beginsend on conversation conversionHandlemessage type HelloWorldRespone(N'Hello from '+servernameend conversation conversionHandleend-commitcommit trango7. 检验7.1 检查目标队列use ssbDemogo-check the target queueselect * from HelloWorldTargetQueue with (nolock -go-convert the message to nvarcharselect cast(message_body as nvarchar(maxfrom HelloWorldTargetQueuego7.2 检查消息接受情况-Che