SQL Server 2008中远程Service Broker实现

SQL Server 2008中SQL应用系列--目录索引

 

上文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 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

最终结果：

参考：

1、Service Broker 通信协议  http://msdn.microsoft.com/zh-cn/library/ms166061%28v=sql.100%29.aspx

 

下文将介绍SQL Server 2008中新增的很酷的功能：Service broker事件通知。

 

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助!  3w@live.cn