• 转发：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事件通知。

    转发自：http://www.cnblogs.com/downmoon/archive/2011/05/05/2037830.html
  • 相关阅读:
    RMAN异机还原遭遇ORA-19698错误案例
    Linux sendmail发送邮件失败诊断案例(一)
    Oracle system identifier(SID) "xxx" alread exits. Specify another SID
    ORA-12516：TNS:listener could not find available handler with matching protocol stack
    ORACLE编译失效对象小结
    Win7 安装SQL SERVER 2012需要SP1补丁
    SQL SERVER出现大量一致性错误的解决方法
    MS SQL统计信息浅析下篇
    Dell PowerVault TL4000 磁带机卡带问题
    虚拟机VMWARE上ORACLE License 的计算
  • 原文地址：https://www.cnblogs.com/chorrysky/p/2065548.html
Copyright © 2020-2023  润新知