Transactional replication with updates on the subscriber?

Question

Summary

We need a "two-way" (bi-directional) replication topology which should be easy to setup and administer, with minimal downtime when changes are required.

Current solution

We have two SQL Server Instances 2008 R2 Standard, on Servers SQL1 and SQL2. Server SQL1 has a database db1 with tables a,b,c,d,e,f,g,h,i,j,k. Server SQL2 has a database db2 with tables a,b,c,d,e,f,g,h,i, x,y,z

db1 serves as the primary database of the application app1, where all transactions are executed (mainly OLTP workload). db2 serves as the replica database of db1 where all the reports and DW tasks are executed (mainly DW workload). db2 has almost all data of db1 (tables a-i) and some extra tables (x,y,z).

We have setup a Transactional Replication (db1_to_db2) with the following (important) options for the publication: - @repl_freq = N'continuous' - @immediate_sync = N'true' - @replicate_ddl = 1

for the published articles: - @schema_option = 0x000001410DC37EDF

As there is little maintenance window available for both databases, with the described setup we can: 1. replicate schema changes to the subscriber 2. add new tables to the publication and use a partial snapshot rather than having to reinitialize the entire subscription

This is working like a charm. But now, a new requirement has come up.

The Problem

Some changes on specific replicated tables, that are updated on db2 directly, should be transferred back to db1. Let's say that tables b on db2 (that references table a and is referenced by table c through FKs), accepts updates on its data that should travel to table b on db1 and not get replicated back to db2. Only Updates are permitted on table b on db2. Not Inserts or Deletes.

Thoughts

We have tried every possible (simple and easily adopted) solution we could think of:

1. Setup merge replication instead of transactional.

   • It does not accept partial snapshots for newly added articles.
   • It adds an extra column which means major changes to the application app1.

   FAIL

2. Transactional replication with updatable subscriber

   • It does not accept partial snapshots for newly added articles.
   • It adds an extra column which means major changes to the application app1.

   FAIL

3. Update db1.b through a (ON INSERT) trigger on db2.b:

   • This is a 2Phase-commit transaction that could fail due to network connectivity issues (which otherwise do not bother us)
   • There is no easy way to explude these Updates from being replicated back to db2.b through the replication.

   FAIL

4. Setup a transactional replication from db2 to db1 with table b as the only article.

   • Again there is no way to exclude these transactions from being replicated back to db2. It would be nice to have something like a 'NOT FOR REPLICATION' option for these transactions...

   FAIL

This is as far as we have gone in search of a solution.

Please help

Please state any idea you might have, taking into account our specific needs. Forgive my being too analytic but I wanted to give every piece of required information you might need.

Answer 1

After all it seems that the answer to my question was #4 (Setup a transactional replication from db2 to db1 with table b as the only article)

There is a not-so-advertised option for the subscriptions (at least not to me) called @loopback_detection, which makes sure that transaction are not replicated back to their originator! This means that you can set up a two-way transactional replication, plain and simple, and accomplish what I was asking for: a subscriber that can update specific tables, so that these changes are replicated to the publisher (and never come back to the subscriber).

You have to pay attention to some options however, to avoid messing things up when setting up a two-way transactional replication: - When adding articles to the "reverse" publication: @pre_creation_cmd = N'none' -- so that the original tables are not dropped/recreated - When setting up the "reverse" subscription you should set: @sync_type = N'replication support only' -- so that no snapshot is required to initiate the subascriber @loopback_detection = 'true' (which is set by default)

These are the steps that need special attention. Other than that, it is a straight forward process.

When you have everything set up, you can test the reverse replication by executing an update on the replica db, while you have the "View synchronization status" windows for both replications open. You will notice that only the reverse replication will log a "1 transaction(s) with 1 command(s) were delivered." message.

PS. I will provide a detailed description of the setup process, shortly.