复制过滤的数据子集:合并还是事务复制? [英] Replicate a filtered subset of data: Merge or Transactional replication?

问题描述

首先感谢您的阅读.

我需要复制基于联接过滤器的数据子集；筛选器基于与另一个表的联接(Microsoft:使用联接筛选器，您可以将行筛选器从一个已发布的表扩展到另一个表.").这是设置:

I need to replicate a subset of data that is based on a join filter; filter based on a join with an other table (Microsoft:"Using join filters, you can extend a row filter from one published table to another."). This is the setting:

• SQL Server 2012；
• 订阅事务复制的复制源
• 复制必须是一个方向的同步(从发布者到订阅者)；
• 仅一个订户/订阅；
• 交易不多的小型数据集；
• WAN网络.

我到目前为止建立的:

选项1 -创建视图并通过事务复制将其复制到表中.

Option 1 - Create views and replicate those to tables via Transactional replication.

• 优点:不使用触发器

缺点:不会复制键，约束之类的对象

cons: objects like key, constraints are not replicated

选项2 -将合并复制与联接过滤器一起使用，并设置@subscriber_upload_options = 2(仅下载).

Option 2 - Use Merge replication with the join filter and set @subscriber_upload_options = 2 (download only).

• 优点:本机MS功能，所有对象均已复制

• pros: native MS functionality, all objects are replicated

缺点:合并复制使用触发器，批量加载不会触发这些触发器.

cons: merge replication uses triggers, these won't be fired with bulk loads.

这两种方法的结果完全相同.但是，该技术有所不同，例如使用的代理不同.据我了解，合并复制尤其适用于服务器-客户端体系结构，这不是我的情况，但是..

The results of these two approaches are exactly the same. However the technique differs, for example the different Agents that are used.To my understanding Merge replication is especially for server - client architectures, which is not my case but.. it works..

由于结果相同，因此我不确定应该采用哪种方法.我希望您能给我一些要点，以考虑或建议我应该采用哪种方法.

Because of the result is the same I am a bit in doubt which approach I should follow. I was hoping that you can give me some points to consider or advice me in which approach I should follow.

推荐答案

对于此问题中给出的设置，事务复制和合并复制类型都很好. 您唯一需要考虑的是:

For the setup given in this question, both Transactional and Merge replication types are good.
The only things for you to consider are:

• 如果将数据传输到订阅服务器的延迟最小，请选择事务复制".
• 如果需要访问中间数据状态，请选择事务复制".
  例如，如果一行更改了五次，则事务复制允许应用程序响应每个更改(例如触发触发器)，而不仅仅是行的净数据更改.

• If latency for data transfer to the Subscriber should be minimal, choose Transactional Replication.
• If you require access to intermediate data states, choose Transactional Replication.
  For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.

但是，您为应用程序选择的复制类型取决于许多因素.

However, the type of replication you choose for an application depends on many factors.

以下是docs.microsoft.com上相关文章的链接:

Here are links to relevant articles on docs.microsoft.com:

• 复制类型"
• 交易复制"
• 合并复制"

• "Types of Replication"
• "Transactional Replication"
• "Merge Replication"

这篇关于复制过滤的数据子集:合并还是事务复制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！