浅述远程Service Broker的实现

数据库 SQL Server
上文SQL Server 2008中Service Broker基础应用(上)、SQL Server 2008中Service Broker基础应用(下)演示了在同一个SQL Server实例的不同数据库之间实现Service Broker,其实,更常见的是在不同的SQL Server实例之间进行通信,本文将通过实例演示如何进行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 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

最终结果:

原文链接:http://www.cnblogs.com/downmoon/archive/2011/05/05/2037830.html

【编辑推荐】

  1. Service Broker基础应用(下)
  2. Service Broker基础应用(上)
  3. 简述Service Broker事件通知功能
  4. 一个Excel导入SQL Server的例子
  5. 优化你的DiscuzNT,让它跑起来
责任编辑:艾婧 来源: 博客园
相关推荐

2010-07-06 17:05:22

PPPOE协议

2010-07-15 13:52:51

TELNET连接

2010-06-29 12:17:16

SIGTRAN协议

2010-06-24 15:02:43

FTP协议

2009-09-28 14:49:44

Hiberante映射

2010-08-02 16:08:24

ICMP协议设置

2010-09-06 14:10:27

PPP内核

2009-09-25 13:10:15

Hibernate性能

2010-08-05 17:06:58

RIP路由协议

2010-07-13 14:52:21

Linux SNMP

2010-07-12 16:58:34

LEACH协议

2010-09-07 14:50:20

CISCO PPP配置

2010-06-10 16:16:42

BGP路由协议

2010-09-08 16:01:57

SIP协议栈

2011-08-29 11:25:29

清空service bSQL Server

2010-08-04 16:55:16

NFS服务

2010-07-20 17:40:57

AIX telnet

2010-09-25 14:25:29

Linux DHCP服

2010-09-07 14:30:20

Linux PPPoE

2009-10-12 14:42:22

RHEL KVM
点赞
收藏

51CTO技术栈公众号