SQL Server 2008中远程Service Broker实现

忘是亡心i 2022-06-18 06:42 171阅读 0赞

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

[http://msdn.microsoft.com/zh-cn/library/ms166061%28v=sql.100%29.aspx][http_msdn.microsoft.com_zh-cn_library_ms166061_28v_sql.100_29.aspx]

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

转载自：[http://www.cnblogs.com/downmoon/archive/2011/05/05/2037830.html][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 网络通信的高级视图。

![0_1318846269yY0V.gif][]![0_1318846269yY0V.gif][]

准备工作：

![0_1318846296wJj4.gif][]

/*
       
        **********************************************************
       
        */
       
        
       
        --
       
        ----远程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][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][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][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_msdn.microsoft.com_zh-cn_library_ms166061_28v_sql.100_29.aspx]: http://msdn.microsoft.com/zh-cn/library/ms166061%28v=sql.100%29.aspx
[http_www.cnblogs.com_downmoon_archive_2011_05_05_2037830.html]: http://www.cnblogs.com/downmoon/archive/2011/05/05/2037830.html
[0_1318846269yY0V.gif]: /images/20220618/e9f33fa330c341469ecaaaf8974df082.png
[0_1318846296wJj4.gif]: /images/20220618/4e6f8705649f477b9bd7addc41acd0ba.png
[http_msdn.microsoft.com_zh-cn_library_ms166036.aspx]: http://msdn.microsoft.com/zh-cn/library/ms166036.aspx
[http_msdn.microsoft.com_en-us_library_ms186742.aspx]: http://msdn.microsoft.com/en-us/library/ms186742.aspx
[http_msdn.microsoft.com_en-us_library_ms178024.aspx]: http://msdn.microsoft.com/en-us/library/ms178024.aspx