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 ------ 3wlive.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 STATUSON 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 STATUSON 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:Service Broker 对话安全设置 | Microsoft Learn。/***********************************************************************/ -- 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 ROUTEhttp://msdn.microsoft.com/en-us/library/ms186742.aspx创建路由/************************创建路由 3wlive.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 BINDINGCREATE REMOTE SERVICE BINDING (Transact-SQL) - SQL Server | Microsoft Learn创建远程服务绑定/*********************创建远程服务绑定 3wlive.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 id3439 customer22 orderdate9/25/2008 LineItem ItemNumber22 ISBN1-59059-592-0 Quantity10 / /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 通信协议 Service Broker 通信协议 | Microsoft Learn下文将介绍SQL Server 2008中新增的很酷的功能Service broker事件通知。邀月注本文版权由邀月和CSDN共同所有转载请注明出处。助人等于自助!3wlive.cn