建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
淺述遠程ServiceBroker的實現(xiàn)
上文SQL Server 2008中Service Broker基礎(chǔ)應用(上)、SQL Server 2008中Service Broker基礎(chǔ)應用(下)演示了在同一個SQL Server實例的不同數(shù)據(jù)庫之間實現(xiàn)Service Broker，其實，更常見的是在不同的SQL Server實例之間進行通信，本文將通過實例演示如何進行Service Broker遠程通信。
創(chuàng)新互聯(lián)堅信：善待客戶，將會成為終身客戶。我們能堅持多年，是因為我們一直可值得信賴。我們從不忽悠初訪客戶，我們用心做好本職工作，不忘初心，方得始終。10余年網(wǎng)站建設(shè)經(jīng)驗創(chuàng)新互聯(lián)是成都老牌網(wǎng)站營銷服務(wù)商,為您提供成都網(wǎng)站設(shè)計、網(wǎng)站建設(shè)、網(wǎng)站設(shè)計、H5開發(fā)、網(wǎng)站制作、品牌網(wǎng)站制作、微信小程序開發(fā)服務(wù),給眾多知名企業(yè)提供過好品質(zhì)的建站服務(wù)。
以下是實現(xiàn)遠程Service Broker的基本步驟：
一、啟用傳輸安全
二、啟用對話安全
三、創(chuàng)建路由
四、創(chuàng)建遠程服務(wù)綁定
下圖顯示的是兩個 SQL Server 實例間的 Service Broker 網(wǎng)絡(luò)通信的高級視圖。
準備工作：
/* ********************************************************** */
-- ----遠程Service Broker
-- ---- 3w@live.cn
-- ---源服務(wù)器實例,在本例中為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
-- ---目標服務(wù)器實例,在本例中為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實例之間進行網(wǎng)絡(luò)連接時，啟用或限制它們之間的加密通信。傳輸安全是在SQL Server實例級別，因此這個示例演示如何創(chuàng)建在兩個SQL Server實例的主數(shù)據(jù)庫對象。您可以選擇兩種形式的運輸安全：Windows身份驗證或基于證書的安全認證。
-- Enabling Transport Security
-- 在源實例上執(zhí)行 Ap4\agronet09
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' 1294934A! '
-- 在目標實例上執(zhí)行 Ap2\agronetserver
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' 1294934B! '
-- 在源實例上執(zhí)行 Ap4\agronet09
CREATE CERTIFICATE AP4MasterCert
WITH SUBJECT = ' Ap4 Transport Security SB ' ,
EXPIRY_DATE = ' 5/4/2012 '
GO
-- 在目標實例上執(zhí)行 Ap2\agronetserver
CREATE CERTIFICATE AP2MasterCert
WITH SUBJECT = ' AP2 Transport Security SB ' ,
EXPIRY_DATE = ' 5/4/2012 '
GO
-- 在源實例上執(zhí)行 Ap4\agronet09
BACKUP CERTIFICATE AP4MasterCert
TO FILE = ' H:\SqlBackup\AP4MasterCert.cer '
GO
-- 在目標實例上執(zhí)行 Ap2\agronetserver
BACKUP CERTIFICATE AP2MasterCert
TO FILE = ' H:\SqlBackup\AP2MasterCert.cer '
GO
-- 在源實例上執(zhí)行 Ap4\agronet09
CREATE ENDPOINT SB_AP4_Endpoint
STATE = STARTED
AS TCP
(LISTENER_PORT = 4020 )
FOR SERVICE_BROKER
(AUTHENTICATION = CERTIFICATE AP4MasterCert,
ENCRYPTION = REQUIRED)
GO
-- 在目標實例上執(zhí)行 Ap2\agronetserver
CREATE ENDPOINT SB_AP2_Endpoint
STATE = STARTED
AS TCP
(LISTENER_PORT = 4021 )
FOR SERVICE_BROKER
(AUTHENTICATION = CERTIFICATE AP2MasterCert,
ENCRYPTION = REQUIRED)
GO
-- 在源實例上執(zhí)行 Ap4\agronet09
CREATE LOGIN SBLogin
WITH PASSWORD = ' Used4TransSec '
GO
CREATE USER SBUser
FOR LOGIN SBLogin
GO
-- 在目標實例上執(zhí)行 Ap2\agronetserver
CREATE LOGIN SBLogin
WITH PASSWORD = ' Used4TransSec '
GO
CREATE USER SBUser
FOR LOGIN SBLogin
GO
-- 在源實例上執(zhí)行 Ap4\agronet09
GRANT CONNECT ON Endpoint::SB_AP4_Endpoint TO SBLogin
GO
-- 在目標實例上執(zhí)行 Ap2\agronetserver
GRANT CONNECT ON Endpoint::SB_AP2_Endpoint TO SBLogin
GO
-- --在源實例上執(zhí)行 Ap4\agronet09
-- --需要從AP2上復制到AP4下H:\SqlBackup,邀月注
CREATE CERTIFICATE AP2MasterCert
AUTHORIZATION SBUser
FROM FILE = ' H:\SqlBackup\AP2MasterCert.cer '
GO
-- -- 在目標實例上執(zhí)行 Ap2\agronetserver
-- --需要從AP4上復制到AP2下H:\SqlBackup，邀月注
CREATE CERTIFICATE AP4MasterCert
AUTHORIZATION SBUser
FROM FILE = ' H:\SqlBackup\AP4MasterCert.cer '
GO
二、啟用對話安全
設(shè)置安全對話的詳細用法，請參看MSDN:(http://msdn.microsoft.com/zh-cn/library/ms166036.aspx)。
/* ********************************************************************* */
-- Enabling Dialog Security
-- 在源實例上執(zhí)行 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
-- 在目標實例上執(zhí)行 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
-- 在源實例上執(zhí)行 Ap4\agronet09
USE BookStore
GO
BACKUP CERTIFICATE BookStoreCert
TO FILE = ' H:\SqlBackup\BookStoreCert.cer '
GO
-- 在目標實例上執(zhí)行 Ap2\agronetserver
USE BookDistribution
GO
BACKUP CERTIFICATE BookDistributionCert
TO FILE = ' H:\SqlBackup\BookDistributionCert.cer '
GO
-- 在源實例上執(zhí)行 Ap4\agronet09
USE BookStore
GO
CREATE USER BookDistributionUser
WITHOUT LOGIN
GO
-- 在目標實例上執(zhí)行 Ap2\agronetserver
USE BookDistribution
GO
CREATE USER BookStoreUser
WITHOUT LOGIN
GO
-- 在源實例上執(zhí)行 Ap4\agronet09
-- --需要從AP2上復制到AP4下H:\SqlBackup,邀月注
USE BookStore
GO
CREATE CERTIFICATE BookDistributionCert
AUTHORIZATION BookDistributionUser
FROM FILE = ' H:\SqlBackup\BookDistributionCert.cer '
GO
-- 在目標實例上執(zhí)行 Ap2\agronetserver
-- --需要從AP4上復制到AP2下H:\SqlBackup，邀月注
USE BookDistribution
GO
CREATE CERTIFICATE BookStoreCert
AUTHORIZATION BookStoreUser
FROM FILE = ' H:\SqlBackup\BookStoreCert.cer '
GO
-- 在源實例上執(zhí)行 Ap4\agronet09
USE BookStore
GO
GRANT SEND ON
SERVICE:: [ //SackConsulting/BookOrderService ] TO BookDistributionUser
GO
-- 在目標實例上執(zhí)行 Ap2\agronetserver
USE BookDistribution
GO
GRANT SEND ON
SERVICE:: [ //SackConsulting/BookDistributionService ]
TO BookStoreUser
三、創(chuàng)建路由
使用命令：CREATE ROUTE(http://msdn.microsoft.com/en-us/library/ms186742.aspx)創(chuàng)建路由
/* ***********************創(chuàng)建路由 3w@live.cn*************** */
-- 在源實例上執(zhí)行 Ap4\agronet09
USE BookStore
GO
CREATE ROUTE Route_BookDistribution
WITH SERVICE_NAME = ' //SackConsulting/BookDistributionService ' ,
ADDRESS = ' TCP://192.168.1.99:4021 '
GO
-- 在目標實例上執(zhí)行 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
四、創(chuàng)建遠程服務(wù)綁定
使用命令：CREATE REMOTE SERVICE BINDING(http://msdn.microsoft.com/en-us/library/ms178024.aspx)創(chuàng)建遠程服務(wù)綁定
/* ********************創(chuàng)建遠程服務(wù)綁定 3w@live.cn ********** */
-- 在源實例上執(zhí)行 Ap4\agronet09
USE BookStore
GO
CREATE REMOTE SERVICE BINDING BookDistributionBinding
TO SERVICE ' //SackConsulting/BookDistributionService '
WITH USER = BookDistributionUser
GO
-- 在目標實例上執(zhí)行 Ap2\agronetserver
USE BookDistribution
GO
CREATE REMOTE SERVICE BINDING BookStoreBinding
TO SERVICE ' //SackConsulting/BookOrderService '
WITH USER = BookStoreUser
GO
-- 在源實例上執(zhí)行 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 =
'' ;
SEND ON CONVERSATION @Conv_Handler
MESSAGE TYPE [ //SackConsulting/SendBookOrder ]
( @OrderMsg );
查看結(jié)果：
--
在目標實例上執(zhí)行 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
最終結(jié)果：
分享名稱：淺述遠程ServiceBroker的實現(xiàn)
本文網(wǎng)址：http://www.dlmjj.cn/article/cdgggjd.html
