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

View Code 发送方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
--===

View Code 接收方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.
--故障转移回来,

View Code 发送方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 --查看当期数据库中待传送的消息

View Code 接收方scmdb10(副本)

posted on 2016-07-22 15:35 探索发现dqs 阅读(930) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

探索发现dqs

导航

公告

Service Broker应用（2）：不同server间的数据传输，包含集群