The best technology to synchronize data between different database schemas?
I have an existing SQL Server 2005 database that runs our accounting/inventory application. We are looking at using a new on-line ordering framework - which has it's own database.
If we use this new framework, we will need to transfer the on-line ordering data (inventory, prices, orders, customers) - almost realtime - to and from, our existing inventory database. The transfer of data doesn't have to be real-time, but it has to be quick. Both databases will be in SQL Server.
So my question is... what is the best way to transer data back and forth between two databases, with have different schemas?
Replication? SSIS? What would you suggest, and why?
Any help would be appreciated!
Answer
The Business Rules are the Hard Part
One-way sync? Two-way sync? Real-time push? Nightly updates? Dump and reload? Compare and update? Conflict resolution? Which side wins? Push read-only info one way, and order info the other way? What about changes/cancellations/etc? Do order statuses get pushed back?
You can see where I'm going here. Technology is a secondary question.
Because of the business rules issue, and because the two systems have different schemas (and different purposes), this isn't a standard data move, and most of the "standard" answers (replication, log shipping, etc) are off the table.
There are frameworks out there designed to help with this, like Microsoft BizTalk or Scribe Insight. These are cumbersome and expensive, though.
It isn't too difficult to create a custom queue-ing system either based on SQL triggers, or scheduled pushes (depending on your needs) in C# or your favorite language. That's probably the route I would go. It would probably involve a third "transfer" database to hold the queue of changes made by one side, and a module to apply the business rules and push the data to the other.