.Net学习爱好者的温馨家园

.Net互动平台

导航

转发：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事件通知。

转发自：http://www.cnblogs.com/downmoon/archive/2011/05/05/2037830.html

 

posted on 2011-06-01 10:49  Xt Idt  阅读(270)  评论(0)  编辑  收藏  举报