快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

在SQL Server实例与Service Broker之间同步数据

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

问题

我正在考虑将Service Broker组件用于SQL Server表同步项目,但似乎没有足够相关且详细的示例可供我使用。您能否描述使用Service Broker在SQL Server实例之间复制数据的真实案例?

熟悉Service Broker的最佳方法(如果您还没有的话)是通过简单的用例获得一些实践经验。但是,在开始工作之前,您必须了解一些定义和一般事实。因此,您应该花时间探索以下提示:

本技巧基于我几个月前所做的有效实施。我的客户仍然在SQL 2008 R2上运行,但是我已经为您重新完成了最新版本的所有工作。

我不会再对Service Broker组件进行概述。有很多不错的介绍文章,上面列出了其中的一些。相反,我将详细描述如何在两个SQL Server实例之间实现简单的表同步。

我的目标是将“启动器”实例上的表中插入的记录“复制”到“目标”实例上的相同表中。每天在源表中的插入少于100个,并且这些表不需要紧密耦合。因此,我选择实施Service Broker解决方案。

测试环境规格

过去,我已经使用SQL 2008 R2测试了此过程,在本技巧中,我将使用SQL Server 2014 SP2实例:

启动器

  • Windows 10企业版1703 OS Build 15063.7026(我的开发物理机)
  • SQL 2014 SP2版本12.0.5207.0
  • 端口1433和4023已打开
  • 启用了TCP / IP协议

目标

  • Windows Server 2012 R2 Standard(虚拟机)
  • SQL 2014 SP2版本12.0.5000.0
  • 端口1433和4022已打开
  • 启用了TCP / IP协议

在每个实例上,我创建了一个用户数据库,该数据库承载相应的Service Broker对象。我需要将SBSource.dbo.SourceTable中的记录复制到“目标”实例上的SBTarget.dbo.TargetTable中。请在 随附的档案中找到相应的脚本SB_Source_user_db.sql和 SB_Target_user_db.sql

设置网络访问

如果将实例安装在来自同一域或受信任域的计算机上,则实例之间的网络访问可以基于 Windows身份验证。由于我的虚拟机不属于任何域,因此我使用了基于证书的身份验证。

在每个实例的主数据库中,我创建了用于保护实例之间访问的元素:

  • 数据库主密钥,用于保护证书的私钥
  • 我将证书用作Service Broker端点的身份验证选项
  • 服务代理端点
  • 有权连接到端点的登录名

请注意,每个证书都备份到磁盘上的文件中,并在实例之间交换。

您可以在 随附的归档文件中找到两个实例(SB_Source_master.sql 和SB_Target_master.sql) 的脚本。

创建服务代理特定的对象

SB_Source_objects.sqlSB_Target_objects.sql 在每个数据库中创建Service Broker特定的对象:

  • 具有“格式正确的xml”验证的消息类型
  • 此消息类型的合同
  • 队列
  • 以上队列和合同的服务

在“源”脚本中,我包含了一个为要在其中插入新数据的表创建的触发器(trgSendItemData):

插入dbo.SourceTable(ID,RegDay,RegTime,Code)
VALUES(1,CAST(当前日期为CURRENT_TIMESTAMP),CAST(当前时间为ASCURRENT_TIMESTAMP),“ A123”)

触发器调用dbo.SendItemData过程(SendItemData.sql),该过程仅“在对话中”将新数据发送到目标,即将XML片段存储在发起者队列中并将其移动到目标队列。新数据的格式为XML:

SET @ item _data =(选择ID,RegDay,RegTime,从XML AS中插入的代码,用于XML AUTO,元素);

与网络上目标端的另一个插入操作相比,当前事务中的工作量更少。

每个脚本都包含一个“调试”部分。

在目标端处理消息

至此,如果一切正常,并且您查询目标队列,您应该能够找到已经发送的消息:

-消息以二进制格式存储在队列中
从dbo.ItemQueue2014中选择CAST(message_body AS xml)

结果应如下所示:

<t>
  <ID> 1 </ ID>
  <RegDay> 2017-12-15 </ RegDay>
  <RegTime> 14:38:40.3170000 </ RegTime>
  <Code> A123 </ Code>
</ t> 

但是,故事的另一半仍然缺失。此实现的目标是将插入在“源”表中的记录复制到目标表。XML片段将从目标队列中“挑选”出来,进行解析,数据最终将出现在SBTarget.dbo.TargetTable中。这项工作将由目标队列的激活存储过程完成。  InsertDataAtTarget.sql是目标队列的激活存储过程–每当在队列中插入消息时,它将执行一次。

激活存储过程不能使用任何参数。它仅处理队列的message_body列的内容。如果一切顺利,则此消息是从启动器实例发送的XML片段。在我们的情况下,激活存储过程将解析此片段并将数据插入目标表中。

请注意,这种过程遵循一个模板–接收到来自队列的消息,然后对其进行处理(在这种情况下,将其分析并插入目标表中),如果队列为空(@@ ROWCOUNT = 0),则退出。我为每条消息添加了5秒的WAITFOR,即每5秒将处理一条消息。

对话问题疑难解答

设置Service Broker对话时很容易出错。您需要一种详细的导向态度和很多关注。考虑对象的依赖关系,删除并创建对象(服务,队列,协定,消息类型)。在T-SQL代码中键入服务名称和代理ID时要格外小心,尤其是当引号中包含文字时。例如,如果在dbo.SendItemData代码中输入了错误的目标服务名称,则不会收到任何错误消息。

如果一切顺利,并且消息(XML片段)到达其目的地并由传输队列处理,则ItemQueue在发起方和目标方都应保持为空。但是,如果有消息卡在这些队列中,则应开始调查正在发生的情况。

sys.dm_transmission_queue

根据文档,此目录视图的transmission_status列包含消息仍在此处的原因的描述。当该列确实包含错误消息时,这是显而易见的。例如,假设您在SendItemData存储过程中拼写了目标服务名称。这次,transmission_status消息清除了:“找不到目标服务名称。确保正确指定服务名称和/或已提供路由信息。”。 如果未在启动器上启用代理,则消息将为“代理已在发送者的数据库中禁用。” 

message_body列包含您以二进制格式发送的XML片段。您仍然可以以可读格式检索内容:

从sys.transmission_queue中选择CAST(message_body AS xml)

但是,很多时候您会发现Transmission_status列为空。在这种情况下,请遵循 Remus Rusanu的建议并在目标计算机上运行SQL跟踪。我已经在 脚本归档文件中包含了此跟踪的脚本(SB_trace.sql)和类似的扩展事件会话(SB_xevents.sql) 。

以下是一些我必须运行此跟踪以找出错误原因的示例。

  • 我没有在目标端定义返回发起方的路由。即使不需要将消息发送回启动器,Service Broker仍然需要发送回“确认”,因此必须定义从目标到启动器的路由。.trc文件将包含带有文本数据“此消息无法发送,因为它是重复的”的Broker:Message Undeliverable事件类
  • 我拼错了寄件人的合同名称。消息为“由于找不到<contract name>合同或服务不接受该合同的对话而无法传递此消息。” 
  • 目标队列已禁用。消息为“由于目标队列已被禁用,因此无法传递此消息。队列ID:<QueueID>。”。

SSBDiagnose实用程序

SSBDiagnose是一个命令行应用程序,它将使您知道在用户数据库级别创建的Service Broker特定对象是否存在问题。SSBDiagnose.exe文件的位置取决于SQL Server版本,对于SQL Server 2014 SP2开发人员版实例,它位于C:\ Program Files \ Microsoft SQL Server \ 120 \ Tools \ Binn。

这是一个用法示例:

cd C:\ Program Files \ Microsoft SQL Server \ 120 \ Tools \ Binn
从服务tcp:// <启动程序IP地址>:<启动程序端口> / SBSource / item_list / ItemDataSender2014 -s <实例名称> -d SBSource到服务tcp:// <目标IP地址>:<目标端口> /的ssb诊断配置SBTarget / item_list / ItemDataReceiver2014 -S <实例名称> -U sa -P <密码> -d SBTarget已签约// item_list / ItemContract2014加密已关闭                                                        

我的目标队列已禁用,SSBDiagnose将通过显示错误消息“ Queue dbo.ItemQueue2014 is disabled”来通知我。

sys.conversation_endpoints

由于在dbo.SendItemData中开始的对话永远不会结束,因此sys.conversation_endpoint将为此对话保留一条记录,而dbo.Conversation.DialogId是sys.conversation_endpoint.conversation_handle。如果最后得到几条“ DISCONNECTED_INBOUND”记录,则应 修剪它们

下一步

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1922638