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

复制代码 代码如下: USE TestDB declare
@conversation uniqueidentifier while exists (select 1 from
sys.transmission_queue ) begin set @conversation = (select top 1
conversation_handle from sys.transmission_queue ) end conversation
@conversation with cleanup end
那么客户端接受到的消息如果没有处理,也会积攒在客户端队列中,其实就相当于许多未读邮件,我们可以使用以下脚本读取队列
,读取后队列自动清空: 复制代码 代码如下:
USE TestDB DECLARE @RecvReplyMsg NVARCHAR(100) ; DECLARE
@RecvReplyDlgHandle UNIQUEIDENTIFIER ; BEGIN TRANSACTION ; WHILE ( 1 = 1
) BEGIN WAITFOR ( RECEIVE TOP(1) @RecvReplyDlgHandle =
conversation_handle, @RecvReplyMsg = message_body FROM
dbo.Test_TargetQueue ), TIMEOUT 1000 ; END CONVERSATION
@RecvReplyDlgHandle ; SELECT @RecvReplyMsg AS ReceivedReplyMsg ; END
COMMIT TRANSACTION ;

不同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_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

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

澳门金沙vip,   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;