SqlServer2008 Service Broker点对点模式应用

Service Broker应用

1.简述Service Broker事件通知功能

事件通知是集成到Service Broker的功能，使用它可以在SQL Server实例中异步捕捉SQL事件，将事件消息路由到特定的队列中。只需最小的系统开销，就可以跟踪发生在SQL Server实例中的事件，比如用户登录、存储过程编译、权限修改、对象处理（例如对数据库、程序集、角色或表的Create/Alter/Drop事件）。

不像创建自己的Service Broker应用程序，因为可以控制发起方组件，使用事件通知后只需创建队列和Service Broker组件。用来捕捉和发送事件通知的发起方组件（消息类型和约定）已经内建在SQL Server中。

使用CREATE EVENT NOTIFICATION（http://msdn.microsoft.com/en-us/library/ms189453.aspx）

关于SQL Trace，相信很多人都用过Profiler,即SQL Server提供的查询跟踪器。关于SQL Trace及profiler（即事件跟踪器）的更多用法，请查看MSDN:http://msdn.microsoft.com/en-us/library/ms187929.aspx

[重要说明]：事件通知功能与SQL Trace相似，不同之处在于，它是异步的，并且对SQL Server实例整体性能影响最小。

下面是一个示例,演示了如何通过事件通知捕捉所有在SQL Server实例中执行的Create Login、Alter Login、和Drop Login命令。

/**************************downmoon [e-mail]***********************/

-- Capturing Login Commands
--创建示例数据库
IFEXISTS (SELECT nameFROM sys.databasesWHERE name ='EventTracking')
dropdatabase EventTracking
GO
CREATEDATABASE EventTracking
GO

USE EventTracking
GO
--创建队列
Create QUEUE SQLEventQueue
WITH STATUS=ON;
GO

--创建服务,并关联到内建的事件通知约定
CREATE SERVICE[//AP4/TrackLoginModificationService]
ON QUEUE SQLEventQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

--对系统目录视图sys.databases进行查询
select service_broker_guid
from sys.databases
WHERE name ='EventTracking'
/*
service_broker_guid
18FD2712-E551-4B6A-BC88-58E16D8D5BCD
*/

--在Server范围内创建事件通知来跟踪所有登录名的创建、修改和删除操作
----DROP EVENT NOTIFICATION EN_LoginEvents
----ON Server;
Create EVENT NOTIFICATION EN_LoginEvents
ON SERVER
FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
TO SERVICE '//AP4/TrackLoginModificationService',
'18FD2712-E551-4B6A-BC88-58E16D8D5BCD';
--测试新的事件通知，创建一个登录名
----DROP login TrishelleN
----go
Create LOGIN TrishelleNWITH PASSWORD ='AR!3i2ou4'
GO
--使用Select或Recieve(其中Recieve会删除队列中的事件消息)查询队列
SELECTCAST(message_bodyas xml) EventInfo
FROM dbo.SQLEventQueue
/*
<EVENT_INSTANCE>
<EventType>DROP_LOGIN</EventType>
<PostTime>2011-05-05T17:10:50.610</PostTime>
<SPID>52</SPID>
<ServerName>AP4\AGRONET09</ServerName>
<LoginName>AP4\Administrator</LoginName>
<ObjectName>TrishelleN</ObjectName>
<ObjectType>LOGIN</ObjectType>
<DefaultLanguage>us_english</DefaultLanguage>
<DefaultDatabase>master</DefaultDatabase>
<LoginType>SQL Login</LoginType>
<SID>ukcT55ooZECq0+bpBuvi1A==</SID>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>DROP login TrishelleN
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
*/

 

小结

本例演示了通过执行如下步骤来设置事件通知：

1、在已有数据库中创建新队列

2、创建绑定到新队列和内建事件通知约定的新服务

3、使用CREATE EVENT NOTIFICATION命令跟踪一个或多个事件或事件组

事件通知功能提供了跟踪SQL Server实例、数据库、或Service Broker应用程序级别的活动的低开销的方法。正如示例中所见，跟踪事件只需要极少量的代码。这个新功能对于IT安全或业务级别审计需求特别有用。例如，当捕获到登录名创建事件时，也会捕获到创建它的用户，以及登录名的类型（ＳＱＬ登录名）、默认数据库、语言和新登录名的安全标识符。

2.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
IFNOTEXISTS (SELECT name FROM sys.databases WHERE name ='BookStore')
CREATEDATABASE BookStore
GO
IFNOTEXISTS (SELECT name FROM sys.databases WHERE name ='BookDistribution')
CREATEDATABASE BookDistribution
GO
ALTERDATABASE BookStore SET ENABLE_BROKER
GO
ALTERDATABASE BookStore SET TRUSTWORTHY ON
GO
ALTERDATABASE BookDistribution SET ENABLE_BROKER
GO
ALTERDATABASE 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_Handleruniqueidentifier
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
--创建一个表存放接收到的订单信息
CREATETABLE dbo.BookOrderReceived
(BookOrderReceivedID intIDENTITY (1,1) NOTNULL,
conversation_handle uniqueidentifierNOTNULL,
conversation_group_id uniqueidentifierNOTNULL,
message_body xml NOTNULL)
GO

-- 声明变量
DECLARE@Conv_Handleruniqueidentifier
DECLARE@Conv_Groupuniqueidentifier
DECLARE@OrderMsg xml
DECLARE@TextResponseMsgvarchar(8000)
DECLARE@ResponseMsg xml
DECLARE@OrderIDint;

--从队列中获取消息，将接收值赋于局部变量
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(@OrderIDasvarchar(10)) +
'"/>';
SELECT@ResponseMsg=CAST(@TextResponseMsgas xml);

-- 使用既有的会话句柄，发送响应消息到发起方
SEND ON CONVERSATION @Conv_Handler
MESSAGE TYPE [//SackConsulting/BookOrderReceived]

（十）、结束会话

-- Ending a Conversation

USE BookStore
GO
-- 创建订单确认表
CREATETABLE dbo.BookOrderConfirmation
(BookOrderConfirmationID intIDENTITY (1,1) NOTNULL,
conversation_handle uniqueidentifierNOTNULL,
DateReceived datetimeNOTNULLDEFAULTGETDATE(),
message_body xml NOTNULL)

DECLARE@Conv_Handleruniqueidentifier
DECLARE@Conv_Groupuniqueidentifier
DECLARE@OrderMsg xml
DECLARE@TextResponseMsgvarchar(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_Handleruniqueidentifier
DECLARE@Conv_Groupuniqueidentifier
DECLARE@OrderMsg xml
DECLARE@message_type_namenvarchar(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应用程序的异步特性可以防止因应用程序挂起而导致业务系统产生瓶颈。

3. 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开关。

--设置会话优先级

--启用会话优先级选项
ALTERDATABASE BookStore
SET HONOR_BROKER_PRIORITYON

--启用会话优先级选项
ALTERDATABASE BOOKDistribution
SET HONOR_BROKER_PRIORITYON

--查看设置结果
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目录视图，查询优先级：

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）目录视图关联起来。

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）选项，也可以指定可以激活并处理在同一队列中传入的消息的、同时执行的相同服务程序的数量。

　　继续上文的示例：

-- Creating the Bookstore Stored Procedure

USE BookDistribution
GO
CREATEPROCEDURE dbo.usp_SB_ReceiveOrders
AS
DECLARE@Conv_Handleruniqueidentifier
DECLARE@Conv_Groupuniqueidentifier
DECLARE@OrderMsg xml
DECLARE@TextResponseMsgvarchar(8000)
DECLARE@ResponseMsg xml
DECLARE@Message_Type_Namenvarchar(256);
DECLARE@OrderIDint;

-- XACT_ABORT automatically rolls back the transaction when a runtime error occurs
SET XACT_ABORT ON

BEGINTRAN;

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(@OrderIDasvarchar(10))+
'"/>';
SELECT@ResponseMsg=CAST(@TextResponseMsgas 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
COMMITTRAN
GO

　　解析：该存储过程包含处理//SackConsulting/SendBookOrder和http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog消息类型的逻辑。如果发送发后者，特定会话的句柄的特定会话会结束。如果接收到图书订单消息类型，它的消息将插入到表中，并且返回订单确认信息。

　　可以使用Alter Queue命令修改既有的队列。这个命令使用与Create Queue相同的选项，它允许改变队列的状态与保持期、待激活的存储过程、队列读取存储过程实例的最大数量以及过程的安全模式契约。

　　Alter Queue包括一个额外的参数Drop，它用来删除队列的所有存储过程激活设置。

　　使用Alter Queue命令将存储过程绑定到既有的队列：

----使用Alter Queue命令将存储过程绑定到既有的队列
ALTER QUEUE dbo.BookDistributionQueue
WITH ACTIVATION (STATUS=ON,
PROCEDURE_NAME = dbo.usp_SB_ReceiveOrders,
MAX_QUEUE_READERS =2,--独立处理队列中不同消息的同一存储过程同时执行的最大数量
EXECUTEAS SELF)--即存储过程将以与执行Alter Queue命令的主体的相同的权限来执行

　　为了测试BookStore数据库的新服务程序，开始一个会话并设置新顺序：

Use BookStore
GO

DECLARE@Conv_Handleruniqueidentifier
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：

SELECT conversation_handle,CAST(message_bodyas xml) message
FROM dbo.BookStoreQueue
/*
conversation_handle message
A7B7FA73-5B5F-E011-8B4E-001C23FA56DD <orderreceived id="3439" />
*/

4. SQL Server 2008中远程Service Broker实现

演示了在同一个SQL Server实例的不同数据库之间实现Service Broker，其实，更常见的是在不同的SQL Server实例之间进行通信，本文将通过实例演示如何进行Service Broker远程通信。

　　以下是实现远程Service Broker的基本步骤：

　　一、启用传输安全

　　二、启用对话安全

　　三、创建路由

　　四、创建远程服务绑定

　　下图显示的是两个 SQL Server实例间的 Service Broker 网络通信的高级视图。

　　准备工作：

/************************************************************/
------远程Service Broker
------ [e-mail]
-----源服务器实例,在本例中为ap4\agronet09
USE master
GO
-- Enable Service Broker for the database
ALTERDATABASE BookStoreSET ENABLE_BROKER
GO
ALTERDATABASE BookStoreSET TRUSTWORTHY ON
GO

USE BookStore
GO
-- Create the messages
CREATE MESSAGE TYPE[//SackConsulting/SendBookOrder]
VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE[//SackConsulting/BookOrderReceived]
VALIDATION = WELL_FORMED_XML
GO

-- Create the contract
CREATE CONTRACT
[//SackConsulting/BookOrderContract]
( [//SackConsulting/SendBookOrder]
SENT BY INITIATOR,
[//SackConsulting/BookOrderReceived]
SENT BY TARGET
)
GO

-- Create the queue
CREATE QUEUE BookStoreQueue
WITH STATUS=ON
GO

-- Create the service
CREATE SERVICE [//SackConsulting/BookOrderService]
ON QUEUE dbo.BookStoreQueue
([//SackConsulting/BookOrderContract])
GO

-----目标服务器实例,在本例中为ap2\agronetserver
USE master
GO
IFNOTEXISTS (SELECT name
FROM sys.databases
WHERE name ='BookDistribution')
CREATEDATABASE BookDistribution
GO

-- Enable Service Broker for the database
ALTERDATABASE BookDistributionSET ENABLE_BROKER
GO
ALTERDATABASE BookDistributionSET TRUSTWORTHY ON
GO

USE BookDistribution
GO
-- Create the messages
CREATE MESSAGE TYPE[//SackConsulting/SendBookOrder]
VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE[//SackConsulting/BookOrderReceived]
VALIDATION = WELL_FORMED_XML
GO

-- Create the contract
CREATE CONTRACT
[//SackConsulting/BookOrderContract]
( [//SackConsulting/SendBookOrder]
SENT BY INITIATOR,
[//SackConsulting/BookOrderReceived]
SENT BY TARGET
)
GO

-- Create the queue
CREATE QUEUE BookDistributionQueue
WITH STATUS=ON
GO

-- Create the service
CREATE SERVICE [//SackConsulting/BookDistributionService]
ON QUEUE dbo.BookDistributionQueue
([//SackConsulting/BookOrderContract])
GO

一、启用传输安全

　　在Service Broker的安全传输，是指两个SQL Server实例之间进行网络连接时，启用或限制它们之间的加密通信。传输安全是在SQL Server实例级别，因此这个示例演示如何创建在两个SQL Server实例的主数据库对象。您可以选择两种形式的运输安全：Windows身份验证或基于证书的安全认证。

-- Enabling Transport Security

-- 在源实例上执行 Ap4\agronet09
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='1294934A!'

-- 在目标实例上执行 Ap2\agronetserver
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='1294934B!'

-- 在源实例上执行 Ap4\agronet09
CREATE CERTIFICATE AP4MasterCert
WITH SUBJECT ='Ap4 Transport Security SB',
EXPIRY_DATE ='5/4/2012'
GO

-- 在目标实例上执行 Ap2\agronetserver
CREATE CERTIFICATE AP2MasterCert
WITH SUBJECT ='AP2 Transport Security SB',
EXPIRY_DATE ='5/4/2012'
GO

-- 在源实例上执行 Ap4\agronet09
BACKUP CERTIFICATE AP4MasterCert
TOFILE='H:\SqlBackup\AP4MasterCert.cer'
GO

-- 在目标实例上执行 Ap2\agronetserver
BACKUP CERTIFICATE AP2MasterCert
TOFILE='H:\SqlBackup\AP2MasterCert.cer'
GO

-- 在源实例上执行 Ap4\agronet09
CREATE ENDPOINT SB_AP4_Endpoint
STATE = STARTED
AS TCP
(LISTENER_PORT =4020)
FOR SERVICE_BROKER
(AUTHENTICATION = CERTIFICATE AP4MasterCert,
ENCRYPTION = REQUIRED)
GO

-- 在目标实例上执行 Ap2\agronetserver
CREATE ENDPOINT SB_AP2_Endpoint
STATE = STARTED
AS TCP
(LISTENER_PORT =4021)
FOR SERVICE_BROKER
(AUTHENTICATION = CERTIFICATE AP2MasterCert,
ENCRYPTION = REQUIRED)
GO

-- 在源实例上执行 Ap4\agronet09
CREATE LOGIN SBLogin
WITH PASSWORD ='Used4TransSec'
GO
CREATEUSER SBUser
FOR LOGIN SBLogin
GO

-- 在目标实例上执行 Ap2\agronetserver
CREATE LOGIN SBLogin
WITH PASSWORD ='Used4TransSec'
GO
CREATEUSER SBUser
FOR LOGIN SBLogin
GO

-- 在源实例上执行 Ap4\agronet09
GRANT CONNECT ON Endpoint::SB_AP4_EndpointTO SBLogin
GO

-- 在目标实例上执行 Ap2\agronetserver
GRANT CONNECT ON Endpoint::SB_AP2_EndpointTO SBLogin
GO

----在源实例上执行 Ap4\agronet09
----需要从AP2上复制到AP4下H:\SqlBackup,邀月注
CREATE CERTIFICATE AP2MasterCert
AUTHORIZATION SBUser
FROMFILE='H:\SqlBackup\AP2MasterCert.cer'
GO

---- 在目标实例上执行 Ap2\agronetserver
----需要从AP4上复制到AP2下H:\SqlBackup，邀月注
CREATE CERTIFICATE AP4MasterCert
AUTHORIZATION SBUser
FROMFILE='H:\SqlBackup\AP4MasterCert.cer'
GO

　　二、启用对话安全

/***********************************************************************/
-- Enabling Dialog Security

-- 在源实例上执行 Ap4\agronet09
USE BookStore
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='1294934A!'
GO

CREATE CERTIFICATE BookStoreCert
WITH SUBJECT ='BookStore SB cert',
EXPIRY_DATE ='5/4/2012'
GO

-- 在目标实例上执行 Ap2\agronetserver
USE BookDistribution
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='1294934B!'
GO
CREATE CERTIFICATE BookDistributionCert
WITH SUBJECT ='BookDistributionCert SB cert',
EXPIRY_DATE ='5/4/2012'
GO

-- 在源实例上执行 Ap4\agronet09
USE BookStore
GO
BACKUP CERTIFICATE BookStoreCert
TOFILE='H:\SqlBackup\BookStoreCert.cer'
GO

-- 在目标实例上执行 Ap2\agronetserver
USE BookDistribution
GO
BACKUP CERTIFICATE BookDistributionCert
TOFILE='H:\SqlBackup\BookDistributionCert.cer'
GO

-- 在源实例上执行 Ap4\agronet09
USE BookStore
GO
CREATEUSER BookDistributionUser
WITHOUT LOGIN
GO

-- 在目标实例上执行 Ap2\agronetserver
USE BookDistribution
GO
CREATEUSER BookStoreUser
WITHOUT LOGIN
GO

-- 在源实例上执行 Ap4\agronet09
----需要从AP2上复制到AP4下H:\SqlBackup,邀月注
USE BookStore
GO
CREATE CERTIFICATE BookDistributionCert
AUTHORIZATION BookDistributionUser
FROMFILE='H:\SqlBackup\BookDistributionCert.cer'
GO

-- 在目标实例上执行 Ap2\agronetserver
----需要从AP4上复制到AP2下H:\SqlBackup，邀月注
USE BookDistribution
GO
CREATE CERTIFICATE BookStoreCert
AUTHORIZATION BookStoreUser
FROMFILE='H:\SqlBackup\BookStoreCert.cer'
GO

-- 在源实例上执行 Ap4\agronet09
USE BookStore
GO
GRANT SEND ON
SERVICE::[//SackConsulting/BookOrderService]TO BookDistributionUser
GO

-- 在目标实例上执行 Ap2\agronetserver
USE BookDistribution
GO
GRANT SEND ON
SERVICE::[//SackConsulting/BookDistributionService]
TO BookStoreUser

三、创建路由

　　使用命令：CREATE ROUTE（http://msdn.microsoft.com/en-us/library/ms186742.aspx）创建路由

/************************创建路由 [e-mail]****************/
-- 在源实例上执行 Ap4\agronet09
USE BookStore
GO
CREATE ROUTE Route_BookDistribution
WITH SERVICE_NAME='//SackConsulting/BookDistributionService',
ADDRESS ='TCP://192.168.1.99:4021'
GO

-- 在目标实例上执行 Ap2\agronetserver
USE BookDistribution
GO
----SERVICE BROKER 609
CREATE ROUTE Route_BookStore
WITH SERVICE_NAME='//SackConsulting/BookOrderService',
ADDRESS ='TCP://192.168.1.6:4020'
GO

　　四、创建远程服务绑定

　　使用命令：CREATE REMOTE SERVICE BINDING（http://msdn.microsoft.com/en-us/library/ms178024.aspx）创建远程服务绑定

/*********************创建远程服务绑定 [e-mail] ***********/
-- 在源实例上执行 Ap4\agronet09
USE BookStore
GO
CREATE REMOTE SERVICE BINDING BookDistributionBinding
TO SERVICE '//SackConsulting/BookDistributionService'
WITHUSER= BookDistributionUser
GO

-- 在目标实例上执行 Ap2\agronetserver
USE BookDistribution
GO
CREATE REMOTE SERVICE BINDING BookStoreBinding
TO SERVICE '//SackConsulting/BookOrderService'
WITHUSER= BookStoreUser
GO

-- 在源实例上执行 Ap4\agronet09
Use BookStore
GO
DECLARE@Conv_Handleruniqueidentifier
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="9/25/2008">
<LineItem ItemNumber="22" ISBN="1-59059-592-0" Quantity="10" />
</order>';

SEND ON CONVERSATION@Conv_Handler
MESSAGE TYPE [//SackConsulting/SendBookOrder]
(@OrderMsg);

　　查看结果：

-- 在目标实例上执行 Ap2\agronetserver

USE BookDistribution
GO
SELECT message_type_name,CAST(message_bodyas xml) message,
queuing_order, conversation_handle, conversation_group_id
FROM dbo.BookDistributionQueue

　　最终结果：