SQL Server 2008中远程Service Broker实现

上文SQL Server 2008中Service Broker基础应用（上） 、SQL Server 2008中 Service Broker基础应用（下）
演示了在同一个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 _disibledevent=> <br/> <strong>一、启用传输安全</strong> <br/> 在Service Broker的安全传输，是指两个SQL Server实例之间进行网络连接时，启用或限制它们之间的加密通信。传输安全是在SQL Server实例级别，因此这个示例演示如何创建在两个SQL Server实例的主数据库对象。您可以选择两种形式的运输安全：Windows身份验证或基于证书的安全认证。<br/> <textarea cols="50" rows="15" name="code" class="ruby">-- 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 _disibledevent=> <br/> <strong>二、启用对话安全</strong> <br/> <textarea cols="50" rows="15" name="code" class="ruby">/***********************************************************************/ -- 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 _disibledevent=>http://msdn.microsoft.com/en-us/library/ms186742.aspx</a> </strong> ）创建路由<br/> <textarea cols="50" rows="15" name="code" class="ruby">/************************创建路由 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 _disibledevent=>-- 在目标实例上执行 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
最终结果：

参考：
1、Service Broker 通信协议
http://msdn.microsoft.com/zh-cn/library/ms166061%28v=sql.100%29.aspx
邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助!  [email protected]  

延伸阅读