SQL Server 2008中远程Service Broker实现

同样看该案例时 先看微软官方资料

http://msdn.microsoft.com/zh-cn/library/ms166061%28v=sql.100%29.aspx 

只有理解了相关的协议才能看懂该案例。

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

演示了在同一个SQL Server实例的不同数据库之间实现Service Broker，其实，更常见的是在不同的SQL Server实例之间进行通信，本文将通过实例演示如何进行Service Broker远程通信。

以下是实现远程Service Broker的基本步骤：

一、启用传输安全

二、启用对话安全

三、创建路由

四、创建远程服务绑定

下图显示的是两个 SQL Server 实例间的 Service Broker 网络通信的高级视图。

 

 

准备工作：

 

/************************************************************/------远程Service Broker------ [e-mail]-----源服务器实例,在本例中为ap4\agronet09USE masterGO-- Enable Service Broker for the databaseALTER DATABASE BookStore SET ENABLE_BROKERGOALTER DATABASE BookStore SET TRUSTWORTHY ONGOUSE BookStoreGO-- Create the messagesCREATE MESSAGE TYPE [//SackConsulting/SendBookOrder]VALIDATION = WELL_FORMED_XMLGOCREATE MESSAGE TYPE [//SackConsulting/BookOrderReceived]VALIDATION = WELL_FORMED_XMLGO-- Create the contractCREATE CONTRACT[//SackConsulting/BookOrderContract]( [//SackConsulting/SendBookOrder]SENT BY INITIATOR,[//SackConsulting/BookOrderReceived]SENT BY TARGET)GO-- Create the queueCREATE QUEUE BookStoreQueueWITH STATUS=ONGO-- Create the serviceCREATE SERVICE [//SackConsulting/BookOrderService]ON QUEUE dbo.BookStoreQueue([//SackConsulting/BookOrderContract])GO-----目标服务器实例,在本例中为ap2\agronetserverUSE masterGOIF NOT EXISTS (SELECT nameFROM sys.databasesWHERE name = 'BookDistribution')CREATE DATABASE BookDistributionGO-- Enable Service Broker for the databaseALTER DATABASE BookDistribution SET ENABLE_BROKERGOALTER DATABASE BookDistribution SET TRUSTWORTHY ONGOUSE BookDistributionGO-- Create the messagesCREATE MESSAGE TYPE [//SackConsulting/SendBookOrder]VALIDATION = WELL_FORMED_XMLGOCREATE MESSAGE TYPE [//SackConsulting/BookOrderReceived]VALIDATION = WELL_FORMED_XMLGO-- Create the contractCREATE CONTRACT[//SackConsulting/BookOrderContract]( [//SackConsulting/SendBookOrder]SENT BY INITIATOR,[//SackConsulting/BookOrderReceived]SENT BY TARGET)GO-- Create the queueCREATE QUEUE BookDistributionQueueWITH STATUS=ONGO-- Create the serviceCREATE SERVICE [//SackConsulting/BookDistributionService]ON QUEUE dbo.BookDistributionQueue([//SackConsulting/BookOrderContract])GO

 

一、启用传输安全

在Service Broker的安全传输，是指两个SQL Server实例之间进行网络连接时，启用或限制它们之间的加密通信。传输安全是在SQL Server实例级别，因此这个示例演示如何创建在两个SQL Server实例的主数据库对象。您可以选择两种形式的运输安全：Windows身份验证或基于证书的安全认证。

-- Enabling Transport Security-- 在源实例上执行 Ap4\agronet09USE masterGOCREATE MASTER KEY ENCRYPTION BY PASSWORD = '1294934A!'-- 在目标实例上执行 Ap2\agronetserverUSE masterGOCREATE MASTER KEY ENCRYPTION BY PASSWORD = '1294934B!'-- 在源实例上执行 Ap4\agronet09CREATE CERTIFICATE AP4MasterCertWITH SUBJECT = 'Ap4 Transport Security SB',EXPIRY_DATE = '5/4/2012'GO-- 在目标实例上执行 Ap2\agronetserverCREATE CERTIFICATE AP2MasterCertWITH SUBJECT = 'AP2 Transport Security SB',EXPIRY_DATE = '5/4/2012'GO-- 在源实例上执行 Ap4\agronet09BACKUP CERTIFICATE AP4MasterCertTO FILE = 'H:\SqlBackup\AP4MasterCert.cer'GO-- 在目标实例上执行 Ap2\agronetserverBACKUP CERTIFICATE AP2MasterCertTO FILE = 'H:\SqlBackup\AP2MasterCert.cer'GO-- 在源实例上执行 Ap4\agronet09CREATE ENDPOINT SB_AP4_EndpointSTATE = STARTEDAS TCP(LISTENER_PORT = 4020)FOR SERVICE_BROKER(AUTHENTICATION = CERTIFICATE AP4MasterCert,ENCRYPTION = REQUIRED)GO-- 在目标实例上执行 Ap2\agronetserverCREATE ENDPOINT SB_AP2_EndpointSTATE = STARTEDAS TCP(LISTENER_PORT = 4021)FOR SERVICE_BROKER(AUTHENTICATION = CERTIFICATE AP2MasterCert,ENCRYPTION = REQUIRED)GO-- 在源实例上执行 Ap4\agronet09CREATE LOGIN SBLoginWITH PASSWORD = 'Used4TransSec'GOCREATE USER SBUserFOR LOGIN SBLoginGO-- 在目标实例上执行 Ap2\agronetserverCREATE LOGIN SBLoginWITH PASSWORD = 'Used4TransSec'GOCREATE USER SBUserFOR LOGIN SBLoginGO-- 在源实例上执行 Ap4\agronet09GRANT CONNECT ON Endpoint::SB_AP4_Endpoint TO SBLoginGO-- 在目标实例上执行 Ap2\agronetserverGRANT CONNECT ON Endpoint::SB_AP2_Endpoint TO SBLoginGO----在源实例上执行 Ap4\agronet09----需要从AP2上复制到AP4下H:\SqlBackup,邀月注CREATE CERTIFICATE AP2MasterCertAUTHORIZATION SBUserFROM FILE = 'H:\SqlBackup\AP2MasterCert.cer'GO---- 在目标实例上执行 Ap2\agronetserver----需要从AP4上复制到AP2下H:\SqlBackup，邀月注CREATE CERTIFICATE AP4MasterCertAUTHORIZATION SBUserFROM FILE = 'H:\SqlBackup\AP4MasterCert.cer'GO

二、启用对话安全

设置安全对话的详细用法，请参看MSDN:（http://msdn.microsoft.com/zh-cn/library/ms166036.aspx）。

/***********************************************************************/-- Enabling Dialog Security-- 在源实例上执行 Ap4\agronet09USE BookStoreGOCREATE MASTER KEY ENCRYPTION BY PASSWORD = '1294934A!'GOCREATE CERTIFICATE BookStoreCertWITH SUBJECT = 'BookStore SB cert',EXPIRY_DATE = '5/4/2012'GO-- 在目标实例上执行 Ap2\agronetserverUSE BookDistributionGOCREATE MASTER KEY ENCRYPTION BY PASSWORD = '1294934B!'GOCREATE CERTIFICATE BookDistributionCertWITH SUBJECT = 'BookDistributionCert SB cert',EXPIRY_DATE = '5/4/2012'GO-- 在源实例上执行 Ap4\agronet09USE BookStoreGOBACKUP CERTIFICATE BookStoreCertTO FILE = 'H:\SqlBackup\BookStoreCert.cer'GO-- 在目标实例上执行 Ap2\agronetserverUSE BookDistributionGOBACKUP CERTIFICATE BookDistributionCertTO FILE = 'H:\SqlBackup\BookDistributionCert.cer'GO-- 在源实例上执行 Ap4\agronet09USE BookStoreGOCREATE USER BookDistributionUserWITHOUT LOGINGO-- 在目标实例上执行 Ap2\agronetserverUSE BookDistributionGOCREATE USER BookStoreUserWITHOUT LOGINGO-- 在源实例上执行 Ap4\agronet09----需要从AP2上复制到AP4下H:\SqlBackup,邀月注USE BookStoreGOCREATE CERTIFICATE BookDistributionCertAUTHORIZATION BookDistributionUserFROM FILE = 'H:\SqlBackup\BookDistributionCert.cer'GO-- 在目标实例上执行 Ap2\agronetserver----需要从AP4上复制到AP2下H:\SqlBackup，邀月注USE BookDistributionGOCREATE CERTIFICATE BookStoreCertAUTHORIZATION BookStoreUserFROM FILE = 'H:\SqlBackup\BookStoreCert.cer'GO-- 在源实例上执行 Ap4\agronet09USE BookStoreGOGRANT SEND ONSERVICE::[//SackConsulting/BookOrderService] TO BookDistributionUserGO-- 在目标实例上执行 Ap2\agronetserverUSE BookDistributionGOGRANT SEND ONSERVICE::[//SackConsulting/BookDistributionService]TO BookStoreUser

 

三、创建路由

使用命令：CREATE ROUTE（http://msdn.microsoft.com/en-us/library/ms186742.aspx）创建路由

/************************创建路由 [e-mail]****************/-- 在源实例上执行 Ap4\agronet09USE BookStoreGOCREATE ROUTE Route_BookDistributionWITH SERVICE_NAME = '//SackConsulting/BookDistributionService',ADDRESS = 'TCP://192.168.1.99:4021'GO-- 在目标实例上执行 Ap2\agronetserverUSE BookDistributionGO----SERVICE BROKER 609CREATE ROUTE Route_BookStoreWITH 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）创建远程服务绑定

/*********************创建远程服务绑定 [e-mail] ***********/-- 在源实例上执行 Ap4\agronet09USE BookStoreGOCREATE REMOTE SERVICE BINDING BookDistributionBindingTO SERVICE '//SackConsulting/BookDistributionService'WITH USER = BookDistributionUserGO-- 在目标实例上执行 Ap2\agronetserverUSE BookDistributionGOCREATE REMOTE SERVICE BINDING BookStoreBindingTO SERVICE '//SackConsulting/BookOrderService'WITH USER = BookStoreUserGO-- 在源实例上执行 Ap4\agronet09Use BookStoreGODECLARE @Conv_Handler uniqueidentifierDECLARE @OrderMsg xml;BEGIN DIALOG CONVERSATION @Conv_HandlerFROM 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_HandlerMESSAGE TYPE [//SackConsulting/SendBookOrder](@OrderMsg); 

查看结果：

-- 在目标实例上执行 Ap2\agronetserverUSE BookDistributionGOSELECT message_type_name, CAST(message_body as xml) message,queuing_order, conversation_handle, conversation_group_idFROM dbo.BookDistributionQueue