Replicate a filtered subset of data: Merge or Transactional replication?

1.2k views Asked by At

First of all thanks for reading.

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;
  • replication sources on a subscription of a transaction replication
  • replication needs to be one direction sync (from publisher to subscriber);
  • only one subscriber/subscription;
  • small dataset with not many transactions;
  • WAN network.

What I established so far:

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

  • pros: no triggers are used,

  • cons: objects like key, constraints are not replicated

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

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

1

There are 1 answers

0
Milan On

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.

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