同样看该案例时 先看微软官方资料
http://msdn.microsoft.com/zh-cn/library/ms166061%28v=sql.100%29.aspx
只有理解了相关的协议才能看懂该案例。
转载自：http://www.cnblogs.com/downmoon/archive/2011/05/05/2037830.html
演示了在同一个SQL Server实例的不同数据库之间实现Service Broker，其实，更常见的是在不同的SQL Server实例之间进行通信，本文将通过实例演示如何进行Service Broker远程通信。
以下是实现远程Service Broker的基本步骤：
一、启用传输安全
二、启用对话安全
三、创建路由
四、创建远程服务绑定
下图显示的是两个 SQL Server 实例间的 Service Broker 网络通信的高级视图。
准备工作：
/* ********************************************************** */ -- ----远程Service Broker -- ---- 3w@live.cn -- ---源服务器实例,在本例中为ap4\agronet09 USE master GO -- Enable Service Broker for the database ALTER DATABASE BookStore SET ENABLE_BROKER GO ALTER DATABASE BookStore SET 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 IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = ' BookDistribution ' ) CREATE DATABASE BookDistribution GO -- Enable Service Broker for the database ALTER DATABASE BookDistribution SET ENABLE_BROKER GO ALTER DATABASE BookDistribution SET 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 TO FILE = ' H:\SqlBackup\AP4MasterCert.cer ' GO -- 在目标实例上执行 Ap2\agronetserver BACKUP CERTIFICATE AP2MasterCert TO FILE = ' 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 CREATE USER SBUser FOR LOGIN SBLogin GO -- 在目标实例上执行 Ap2\agronetserver CREATE LOGIN SBLogin WITH PASSWORD = ' Used4TransSec ' GO CREATE USER SBUser FOR LOGIN SBLogin GO -- 在源实例上执行 Ap4\agronet09 GRANT CONNECT ON Endpoint::SB_AP4_Endpoint TO SBLogin GO -- 在目标实例上执行 Ap2\agronetserver GRANT CONNECT ON Endpoint::SB_AP2_Endpoint TO SBLogin GO -- --在源实例上执行 Ap4\agronet09 -- --需要从AP2上复制到AP4下H:\SqlBackup,邀月注 CREATE CERTIFICATE AP2MasterCert AUTHORIZATION SBUser FROM FILE = ' H:\SqlBackup\AP2MasterCert.cer ' GO -- -- 在目标实例上执行 Ap2\agronetserver -- --需要从AP4上复制到AP2下H:\SqlBackup，邀月注 CREATE CERTIFICATE AP4MasterCert AUTHORIZATION SBUser FROM FILE = ' H:\SqlBackup\AP4MasterCert.cer ' GO
二、启用对话安全
设置安全对话的详细用法，请参看MSDN:（http://msdn.microsoft.com/zh-cn/library/ms166036.aspx）。
/* ********************************************************************* */ -- 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 TO FILE = ' H:\SqlBackup\BookStoreCert.cer ' GO -- 在目标实例上执行 Ap2\agronetserver USE BookDistribution GO BACKUP CERTIFICATE BookDistributionCert TO FILE = ' H:\SqlBackup\BookDistributionCert.cer ' GO -- 在源实例上执行 Ap4\agronet09 USE BookStore GO CREATE USER BookDistributionUser WITHOUT LOGIN GO -- 在目标实例上执行 Ap2\agronetserver USE BookDistribution GO CREATE USER BookStoreUser WITHOUT LOGIN GO -- 在源实例上执行 Ap4\agronet09 -- --需要从AP2上复制到AP4下H:\SqlBackup,邀月注 USE BookStore GO CREATE CERTIFICATE BookDistributionCert AUTHORIZATION BookDistributionUser FROM FILE = ' H:\SqlBackup\BookDistributionCert.cer ' GO -- 在目标实例上执行 Ap2\agronetserver -- --需要从AP4上复制到AP2下H:\SqlBackup，邀月注 USE BookDistribution GO CREATE CERTIFICATE BookStoreCert AUTHORIZATION BookStoreUser FROM FILE = ' 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）创建路由
/* ***********************创建路由 3w@live.cn*************** */ -- 在源实例上执行 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）创建远程服务绑定
/* ********************创建远程服务绑定 3w@live.cn ********** */ -- 在源实例上执行 Ap4\agronet09 USE BookStore GO CREATE REMOTE SERVICE BINDING BookDistributionBinding TO SERVICE ' //SackConsulting/BookDistributionService ' WITH USER = BookDistributionUser GO -- 在目标实例上执行 Ap2\agronetserver USE BookDistribution GO CREATE REMOTE SERVICE BINDING BookStoreBinding TO SERVICE ' //SackConsulting/BookOrderService ' WITH USER = BookStoreUser GO -- 在源实例上执行 Ap4\agronet09 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="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_body as xml) message, queuing_order, conversation_handle, conversation_group_id FROM dbo.BookDistributionQueue
2951
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
