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