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.
For the setup given in this question, both Transactional and Merge replication types are good.
The only things for you to consider are:
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: