Re: Biztalk Server 2004 - Database synchronization

I would say that your first inclination should be to see whether something
like Sql Data Transformation Services (DTS) can meet your requirements,
since you essentially have an ETL problem you are trying to solve.

There are cases where these types of problems are well addressed by BizTalk
if quite a bit of "other business logic" (i.e. interaction with outside
services) is needed to enrich the data or what have you. But look at DTS or
a similar tool first and BizTalk second when tying together two databases at
the database level.

You might find this paper helpful:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/bts_2004wp/html/14bc36a8-69a9-48ed-8e4c-1c85202544c0.asp


Scott Colestock
www.traceofthought.net



<rammohanpv@xxxxxxxxx> wrote in message
news:1127208746.312439.97750@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Hi all,
>
> I have a requirement where in I need to synchronize my legacy Progress
> database to SQL Server.
>
> a) whenever a row gets inserted into my Progress tables, I need this
> row to get inserted into my SQL Server table as well. what are the
> various options to access this database from biztalk? Progress adapter?
> Get the changed data onto MSMQ somehow and use MSMQ adapter?
>
> b) The Progress tables are not normalized. Hence, in some cases, a
> column in Progress table maps to 3 columns in SQL Server tables. Also,
> I need to slice the column data from Progress and send the individual
> slices to each of the 3 columns in SQL Server.
>
> c) When a table gets updated in Progress, I want 3 tables in SQL Server
> to get updated. And, all the 3 updates should happen or none should
> happen.
>
> d) I need to have transaction ordering support. For e.g. in Progress,
> table T1 and table T2 get updated. So, in SQL Server, I want the table
> T2 to get updated first and then T1 should get updated.
>
> e) There is a reverse synchronization requirement from SQL Server to
> Progress. Whenever a row in SQL Server gets updated, I need to update
> the corresponding tables in Progress as well.
>
> Can I achieve all these things using Biztalk server 2004? Is Biztalk
> 2004 the right tool for such requirements? Any help in this regard
> would be appreciated.
>


.

Relevant Pages

  • Re: Config Error
    ... error was Management database. ... BizTalk Server 2006 Administration Console ... grant the current user permission to access SQL Server. ...
    (microsoft.public.biztalk.general)
  • Re: SQL SPID (Blocked By -2)
    ... Are you performing any additional database maintenance above the default SQL ... These databases also reside on a shared SQL server with other applications. ... >> Has anyone run in to this SQL SPID problem on the BizTalk ...
    (microsoft.public.biztalk.general)
  • Re: SQL SPID (Blocked By -2)
    ... Are you performing any additional database maintenance above the default SQL Server jobs that get installed like rebuilding indexes or DBCC checks? ... I have seen this happen when the DTA database is extremely large, once we archived off some of the data and got out DTA database down to a resable size the issue cleared up. ... I have called Mircosoft about this issue, and they take me down the path of performance tuning and gathering all the data from SQL server and BizTalk server. ...
    (microsoft.public.biztalk.general)
  • How to narrow down the failure message
    ... I have a nightly job which copies the entire contents from a Progress database to a SQL Server 2005 one. ... Is there any way to make a Failure statement pinpoint the exact item that caused the failure? ...
    (microsoft.public.sqlserver.server)
  • Re: Need Help Exporting a Progress Table
    ... apllication connects to a progress database using a Data Direct SQL92 ... In order to stop using this database and just use SQL Server 2005 I ... export delimiter "," tablename. ...
    (comp.databases.progress)