以下是实现远程Service Broker的基本步骤：
一、启用传输安全
二、启用对话安全
三、创建路由
四、创建远程服务绑定
下图显示的是两个 SQL Server 实例间的 Service Broker 网络通信的高级视图。
准备工作：
View Code /************************************************************/
------远程Service Broker
------ [email protected]
-----源服务器实例,在本例中为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身份验证或基于证书的安全认证。
View Code -- 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 二、启用对话安全
View Code /***********************************************************************/
-- 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）创建路由
/************************创建路由 [email protected]****************/ -- 在源实例上执行 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）创建远程服务绑定
/*********************创建远程服务绑定 [email protected] ***********/ -- 在源实例上执行 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 = '
查看结果：
-- 在目标实例上执行 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
最新评论