Hi,
I have been asked to create a biztalk solution which copies new
records (insert/update delete) from one database to other.
Reason is currently there are lots of tables in 1 single database. few
of them are used by sales team and rest are used for administration
purpose. Now they want to separate these databases and replace the
whole application with different-different in house applications. But
want to keep both the databases in sync for time being(I guess they
are using biztalk as mediator because in future if they want to
implement some routing rule they can implement )
Can some one suggest me good solution design for such scenario?
Below is something what I'm thinking right now....
Create triggers on existing tables so whenever records are modified
they are also inserted in appropriate dummy tables.
Create front facing orchestration which will poll to main table and
publish records to destination DB or whatever it will be (for example
I have 2 tables customer and customer profil, Where 1 customer can
have multiple profiles. So when ever customer is modified it will make
entry in dummy customer table and when ever profile is modified it
will make entry in dummy profile & dummy customer table. Now my front
facing orchestration will poll dummy customer information. When record
is found it will process the message and other orchestration will get
all the profile information and create a final message with customer
and all profile info. And it will be updated to destination DB or what
ever it will be.)
Problem here is I'm thinking with very narrow set of table schemas
where actual database has 50+ tables so I'm not sure I can manage the
relation ****p between all the tables and I can cover all the tables
with this kind of logic (don't have any issue if I have to create
multiple front facing orchestrations to cover all the tables).
Also I don't like to have trigger for each table and generate dummy
tables for those. (Note: I can't alter existing database schema so I'm
creating dummy tables through triggers and also will create jobs to
clear the processed records from DB to clear processed records).
Please let me know your thoughts on this design or any other efficient
alternative which you can think of.
