你的位置:首页 > 数据库

[数据库]Service Broker应用(2):不同server间的数据传输,包含集群

不同Server之间的数据传输,包含DB使用AlwaysOn

配置脚本:

SQL Server Service Broker 跨集群通信

具体的TSQL 脚本语句如下。注意的是TSQL语句是在发送方还是接收方运行。对每个step,要先运行左边的, 然后运行右边的。 一共15个step。

 

发送方集群

侦听地址:10.17.30.46

接收方集群

侦听地址:172.20.168.235

STEP1. 创建Service Broker端点,默认 TCP 端口号 4022(主本服务器上执行)

注意:执行前请检查当前服务器中,该端点名称是否是正在使用的端点,如果是请重新命名

USE master;

GO

IF EXISTS (SELECT * FROM sys.endpoints

           WHERE name = N'InstInitiatorEndpoint')

     DROP ENDPOINT InstInitiatorEndpoint;

GO

CREATE ENDPOINT InstInitiatorEndpoint

STATE = STARTED

AS TCP ( LISTENER_PORT = 4022 )

FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );

GO

USE master;

GO

IF EXISTS (SELECT * FROM master.sys.endpoints

           WHERE name = N'InstTargetEndpoint')

     DROP ENDPOINT InstTargetEndpoint;

GO

CREATE ENDPOINT InstTargetEndpoint

STATE = STARTED

AS TCP ( LISTENER_PORT = 4022 )

FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );

GO

STEP2. 创建数据库、创建用于支持加密和远程连接的主密钥和用户。(主本服务器上执行)

注意:如果数据已存在,请重新命名。执行前请检查!!!

USE master;

GO

IF EXISTS (SELECT * FROM sys.databases

           WHERE name = N'InstInitiatorDB')

     DROP DATABASE InstInitiatorDB;

GO

CREATE DATABASE InstInitiatorDB;

GO

 

USE InstInitiatorDB;

GO

CREATE MASTER KEY

       ENCRYPTION BY PASSWORD = N'MikeA3070814!';

GO

CREATE USER InitiatorUser WITHOUT LOGIN;

GO

USE master;

GO

IF EXISTS (SELECT * FROM sys.databases

           WHERE name = N'InstTargetDB')

     DROP DATABASE InstTargetDB;

GO

CREATE DATABASE InstTargetDB;

GO

 

USE InstTargetDB;

GO

CREATE MASTER KEY

       ENCRYPTION BY PASSWORD = N'MikeA3070814!';

GO

CREATE USER TargetUser WITHOUT LOGIN;

GO

 

STEP3. 创建用于加密消息的证书。需要copy这个证书到双方能够访问的文件夹(主本服务器上执行)

USE InstInitiatorDB;

GO

CREATE CERTIFICATE InstInitiatorCertificate

     AUTHORIZATION InitiatorUser

     WITH SUBJECT = N'Initiator Certificate',

          EXPIRY_DATE = N'12/31/2090';

BACKUP CERTIFICATE InstInitiatorCertificate

  TO FILE =

N'\\172.20.168.56\Document\SSB\mike\InstInitiatorCertificate2.cer';

GO

USE InstTargetDB;

GO

CREATE CERTIFICATE InstTargetCertificate

     AUTHORIZATION TargetUser

     WITH SUBJECT = 'Target Certificate',

          EXPIRY_DATE = N'12/31/2090';

 

BACKUP CERTIFICATE InstTargetCertificate

  TO FILE =

N'\\172.20.168.56\Document\SSB\mike\InstTargetCertificate2.cer';

GO

STEP4. 为会话创建消息类型和约定 。发起方和目标方指定的消息、约定的名称和他们属性必须相同。(主本服务器上执行)

USE InstInitiatorDB;

GO

CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]

       VALIDATION = WELL_FORMED_

CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]

       VALIDATION = WELL_FORMED_

GO

CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]      ([//BothDB/2InstSample/RequestMessage]

         SENT BY INITIATOR,

 [//BothDB/2InstSample/ReplyMessage]

         SENT BY TARGET

      );

GO

USE InstTargetDB;

GO

CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]

       VALIDATION = WELL_FORMED_

CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]

       VALIDATION = WELL_FORMED_

GO

CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]

([//BothDB/2InstSample/RequestMessage]

         SENT BY INITIATOR,       [//BothDB/2InstSample/ReplyMessage]

         SENT BY TARGET

      );

GO

STEP5. 创建队列和服务,注意服务和队列如何关联。发送给此服务的消息将保存到相应的队列中。(主本服务器上执行)

USE InstInitiatorDB;

GO

CREATE QUEUE InstInitiatorQueue;

CREATE SERVICE [//InstDB/2InstSample/InitiatorService]

       AUTHORIZATION InitiatorUser

       ON QUEUE InstInitiatorQueue ([//BothDB/2InstSample/SimpleContract]);

GO

USE InstTargetDB;

GO

CREATE QUEUE InstTargetQueue;

CREATE SERVICE [//TgtDB/2InstSample/TargetService]

       AUTHORIZATION TargetUser

       ON QUEUE InstTargetQueue       ([//BothDB/2InstSample/SimpleContract]);

GO

STEP6. 创建对目标对象的引用需要访问对方先前创建的证书。(主本服务器上执行)

USE InstInitiatorDB;

GO

CREATE USER TargetUser WITHOUT LOGIN;

CREATE CERTIFICATE InstTargetCertificate

   AUTHORIZATION TargetUser

   FROM FILE =

N'\\172.20.168.56\Document\SSB\mike\InstTargetCertificate2.cer'

GO

USE InstTargetDB

GO

CREATE USER InitiatorUser WITHOUT LOGIN;

CREATE CERTIFICATE InstInitiatorCertificate

   AUTHORIZATION InitiatorUser

   FROM FILE =

N'\\172.20.168.56\Document\SSB\mike\InstInitiatorCertificate2.cer';

GO

STEP7. 创建指向服务路由,并创建将User 与目标服务路由相关联的远程服务绑定。(主本服务器上执行)

注意:TCP地址是对方集群的侦听地址

DECLARE @Cmd NVARCHAR(4000);

SET @Cmd = N'USE InstInitiatorDB;

CREATE ROUTE InstTargetRoute

WITH SERVICE_NAME = N''//TgtDB/2InstSample/TargetService'',

     ADDRESS = ''TCP://172.20.168.235:4022'';';

EXEC (@Cmd);

SET @Cmd = N'USE msdb

CREATE ROUTE InstInitiatorRoute

WITH SERVICE_NAME = N''//InstDB/2InstSample/InitiatorService'',

     ADDRESS = N''LOCAL''';

EXEC (@Cmd);

GO

 

CREATE REMOTE SERVICE BINDING TargetBinding

      TO SERVICE  N'//TgtDB/2InstSample/TargetService'

      WITH USER = TargetUser;

 

GO

 

DECLARE @Cmd NVARCHAR(4000);

SET @Cmd = N'USE InstTargetDB;

CREATE ROUTE InstInitiatorRoute

WITH SERVICE_NAME = N''//InstDB/2InstSample/InitiatorService'',

ADDRESS = ''TCP://10.17.30.46:4022'';';

EXEC (@Cmd);

SET @Cmd = N'USE msdb

CREATE ROUTE InstTargetRoute

WITH SERVICE_NAME = N''//TgtDB/2InstSample/TargetService'',

     ADDRESS = N''LOCAL''';

EXEC (@Cmd);

GO

GRANT SEND  ON SERVICE::[//TgtDB/2InstSample/TargetService]

      TO InitiatorUser;

GO

 

CREATE REMOTE SERVICE BINDING InitiatorBinding

      TO SERVICE N'//InstDB/2InstSample/InitiatorService'

      WITH USER = InitiatorUser;

GO

STEP8. 数据库未加入集群时,测试SSB是否配置成功。目标队列收到发送的消息即成功,反之。(主本服务器上执行)

USE InstInitiatorDB;

GO

--启动会话并发送消息

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;

DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle

     FROM SERVICE [//InstDB/2InstSample/InitiatorService]

     TO SERVICE N'//TgtDB/2InstSample/TargetService'

     ON CONTRACT [//BothDB/2InstSample/SimpleContract]

     WITH

         ENCRYPTION = ON;

SELECT @RequestMsg = '<RequestMsg>test1:测试数据库未加入集群时,SSB是否配置成功</RequestMsg>';

SEND ON CONVERSATION @InitDlgHandle

     MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]

     (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;

select conversation_handle,state_desc,* from sys.conversation_endpoints--查看当前数据库中开启的会话

select conversation_handle,cast(message_body as

select transmission_status,cast(message_body as

 

 

 

 

STEP9. 配置成功后,发送消息的数据库InstInitiatorDB和接收消息的数据库InstTargetDB都加入集群

加入集群的步骤,此处省略

 

加入集群的步骤,此处省略

STEP10. 在副本服务器的Master数据库上建立端点(副本服务器上执行)

注意:执行前请检查当前服务器中,该端点名称是否是正在使用的端点,如果是请重新命名

USE master;

GO

IF EXISTS (SELECT * FROM sys.endpoints

           WHERE name = N'InstInitiatorEndpoint')

     DROP ENDPOINT InstInitiatorEndpoint;

GO

CREATE ENDPOINT InstInitiatorEndpoint

STATE = STARTED

AS TCP ( LISTENER_PORT = 4022, LISTENER_IP = ALL  )

FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );

GO

USE master;

GO

IF EXISTS (SELECT * FROM master.sys.endpoints

           WHERE name = N'InstTargetEndpoint')

     DROP ENDPOINT InstTargetEndpoint;

GO

CREATE ENDPOINT InstTargetEndpoint

STATE = STARTED

AS TCP ( LISTENER_PORT = 4022,LISTENER_IP = ALL )

FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );

GO

STEP11. 在副本服务器的msdb数据库上建立路由(副本服务器上执行)

DECLARE @Cmd NVARCHAR(4000);

SET @Cmd = N'USE msdb

CREATE ROUTE InstInitiatorRoute

WITH SERVICE_NAME =       N''//InstDB/2InstSample/InitiatorService'',

     ADDRESS = N''LOCAL''';

EXEC (@Cmd);

GO

DECLARE @Cmd NVARCHAR(4000);

SET @Cmd = N'USE msdb

CREATE ROUTE InstTargetRoute

WITH SERVICE_NAME =        N''//TgtDB/2InstSample/TargetService'',

     ADDRESS = N''LOCAL''';

EXEC (@Cmd);

GO

 

STEP12. 备份发送方主本服务器的service master key(发送主本服务器上执行)

所有副本服务器必须使用统一的服务主密钥

USE master;

GO

BACKUP SERVICE MASTER KEY TO FILE = '\\172.20.168.56\Document\SSB\mike\serviceMasterKey_node1'

ENCRYPTION BY PASSWORD = 'PasswordA3070814'

STEP13. 把发送方主本服务器的service master key restore到所有的副本服务器上(副本服务器上执行)

USE master;

GO

RESTORE SERVICE MASTER KEY FROM FILE = '\\172.20.168.56\Document\SSB\mike\serviceMasterKey_node1'

DECRYPTION BY PASSWORD = 'PasswordA3070814'

USE master;

GO

RESTORE SERVICE MASTER KEY FROM FILE = '\\172.20.168.56\Document\SSB\mike\serviceMasterKey_node1'

DECRYPTION BY PASSWORD = 'PasswordA3070814'

STEP14. 配置成功,开启会话发送消息。先运行左边发送消息,再运行右边接收消息并发送答复的消息(通过侦听地址登陆执行)

USE InstInitiatorDB;

GO

--启动会话并发送消息

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;

DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle

     FROM SERVICE [//InstDB/2InstSample/InitiatorService]

     TO SERVICE N'//TgtDB/2InstSample/TargetService'

     ON CONTRACT [//BothDB/2InstSample/SimpleContract]

     WITH

         ENCRYPTION = ON;

SELECT @RequestMsg = '<RequestMsg>test2:数据库加入集群,手动故障转移,消息发送成功</RequestMsg>';

SEND ON CONVERSATION @InitDlgHandle

     MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]

     (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;

GO

 

USE InstTargetDB;

GO

--接收消息并发送答复

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;

DECLARE @RecvReqMsg NVARCHAR(100);

DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR

( RECEIVE TOP(1)

    @RecvReqDlgHandle = conversation_handle,

    @RecvReqMsg = message_body,

    @RecvReqMsgName = message_type_name

  FROM InstTargetQueue

), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsgName = N'//BothDB/2InstSample/RequestMessage'

BEGIN

     DECLARE @ReplyMsg NVARCHAR(100);

     SELECT @ReplyMsg =

        N'<ReplyMsg>消息接收成功!</ReplyMsg>';

     SEND ON CONVERSATION @RecvReqDlgHandle

          MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]

          (@ReplyMsg);

     END CONVERSATION @RecvReqDlgHandle;

END

SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;

GO

STEP15. 接收答复并结束会话(侦听地址)

注意:队列上可以绑定存储过程,并自动触发存储过程,完成自动处理消息。

USE InstInitiatorDB;

GO

--接收答复并结束会话

DECLARE @RecvReplyMsg NVARCHAR(100);

DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;

BEGIN TRANSACTION;

WAITFOR

( RECEIVE TOP(1)

    @RecvReplyDlgHandle = conversation_handle,

    @RecvReplyMsg = message_body

  FROM InstInitiatorQueue

), TIMEOUT 1000;

END CONVERSATION @RecvReplyDlgHandle;

-- Display recieved request.

SELECT @RecvReplyMsg AS ReceivedReplyMsg;

COMMIT TRANSACTION;

GO

 

常见的基本操作语句:

select conversation_handle,state_desc,* from sys.conversation_endpoints--查看当前数据库中开启的会话

select conversation_handle,cast(message_body as

select transmission_status,cast(message_body as

select transmission_status,message_body,* from sys.transmission_queue --查看当期数据库中待传送的消息

end conversation  '05D1BC83-F31E-E511-80B6-6EAE8B208F71' with cleanup --根据会话端点ID结束会话

 

--==批量结束会话脚本

declare @conversation uniqueidentifier

    declare handle cursor for select conversation_handle from sys.conversation_endpoints--查看当前数据库中开启的会话

    open handle

    fetch next from handle into @conversation

    while(@@fetch_status = 0)

    begin

        end conversation  @conversation with cleanup

        fetch next from handle into @conversation

    end

    close handle

    deallocate handle

--==

代码:

USE master;GOBACKUP SERVICE MASTER KEY TO FILE = '\\172.20.168.56\Document\SSB\mike\serviceMasterKey_node1' ENCRYPTION BY PASSWORD = 'Password1'--step1USE master;GOIF EXISTS (SELECT * FROM sys.endpoints      WHERE name = N'InstInitiatorEndpoint')   DROP ENDPOINT InstInitiatorEndpoint;GOCREATE ENDPOINT InstInitiatorEndpointSTATE = STARTEDAS TCP ( LISTENER_PORT = 4022 )FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );GO---------------------------------------------------step2USE master;GOIF EXISTS (SELECT * FROM sys.databases      WHERE name = N'InstInitiatorDB')   DROP DATABASE InstInitiatorDB;GOCREATE DATABASE InstInitiatorDB;GOUSE InstInitiatorDB;GOCREATE MASTER KEY    ENCRYPTION BY PASSWORD = N'MyPassword01!';GOCREATE USER InitiatorUser WITHOUT LOGIN;GO-----------------------------------------------------step3USE InstInitiatorDB;GO CREATE CERTIFICATE InstInitiatorCertificate   AUTHORIZATION InitiatorUser   WITH SUBJECT = N'Initiator Certificate',     EXPIRY_DATE = N'12/31/2090';BACKUP CERTIFICATE InstInitiatorCertificate TO FILE = N'\\172.20.168.56\Document\SSB\mike\InstInitiatorCertificate2.cer';GO-------------------------------------------------------step4USE InstInitiatorDB;GOCREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]    VALIDATION = WELL_FORMED_CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]    VALIDATION = WELL_FORMED_GOCREATE CONTRACT [//BothDB/2InstSample/SimpleContract]   ([//BothDB/2InstSample/RequestMessage]     SENT BY INITIATOR,    [//BothDB/2InstSample/ReplyMessage]     SENT BY TARGET   );GO---------------------------------------------------------step5USE InstInitiatorDB;GOCREATE QUEUE InstInitiatorQueue;CREATE SERVICE [//InstDB/2InstSample/InitiatorService]    AUTHORIZATION InitiatorUser    ON QUEUE InstInitiatorQueue ([//BothDB/2InstSample/SimpleContract]);GO---------------------------------------------------------step6USE InstInitiatorDB;GOCREATE USER TargetUser WITHOUT LOGIN;CREATE CERTIFICATE InstTargetCertificate  AUTHORIZATION TargetUser  FROM FILE = N'\\172.20.168.56\Document\SSB\mike\InstTargetCertificate2.cer'GO------------------------------------------------------step7DECLARE @Cmd NVARCHAR(4000);SET @Cmd = N'USE InstInitiatorDB;CREATE ROUTE InstTargetRouteWITH SERVICE_NAME = N''//TgtDB/2InstSample/TargetService'',   ADDRESS = ''TCP://172.20.168.235:4022'';';EXEC (@Cmd);SET @Cmd = N'USE msdbCREATE ROUTE InstInitiatorRouteWITH SERVICE_NAME = N''//InstDB/2InstSample/InitiatorService'',   ADDRESS = N''LOCAL''';EXEC (@Cmd);GOCREATE REMOTE SERVICE BINDING TargetBinding   TO SERVICE N'//TgtDB/2InstSample/TargetService'   WITH USER = TargetUser;GO--------------------------------------------------------------step8USE InstInitiatorDB;GO--启动会话并发送消息 DECLARE @InitDlgHandle UNIQUEIDENTIFIER;DECLARE @RequestMsg NVARCHAR(100);BEGIN TRANSACTION;BEGIN DIALOG @InitDlgHandle   FROM SERVICE [//InstDB/2InstSample/InitiatorService]   TO SERVICE N'//TgtDB/2InstSample/TargetService'   ON CONTRACT [//BothDB/2InstSample/SimpleContract]   WITH     ENCRYPTION = ON;SELECT @RequestMsg = '<RequestMsg>test2:数据库加入集群,手动故障转移,消息发送成功</RequestMsg>';SEND ON CONVERSATION @InitDlgHandle   MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]   (@RequestMsg);SELECT @RequestMsg AS SentRequestMsg;COMMIT TRANSACTION;GO------------------------------------select conversation_handle,state_desc,* from sys.conversation_endpoints--查看当前数据库中开启的会话select conversation_handle,cast(message_body as * from [dbo].[InstInitiatorQueue]--查看队列中的消息select transmission_status,cast(message_body as * from sys.transmission_queue --查看当期数据库中待传送的消息select transmission_status,message_body,* from sys.transmission_queue --查看当期数据库中待传送的消息end conversation '05D1BC83-F31E-E511-80B6-6EAE8B208F71' with cleanup --根据会话端点ID结束会话--==批量结束会话脚本declare @conversation uniqueidentifier  declare handle cursor for select conversation_handle from sys.conversation_endpoints--查看当前数据库中开启的会话  open handle  fetch next from handle into @conversation  while(@@fetch_status = 0)  begin     end conversation @conversation with cleanup    fetch next from handle into @conversation  end  close handle  deallocate handle--===

View Code 发送方CQECDB2(主本)
--step1USE master;GOIF EXISTS (SELECT * FROM master.sys.endpoints      WHERE name = N'InstTargetEndpoint')   DROP ENDPOINT InstTargetEndpoint;GOCREATE ENDPOINT InstTargetEndpointSTATE = STARTEDAS TCP ( LISTENER_PORT = 4022 )FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );GO---------------------------------------------------step2USE master;GOIF EXISTS (SELECT * FROM sys.databases      WHERE name = N'InstTargetDB')   DROP DATABASE InstTargetDB;GOCREATE DATABASE InstTargetDB;GOUSE InstTargetDB;GOCREATE MASTER KEY    ENCRYPTION BY PASSWORD = N'Mypassword01!';GOCREATE USER TargetUser WITHOUT LOGIN;GO-------------------------------------------------------step3USE InstTargetDB;GOCREATE CERTIFICATE InstTargetCertificate   AUTHORIZATION TargetUser   WITH SUBJECT = 'Target Certificate',     EXPIRY_DATE = N'12/31/2090';BACKUP CERTIFICATE InstTargetCertificate TO FILE = N'\\172.20.168.56\Document\SSB\mike\InstTargetCertificate2.cer';GO--------------------------------------------------------step4USE InstTargetDB;GOCREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]    VALIDATION = WELL_FORMED_CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]    VALIDATION = WELL_FORMED_GOCREATE CONTRACT [//BothDB/2InstSample/SimpleContract]   ([//BothDB/2InstSample/RequestMessage]     SENT BY INITIATOR,    [//BothDB/2InstSample/ReplyMessage]     SENT BY TARGET   );GO------------------------------------------------------step5USE InstTargetDB;GOCREATE QUEUE InstTargetQueue;CREATE SERVICE [//TgtDB/2InstSample/TargetService]    AUTHORIZATION TargetUser    ON QUEUE InstTargetQueue    ([//BothDB/2InstSample/SimpleContract]);GO--------------------------------------------------------step6USE InstTargetDBGOCREATE USER InitiatorUser WITHOUT LOGIN;CREATE CERTIFICATE InstInitiatorCertificate  AUTHORIZATION InitiatorUser  FROM FILE = N'\\172.20.168.56\Document\SSB\mike\InstInitiatorCertificate2.cer';GO---------------------------------------------------------step7DECLARE @Cmd NVARCHAR(4000);SET @Cmd = N'USE InstTargetDB;CREATE ROUTE InstInitiatorRouteWITH SERVICE_NAME = N''//InstDB/2InstSample/InitiatorService'', ADDRESS = ''TCP://10.17.30.46:4022'';';EXEC (@Cmd);SET @Cmd = N'USE msdbCREATE ROUTE InstTargetRouteWITH SERVICE_NAME = N''//TgtDB/2InstSample/TargetService'',   ADDRESS = N''LOCAL''';EXEC (@Cmd);GOGRANT SEND ON SERVICE::[//TgtDB/2InstSample/TargetService]   TO InitiatorUser;GOCREATE REMOTE SERVICE BINDING InitiatorBinding   TO SERVICE N'//InstDB/2InstSample/InitiatorService'   WITH USER = InitiatorUser;GO--------------------------------------------------------step8:USE InstTargetDB;GO--接收请求并发送答复 DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;DECLARE @RecvReqMsg NVARCHAR(100);DECLARE @RecvReqMsgName sysname;BEGIN TRANSACTION;WAITFOR( RECEIVE TOP(1)  @RecvReqDlgHandle = conversation_handle,  @RecvReqMsg = message_body,  @RecvReqMsgName = message_type_name FROM InstTargetQueue), TIMEOUT 1000;SELECT @RecvReqMsg AS ReceivedRequestMsg;IF @RecvReqMsgName = N'//BothDB/2InstSample/RequestMessage'BEGIN   DECLARE @ReplyMsg NVARCHAR(100);   SELECT @ReplyMsg =    N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';   SEND ON CONVERSATION @RecvReqDlgHandle     MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]     (@ReplyMsg);   END CONVERSATION @RecvReqDlgHandle;ENDSELECT @ReplyMsg AS SentReplyMsg;COMMIT TRANSACTION;GO-----------------------------------------------------select conversation_handle,state_desc,* from sys.conversation_endpoints--查看当前数据库中开启的会话select conversation_handle,cast(message_body as * from [dbo].[InstTargetQueue]--查看队列中的消息select transmission_status,cast(message_body as * from sys.transmission_queue --查看当期数据库中待传送的消息--==批量结束会话脚本declare @conversation uniqueidentifier  declare handle cursor for select conversation_handle from sys.conversation_endpoints--查看当前数据库中开启的会话  open handle  fetch next from handle into @conversation  while(@@fetch_status = 0)  begin     end conversation @conversation with cleanup    fetch next from handle into @conversation  end  close handle  deallocate handle--===

View Code 接收方scmdb16(主本)
--step1USE master;GOIF EXISTS (SELECT * FROM sys.endpoints      WHERE name = N'InstInitiatorEndpoint')   DROP ENDPOINT InstInitiatorEndpoint;GOCREATE ENDPOINT InstInitiatorEndpointSTATE = STARTEDAS TCP ( LISTENER_PORT = 4022, LISTENER_IP = ALL )FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );GO------------------------------------------step2 建立msdb routeDECLARE @Cmd NVARCHAR(4000);SET @Cmd = N'USE msdbCREATE ROUTE InstInitiatorRouteWITH SERVICE_NAME =    N''//InstDB/2InstSample/InitiatorService'',   ADDRESS = N''LOCAL''';EXEC (@Cmd);GO---------------------------------------------------USE master;GORESTORE SERVICE MASTER KEY FROM FILE = '\\172.20.168.56\Document\SSB\mike\serviceMasterKey_node1'DECRYPTION BY PASSWORD = 'Password1'消息 15320,级别 16,状态 12,第 1 行对使用旧主密钥加密的 主密钥 'QWMS_Interface'进行解密时出错。可以使用 FORCE 选项忽略此错误并继续此操作,但使用该旧主密钥无法解密的数据将变得不可用。--发送方第一次故障转移:The session keys for this conversation could not be created or accessed. The database master key is required for this operation.--故障转移回来,

View Code 发送方CQECDB3(副本)
USE master;GOIF EXISTS (SELECT * FROM master.sys.endpoints      WHERE name = N'InstTargetEndpoint')   DROP ENDPOINT InstTargetEndpoint;GOCREATE ENDPOINT InstTargetEndpointSTATE = STARTEDAS TCP ( LISTENER_PORT = 4022,LISTENER_IP = ALL )FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );GO-------------------------------------------step2 建立msdb routeDECLARE @Cmd NVARCHAR(4000);SET @Cmd = N'USE msdbCREATE ROUTE InstTargetRouteWITH SERVICE_NAME =    N''//TgtDB/2InstSample/TargetService'',   ADDRESS = N''LOCAL''';EXEC (@Cmd);GO----------------------------------USE master;GORESTORE SERVICE MASTER KEY FROM FILE = '\\172.20.168.56\Document\SSB\mike\serviceMasterKey_node1'DECRYPTION BY PASSWORD = 'Password1'go新旧主密钥完全相同。不需要重新加密数据。--------------------------------------select conversation_handle,state_desc,* from sys.conversation_endpoints--查看当前数据库中开启的会话select conversation_handle,cast(message_body as * from [dbo].[InstTargetQueue]--查看队列中的消息select transmission_status,cast(message_body as * from sys.transmission_queue --查看当期数据库中待传送的消息

View Code 接收方scmdb10(副本)