淺述遠程Service Broker的實現
作者：邀月
上文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
【編輯推薦】
責任編輯：艾婧
來源：
博客園
