What are viable options for data synchronization and transformation in sql server 2008?

Question

Our company needs to synchronize two sql server 2008 databases on two different servers.

1. The database schemas are about 50% different so transformation is needed.
2. Synchronization needs to be done in real time.
3. Synchronization needs to be bi-directional.

What are some good practices used for this purpose?

We have analyzed the following solutions and they didn't work for us

Microsoft Sync Framework. This option doesn't work because of the amount of time required to set up the framework (specifically the metadata tables/triggers/sprocs that the framework uses). It is also a newer framework so documentation/examples are scarce and the product might not be as stable as some other solutions.

SQL Server Integration Services. This solution has a learning curve and possible road blocks. It also seems to be too much for just this purpose.

Any help is greatly appreciated.

Answer 1

SQL Server Replication.

Create views in both databases to emulate the 50% of the other side that is different. These, along with the tables that still match will server as your publication source. For the tables that match, just set up two-way replication.

For the tables that do not match use the emulations views as the publication sources. Add a "SourceID" to their base tables to identify what server they were originally created on, and then setup replication filters to insure that no server ever receives a row that it originally created. Publish these views to the other server(s) one-way only. You may need to make these as indexed views in order for it to work (sorry, I can't remember).