Continuously synchronize tables between two databases

Question

Hello,

I have had my experience with MSSQL Server somewhat 6 years ago, so I have only basic knowledge of its workings now.

The problem I'm posed with is that of syncing the databases between two live CRMs (NopCommerce and Rainbow Portal-based one if anyone's curious) running on the same DB server. The data I'm interested in is spread out among 7 tables in one DB and 5 in the other one. The idea is to have two web applications with same data with updates in one instantly propagating to the other.

Each database has numerous triggers and stored procedures that are used to keep the data consistent.

I am not aware of all possibilities of SQL Server, so I am open to suggestions as to what is the best and quickest way to achieve the goal. Is it about writing more triggers? Should I create a "watcher" application? Is there some built-in mechanism for that?

Thanks!

Answer 1

You might want to look at SQL Server Replication - http://msdn.microsoft.com/en-us/library/bb500346.aspx in particular Merge Replication

Answer 2

You should look at SQL Replication, and / or using SSIS for the integration ETL and scheduling etc.

Triggers (especially cross DB) can be messy to maintain and debug - you might also consider loading data into a separate (third) staging database, before then propogating the data into your other 2 databases?

(Other alternatives include Synchronous and Asynchronous Mirroring, which would require the entire DB's to be in synch, and log shipping - also entire DB - which would be one way only, typically for redundancy - These aren't likely to be useful for your purpose though)