Synchronising data entities from different applications

Question

I'm looking for some feedback on the best approach to a problem I've been tasked with. There are two systems with their own databases which store very similar business entities.

For each entity in question there needs to be a synchronization mechanism in place to make sure that changes in one database are delivered to the other when a change occurs and for the changes to be translated into the destination table structure. This translation means that replication is not an option but I don't want to start writing bespoke triggers or views etc to keep them in sync.

Is this something which BizTalk or a similar product could handle after an initial configuration / mapping process? Also, is Biztalk potentially overkill and are there any other methods which I could employee to achieve this?

Thanks,

Brian.

Answer 1

There is a nifty tool called "datariver" by the Swiss company Sowatec (where I did work a few years ago. I wasn't involved with this product though; just so you know). It's meant to flow data from sources to sinks (just like a river).

The web site is in German but the guys behind it are happy to answer any of your questions in English by mail.

Answer 2

It depends on the size of the "systems" (tables ?) to synchronise.

EAI are the general application to do this. Connecting two systems which can't interact together, effectivly mapping one business object to another one, aplling a map to translate one into another. But such tools (like webMethods for exemple) are entreprise tools, if you only need to synchronise two table from two systems EAI will clearly be overkill.

Anyway the principles can help you. The EAI approach would be to have a generic business object that's match all of properties found in both systems for the business objects you want to syncrhonise. Then you will have to have some sort of map to translate each application specific business objet to and from you generic business object. Your object should not only describe the business data, but also the operation to perform (create, update, delete data).

Then you need a trigger (or two if you want to synchronyze both ways) to detect when a change happen, use the map to transform the data your trigger get to generic object (with the operation to perform at the other end).

And finally you need an "updater" that will take the specific business object and do the right operation in the database (insert/update/delete)

EAI provide connectors to take care of triggering the workflow and updating the database. You will still need to define some mappings in some specific way depending of the EAI used. EAI are a lot more powerfull than juste synchronizing two tables. Connnectors have various type and can interact with various system (proprietary ones), various database, simple format (xml, text) or specific protocols (ftp, webservices, etc.) EAI also ensure that any modification is effectivly commited at the end.

Hope it helps.

Answer 3

You can use 3rd party tool that can work different kind of databases and systems (SQL, MySQL) , like this query tool.

Answer 4

Sql Server Integration Services could be a cheep candidate for solving the problem (can connect to other DBs and data sources that Sql Server). SSIS is part of all Sql Server installations (with the exception of Express).

Answer 5

BizTalk is and would be an ideal solution for this kind of problem.

What BizTalk can do? 1. Define a schema which represents a common business entity, this is essentially all the fields which need to be in sync across several database tables.

1. Define the flow of communication (Orchestrations) and end-points(web services), i.e. which update triggers what changes!

2. Use maps to map the common business entity into specific data elements required by the databases. Note that biztalk has built-in adapters to speed up the development process.

3. Adequate time must be spent in design and of this system the results would be fabulous.

For development purposes refer my articles (google keywords: Biztalk + Karamchetti)