I am searching for some feedback on a great way to some problem I have been assigned with. You will find two systems using their own databases which store much the same business organizations.
For every entity under consideration there should be a synchronization mechanism in position to make certain that alterations in one database are shipped towards the other whenever a change happens but for the changes to become converted in to the destination table structure. This translation implies that replication isn't a choice but I'd rather not start writing bespoke triggers or sights etc to ensure that they're synchronized.
Is a thing that BizTalk or perhaps a similar product could handle after a preliminary configuration / mapping process? Also, is Biztalk potentially overkill and are there more techniques that we could worker to do this?
It is dependent on how big the "systems" (tables ?) to synchronise.
EAI would be the general application to get this done. Hooking up two systems which can't interact together, effectivly mapping one business resist a different one, aplling a roadmap to translate one into another. But such tools (like webMethods for exemple) are entreprise tools, should you only have to synchronise two table from two systems EAI will clearly be overkill.
Anyway the concepts will help you. The EAI approach is always to possess a generic business object that's match all qualities present in both systems for that business objects you need to syncrhonise. Then you'll have to possess some kind of map to translate each application specific business objet back and forth from you generic business object. Your object shouldn't only describe the company data, but the operation to do (create, update, remove data).
You will want a trigger (or two if you wish to synchronyze for both) to identify whenever a change happen, make use of the map to change the information your trigger reach generic object (using the operation to do in the other finish).
And lastly you'll need an "updater" which will go ahead and take specific business object and perform the right operation within the database (place/update/remove)
EAI provide fittings to consider proper care of triggering the workflow and upgrading the database. You still have to define some mappings in certain specific way depending from the EAI used. EAI are much more powerfull than juste syncing two tables. Connnectors have various type and may communicate with various system (proprietary ones), various database, simple format (xml, text) or specific methods (ftp, webservices, etc.) EAI also make sure that any modification is effectivly commited in the finish.
Hope it will help.
Sql Server Integration Services might be a cheep candidate for fixing the issue (can connect with other DBs and data sources that Sql Server). SSIS is a component of Sql Server installations (except for Express).
There's a great tool known as "datariver" through the Swiss company Sowatec (where Used to do work a couple of years back. I wasn't associated with the product though so you realize). It's designed to flow data from sources to sinks (as being a river).
The site is within German however the men behind it are pleased to reply to all of your questions in British by mail.
BizTalk is and could be a perfect solution with this type of problem.
What BizTalk can perform? 1. Define a schema which signifies a typical business, this really is basically all of the fields which have to be synchronized across several database tables.
Define the flow of communication (Orchestrations) and finish-points(web services), i.e. which update triggers what changes!
Use maps to map the most popular business into specific data elements needed through the databases. Observe that biztalk has generated-in plugs to accelerate the expansion process.
Sufficient time should be put in design as well as this technique the outcomes could be fabulous.
For development reasons refer my articles (google key phrases: Biztalk + Karamchetti)