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;

IF EXISTS (SELECT * FROM sys.endpoints

WHERE name = N'InstInitiatorEndpoint')

DROP ENDPOINT InstInitiatorEndpoint;

CREATE ENDPOINT InstInitiatorEndpoint

STATE = STARTED

AS TCP ( LISTENER_PORT = 4022 )

FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );

USE master;

IF EXISTS (SELECT * FROM master.sys.endpoints

WHERE name = N'InstTargetEndpoint')

DROP ENDPOINT InstTargetEndpoint;

CREATE ENDPOINT InstTargetEndpoint

STATE = STARTED

AS TCP ( LISTENER_PORT = 4022 )

FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );

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

注意：如果数据已存在，请重新命名。执行前请检查！！！

USE master;

IF EXISTS (SELECT * FROM sys.databases

WHERE name = N'InstInitiatorDB')

DROP DATABASE InstInitiatorDB;

CREATE DATABASE InstInitiatorDB;

USE InstInitiatorDB;

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = N'MikeA3070814!';

CREATE USER InitiatorUser WITHOUT LOGIN;

USE master;

IF EXISTS (SELECT * FROM sys.databases

WHERE name = N'InstTargetDB')

DROP DATABASE InstTargetDB;

CREATE DATABASE InstTargetDB;

USE InstTargetDB;

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = N'MikeA3070814!';

CREATE USER TargetUser WITHOUT LOGIN;

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

USE InstInitiatorDB;

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';

USE InstTargetDB;

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';

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

USE InstInitiatorDB;

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

VALIDATION = WELL_FORMED_XML;

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

VALIDATION = WELL_FORMED_XML;

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

SENT BY INITIATOR,

[//BothDB/2InstSample/ReplyMessage]

SENT BY TARGET

);

USE InstTargetDB;

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

VALIDATION = WELL_FORMED_XML;

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

VALIDATION = WELL_FORMED_XML;

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

([//BothDB/2InstSample/RequestMessage]

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

SENT BY TARGET

);

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

USE InstInitiatorDB;

CREATE QUEUE InstInitiatorQueue;

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

AUTHORIZATION InitiatorUser

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

USE InstTargetDB;

CREATE QUEUE InstTargetQueue;

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

AUTHORIZATION TargetUser

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

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

USE InstInitiatorDB;

CREATE USER TargetUser WITHOUT LOGIN;

CREATE CERTIFICATE InstTargetCertificate

AUTHORIZATION TargetUser

FROM FILE =

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

USE InstTargetDB

CREATE USER InitiatorUser WITHOUT LOGIN;

CREATE CERTIFICATE InstInitiatorCertificate

AUTHORIZATION InitiatorUser

FROM FILE =

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

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);

CREATE REMOTE SERVICE BINDING TargetBinding

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

WITH USER = TargetUser;

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);

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

TO InitiatorUser;

CREATE REMOTE SERVICE BINDING InitiatorBinding

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

WITH USER = InitiatorUser;

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

USE InstInitiatorDB;

--启动会话并发送消息

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 xml),* from [dbo].[InstTargetQueue]--查看队列中的消息

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

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

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

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

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

USE master;

IF EXISTS (SELECT * FROM sys.endpoints

WHERE name = N'InstInitiatorEndpoint')

DROP ENDPOINT InstInitiatorEndpoint;

CREATE ENDPOINT InstInitiatorEndpoint

STATE = STARTED

AS TCP ( LISTENER_PORT = 4022, LISTENER_IP = ALL )

FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );

USE master;

IF EXISTS (SELECT * FROM master.sys.endpoints

WHERE name = N'InstTargetEndpoint')

DROP ENDPOINT InstTargetEndpoint;

CREATE ENDPOINT InstTargetEndpoint

STATE = STARTED

AS TCP ( LISTENER_PORT = 4022,LISTENER_IP = ALL )

FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );

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);

DECLARE @Cmd NVARCHAR(4000);

SET @Cmd = N'USE msdb

CREATE ROUTE InstTargetRoute

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

ADDRESS = N''LOCAL''';

EXEC (@Cmd);

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

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

USE master;

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;

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

DECRYPTION BY PASSWORD = 'PasswordA3070814'

USE master;

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

DECRYPTION BY PASSWORD = 'PasswordA3070814'

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

USE InstInitiatorDB;

--启动会话并发送消息

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;

USE InstTargetDB;

--接收消息并发送答复

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;

STEP15. 接收答复并结束会话（侦听地址）

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

USE InstInitiatorDB;

--接收答复并结束会话

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;

常见的基本操作语句：

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

select conversation_handle,cast(message_body as xml),* from [dbo].[InstInitiatorQueue]--查看队列中的消息

select transmission_status,cast(message_body as xml),* 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

--==

代码：

USE master;

GO

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

ENCRYPTION BY PASSWORD = 'Password1'

--step1

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

-------------------------------------------------

--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'MyPassword01!';

GO

CREATE USER InitiatorUser WITHOUT LOGIN;

GO

---------------------------------------------------

--step3

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

-----------------------------------------------------

--step4

USE InstInitiatorDB;

GO

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

       VALIDATION = WELL_FORMED_XML;

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

       VALIDATION = WELL_FORMED_XML;

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

-------------------------------------------------------

--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

----------------------------------------------------

--step7

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

------------------------------------------------------------

--step8

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

------------------------------------

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

select conversation_handle,cast(message_body as xml),* from [dbo].[InstInitiatorQueue]--查看队列中的消息

select transmission_status,cast(message_body as xml),* 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

--===

发送方CQECDB2(主本)

--step1

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'InstTargetDB')

     DROP DATABASE InstTargetDB;

GO

CREATE DATABASE InstTargetDB;

GO

USE InstTargetDB;

GO

CREATE MASTER KEY

       ENCRYPTION BY PASSWORD = N'Mypassword01!';

GO

CREATE USER TargetUser WITHOUT LOGIN;

GO

-----------------------------------------------------

--step3

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 InstTargetDB;

GO

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

       VALIDATION = WELL_FORMED_XML;

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

       VALIDATION = WELL_FORMED_XML;

GO

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

      ([//BothDB/2InstSample/RequestMessage]

         SENT BY INITIATOR,

       [//BothDB/2InstSample/ReplyMessage]

         SENT BY TARGET

      );

GO

----------------------------------------------------

--step5

USE InstTargetDB;

GO

CREATE QUEUE InstTargetQueue;

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

       AUTHORIZATION TargetUser

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

GO

------------------------------------------------------

--step6

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

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:

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;

END

SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;

GO

-----------------------------------------------------

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

select conversation_handle,cast(message_body as xml),* from [dbo].[InstTargetQueue]--查看队列中的消息

select transmission_status,cast(message_body as xml),* 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

--===

接收方scmdb16(主本)

--step1

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

----------------------------------------

--step2 建立msdb route

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

---------------------------------------------------

USE master;

GO

RESTORE 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.

--故障转移回来,

发送方CQECDB3(副本)

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

-----------------------------------------

--step2 建立msdb route

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

----------------------------------

USE master;

GO

RESTORE 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 xml),* from [dbo].[InstTargetQueue]--查看队列中的消息

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

接收方scmdb10(副本)

Service Broker应用（2）：不同server间的数据传输，包含集群的更多相关文章

SQL Server上唯一的数据库集群：负载均衡、读写分离、容灾(数据零丢失、服务高可用)
SQL Server上唯一的数据库集群:负载均衡.读写分离.容灾(数据零丢失.服务高可用).审计.优化,全面解决数据库用户问题.一键安装,易用稳定,性价比高,下载链接:http://www.zheti ...
SQL Server 2016 + AlwaysOn 无域集群
目录 AlwaysOn 搭建 WSFC 配置计算机的 DNS 后缀安装故障转移集群验证集群创建集群创建文件共享见证配置 AlwaysOn 新建可用性组创建侦听器可读副本的负载均衡主角色 ...
SQL SERVER 2016 AlwaysOn 无域集群+负载均衡搭建与简测
之前和很多群友聊天发现对2016的无域和负载均衡满心期待,毕竟可以简单搭建而且可以不适用第三方负载均衡器,SQL自己可以负载了.windows2016已经可以下载使用了,那么这回终于可以揭开令人憧憬向 ...
将Sql Server迁移到Always on集群 - 账号的同步
Always on环境的建立,网上资料很多,主要是windows集群的建立以及Sql Server Always on的建立,略容易忽略的是Sql server账号同步问题(Always on能实现 ...
Arcgis Server 10&period;4&period;1 搭建集群环境
1.准备工作 Arcgis Server 10.4.1 以及许可一枚共享存储(通过UNC路径访问,如"\\server1\arcgisserver\") 服务器两台(虚拟机也可 ...
MongoDB 集群 config server 查询超时导致 mongos 集群写入失败
环境 OS:CentOS 7.x DB:MongoDB 3.6.12 集群模式:mongod-shard1 *3 + mongod-shard2 *3 + mongod-conf-shard *3 + ...
spring-cloud&colon;eureka server单机、双机、集群示例
1.运行环境开发工具:intellij idea JDK版本:1.8 项目管理工具:Maven 4.0.0 2.GITHUB地址 https://github.com/nbfujx/springCl ...
基于SQL Server 2008 Service Broker构建企业级消息系统
注:这篇文章是为InfoQ 中文站而写,文章的地址是:http://www.infoq.com/cn/articles/enterprisemessage-sqlserver-servicebroke ...
在Windows Server 2008 R2 Server中，连接其他服务器的数据库遇到&OpenCurlyDoubleQuote;未启用当前数据库的 SQL Server Service Broker，因此查询通知不受支持。如果希望使用通知，请为此数据库启用 Service Broker ”
项目代码和数据库部署在不同的Windows Server 2008 R2 Server中,错误日志显示如下: "未启用当前数据库的 SQL Server Service Broker,因此查 ...

随机推荐

SQL Tuning 基础概述01 - Autotrace的设定
1.autotrace的设定 SQL> set autotrace Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [ST ...
LoTVideo：只需两步，让HTML5原生态的Video茁壮成长
开源地址:https://github.com/dunitian/LoTHTML5/tree/master/LoTVideo 第一步引入lotvideo.js(先确认在这个前面引入了jq包) 第二步: ...
android&colon;process为耗资源操作指定一个新进程
当有一些耗费内存比较多的操作时,可以通过android:process指定一个新的进程.保证程序运行. 例如: 一个后台长期运行的service: <service android:name=& ...
Java笔记(day7-day8)
this关键字: (1)this(当局部变量和成员变量重名时,可以用关键字this区分) this代表对象,当前对象 this就是所在函数所属对象的引用简单来说,哪个对象 ...
session的几种模式
https://www.cnblogs.com/panchunting/archive/2012/06/25/ASPNET_Session_Mode.html token的生成和应用 session和 ...
React 中的this&period;setState
在react中如何修改state中的数据第一种写法:this.setState() 参数1:对象需要修改的数据参数2:回调 this.setState是一 ...
BBS论坛（七）
7.1.修改邮箱界面完成 (1)cms/cms_resetemail.html {% extends 'cms/cms_base.html' %} {% block title -%} 修改邮箱 {% ...
用JavaScript制作简单的计算器
<html > <head> <title>简单计算器</title> <style type="text/css"> ...
软件需求规格说明书(转自http&colon;//blog&period;csdn&period;net/li&lowbar;canhui/article/details/6927540)
转自http://blog.csdn.net/li_canhui/article/details/6927540 1概述 1.1编写目的指出编写<需求规格说明书>的目的.下面是示例: 编 ...
Hive的基本介绍
Hive最初是应Facebook每天产生的海量新兴社会网络数据进行管理和机器学习的需求而产生和发展的.那么,到底什么是Hive,我们先看看Hive官网Wiki是如何介绍Hive的(https://cw ...