在Sql Server中使用服务代理进行异步过程调用
问题描述:
我有一个按计划每天运行的备份过程(生产),并且定期使用生成的备份来刷新较低的环境。我正在构建一个自动化解决方案,将.bak文件下载到较低的环境(不同的服务器)。我不愿意在备份中使用MIRROR TO选项,因为如果镜像中的内容失败,它可能会影响正常的备份操作。我在这里想要使用服务代理,并在备份过程结束时异步触发从prod到较低环境的文件副本。我在了解服务代理,消息类型,服务,队列和合同方面付出了很多努力。一切都很好,直到这里。现在我只想了解如何使用服务代理在备份结束时触发带有一些自定义逻辑的filecopy过程。我玩过以下脚本来了解服务代理。有人指导我如何从中建立解决方案。在Sql Server中使用服务代理进行异步过程调用
------------------------------------------------------------SETUP--------------------------------------------
CREATE MESSAGE TYPE [//SBTest/SBSample/RequestMessage] VALIDATION=WELL_FORMED_XML;
CREATE MESSAGE TYPE [//SBTest/SBSample/ReplyMessage] VALIDATION=WELL_FORMED_XML;
-------------------------------------------------------------------------------------------------------------
CREATE CONTRACT [//SBTest/SBSample/SBContract]
(
[//SBTest/SBSample/RequestMessage] SENT BY INITIATOR ,
[//SBTest/SBSample/ReplyMessage] SENT BY TARGET
);
-------------------------------------------------------------------------------------------------------------
CREATE QUEUE SBInitiatorQueue;
CREATE QUEUE SBTargetQueue;
-------------------------------------------------------------------------------------------------------------
CREATE SERVICE [//SBTest/SBSample/SBInitiatorService] ON QUEUE SBInitiatorQueue;
CREATE SERVICE [//SBTest/SBSample/SBTargetService] ON QUEUE SBTargetQueue ([//SBTest/SBSample/SBContract]);
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------INITIATE QUE-----------------------------------------
DECLARE @InitDlgHandle UNIQUEIDENTIFIER
DECLARE @RequestMessage VARCHAR(1000)
BEGIN TRAN
--Determine the Initiator Service, Target Service and the Contract
BEGIN DIALOG @InitDlgHandle
FROM SERVICE [//SBTest/SBSample/SBInitiatorService] TO SERVICE'//SBTest/SBSample/SBTargetService'
ON CONTRACT
[//SBTest/SBSample/SBContract]
WITH ENCRYPTION=OFF;
--Prepare the Message
SELECT @RequestMessage = N'HAHA';
--Send the Message
SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE
[//SBTest/SBSample/RequestMessage]
(@RequestMessage);
SELECT @RequestMessage AS SentRequestMessage;
COMMIT TRAN
---------------------------------------------------------------------------------------------------------------
-------------------------------------------------------READ QUE-------------------------------------------------
\DECLARE @TargetDlgHandle UNIQUEIDENTIFIER
DECLARE @ReplyMessage VARCHAR(1000)
DECLARE @ReplyMessageName Sysname
BEGIN TRAN;
--Receive message from Initiator
RECEIVE TOP(1)
@TargetDlgHandle=Conversation_Handle, @ReplyMessage=Message_Body, @ReplyMessageName=Message_Type_Name
FROM SBTargetQueue;
SELECT @ReplyMessage AS ReceivedRequestMessage;
-- Confirm and Send a reply
IF @ReplyMessageName=N'HAHA'
BEGIN
DECLARE @RplyMsg VARCHAR(1000)
SELECT @RplyMsg =N'HI';
SEND ON CONVERSATION @TargetDlgHandle
MESSAGE TYPE
[//SBTest/SBSample/ReplyMessage]
(@RplyMsg);
END CONVERSATION @TargetDlgHandle;
END
SELECT @RplyMsg AS SentReplyMessage;
COMMIT TRAN;
-------------------------------------------------------------------------------------------------------------------
答
触发使用“内部激活”发生。您需要创建一个存储过程,当消息进入队列,从队列中读取,然后完成工作时将触发该存储过程。 从link改编:
CREATE PROCEDURE TargetActivProc
AS
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;
WHILE (1=1)
BEGIN
BEGIN TRANSACTION;
WAITFOR
(RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
FROM TargetQueueIntAct
), TIMEOUT 5000;
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END
IF @RecvReqMsgName =
N'//SBTest/SBSample/RequestMessage'
BEGIN
--do work here
END
ELSE IF @RecvReqMsgName =
N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @RecvReqDlgHandle;
END
ELSE IF @RecvReqMsgName =
N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
END CONVERSATION @RecvReqDlgHandle;
END
COMMIT TRANSACTION;
END
GO
接下来的部分是对队列
ALTER QUEUE SBTargetQueue
WITH ACTIVATION
(STATUS = ON,
PROCEDURE_NAME = TargetActivProc,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF
);
当消息到达队列,并且将保持激活和循环,直到该过程将被激活使能活化队列是空的。
Richard Cupples 一段时间,如果队列变空了,什么会再次触发它?队列中的新消息触发它? – TharunRaja
@TharunRaja这是正确的。只要队列中有消息,SQL Server将定期检查激活的进程数是否为
我试过改变队列脚本,并附加了一些随机过程名称,它插入到一个虚拟表中。有时我会看到当队列中有消息时,附加到队列中的过程得到执行,有时候我看不到它会被执行。 MAX_QUEUE_READERS选项和队列中消息的数量是什么?你的回答对我了解服务经纪人有很大的帮助。谢谢。 – TharunRaja