The best technology to synchronize data between different database schemas?

Question

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 1

Replication works well, and if it's two way, it might be your only viable option, since conflict resolution is built in.

If you're going one-way, SSIS or triggers on tables would fine, and would push the data real-time (for triggers) or at whatever interval you want (SSIS). The upside to SSIS would be that it's a background process, whereas triggers could potentially hold up transactions on the supply side while they push the data.

If you're looking to move massive amounts of data, there are other products out there that can do it for you, but if it's not too much data, a solution using SQL Servers tools should do all you need.

Answer 2

From personal experience, I would only use replication if there was no other choice. You have to tear it down for any schema change and it has a tendency to just blow up.

For this, I'd most likely use SSIS. It's fairly easy to build a transformation package and fairly simple to maintain.

Answer 3

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.

Answer 4

Personally I would run away from this nightmare as fast as I could. Since you have not yet bought this online ordering I would suggest that keeping the data in synch with the existing application is a valid reason for not doing such a thing. If you buy this you will eternally regret how mucked up your data will become and how much time and money you spend trying to get things to work properly. This is a disaster waiting to happen. You''ll end up having people order items supposedly in iventory when there are none in the warehouse. Do not do this. This is a guarantee of angry customers and angry managers. Far far cheaper over time to hire some developers to put together your own online ordering that accesses your data base. If they go ahead over your objections, I'd update my resume.