上文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- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
一、启用传输安全
在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- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
二、启用对话安全
设置安全对话的详细用法，请参看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- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
三、创建路由
使用命令：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 - 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
四、创建远程服务绑定
使用命令：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); - 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
查看结果：
-- 在目标实例上执行 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.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
最终结果：
原文链接：http://www.cnblogs.com/downmoon/archive/2011/05/05/2037830.html
【编辑推荐】
