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

转载于:https://www.cnblogs.com/downmoon/archive/2011/05/05/2037830.html