I have a very straight-forward data warehousing task; take some of the data from a "main" database, transform it and get it into a "remote" database.

If necessary I can certainly create the routines to do this, schedule them and that would work.

I would prefer to have more of a real-time interface and am not sure what the best tool for the job would be?  Is Replication the best way, Sync-Framework or even something like writing triggers on the main database to do the transform/transfer.

Any thoughts would be appreciated.

Hi Steven,

Just as you said, Microsoft offer multiple synchronization solutions, such as Microsoft Sync Framework and Merge replication. The choice of synchronization solution is depend on the requirement. For example, IT administrators may look for more of a packaged solution that is easy to install and administer. ISVs and developers may be interested in a more embeddable and customizable solution.

The Microsoft Sync Framework can be used to synchronize data stores whether they are databases or even a file store whereas replication is about synchronizing SQL databases. These two synchronization mechanisms also target different audiences. Sync Frameworks targets the developers and provides more flexibility. It also imposes more coding as in the case of the non-database store, the developer must code the providers. Replication targets the DBAs and furnishes out of box procedures and monitors.

Please based on your requirement to make a better choice. For more information about Sync Frameworks versus Replication, please refer to the following documents:
https://msdn.microsoft.com/en-us/sync/cc470041.aspx
http://blogs.msdn.com/b/msfedtech/archive/2010/06/30/sync-frameworks-versus-replication.aspx
http://blogs.msdn.com/b/sqlservercompact/archive/2009/11/09/merge-replication-vs-sync-services-for-compact.aspx

Thanks,
Katherine Xiong