How to use BizTalk to sync data between SQL & Oracle in real time,
where I can find the sample? and any built-in adapter?

Thanks in advance!

[ Post a follow-up to this message ]

We do something similar with Oracle and SAP inventory management
systems.  We use ODBC to connect BizTalk to the Oracle database.  We
leverage stored procedures to return specific data that the
orchestration understands on how to extract data from custom tables and
create IDOCs for postings to SAP.

Similarly, you could do the samething by having some table that is
polled by a receive port and when data is detected, it knows to
retreive the records and then insert them into your outbound database.
Similarly you could have a table in the other database another receive
port monitors and when data found, takes the data and performs an
insert into the other database.

Follow?

[ Post a follow-up to this message ]

Thanks.
Since I'm new on BizTalk, does it need strong programming skills to
implement this via BizTalk? or just like create SQL DTS package with
several wizards? where I can any sample or documentation on this topics?


"CranCran77" <crancran@gmail.com>
???????:1139227196.395671.63290@o13g2000cwo.googlegroups.com...
> We do something similar with Oracle and SAP inventory management
> systems.  We use ODBC to connect BizTalk to the Oracle database.  We
> leverage stored procedures to return specific data that the
> orchestration understands on how to extract data from custom tables and
> create IDOCs for postings to SAP.
>
> Similarly, you could do the samething by having some table that is
> polled by a receive port and when data is detected, it knows to
> retreive the records and then insert them into your outbound database.
> Similarly you could have a table in the other database another receive
> port monitors and when data found, takes the data and performs an
> insert into the other database.
>
> Follow?
>

[ Post a follow-up to this message ]

Depends on what you're trying to do with it?

[ Post a follow-up to this message ]

I have one front-end MS SQL db and one back-end Oracle db, I'd like to have
the data of some columns are consistent between these two db in real time.
It's like real-time replication.

Thanks.


"CranCran77" <crancran@gmail.com>
???????:1139258537.757541.197490@g47g2000cwa.googlegroups.com...
> Depends on what you're trying to do with it?
>

[ Post a follow-up to this message ]

Can you setup some level of a database link instead if you need
realtime?

[ Post a follow-up to this message ]

What do you mean "setup db link"? Thanks

"CranCran77" <crancran@gmail.com>
???????:1139393503.401153.277050@o13g2000cwo.googlegroups.com...
> Can you setup some level of a database link instead if you need
> realtime?
>

[ Post a follow-up to this message ]

In Oracle and I believe in SQL server you can setup what is known as a
"link" or virtual table that is actually stored in another database.

So the table MY_TABLE is in the Oracle database but the same table name
is in SQL server and virtually refers to my Oracle table.  When I issue
"SELECT * FROM MY_TABLE", it actually queries the data from oracle but
to the end program, it looks like a SQL server table.

make sense?

[ Post a follow-up to this message ]

For security issue, the front-end and back-end db should not have direct
connection.
(1) if hacker attack front-end system successfully, then Oracle is in
danger and without any back-up system.

For performance and stability issue, the front-end and back-end db should be
totally separate.
(1) if the network connection (between SQL and Oracle) is broken
then the front-end system can not work
(2) if hacker use DOS attack to occupy resource of Oracle db, then
both front-end and back-end system can not work
(3) if back-end Oracle db is out of order no matter due to H/W or S/W
then front-end system can not work too.
(4) The table schema are not same between two db, the Oracle schema
is designed for back-end system and isn't normalized and optimized
to front-end system. Front-end system query back-end db will cause
performance issue in long term.

Our goal is to have these two systems work independently and be each
other's back-up system, not allow two systems crash at same time.
Currently, I use MSMQ to do two ways data sync, but want to migrate
to BizTalk.

Thanks


"CranCran77" <crancran@gmail.com>
???????:1139468340.857349.219160@o13g2000cwo.googlegroups.com...
> In Oracle and I believe in SQL server you can setup what is known as a
> "link" or virtual table that is actually stored in another database.
>
> So the table MY_TABLE is in the Oracle database but the same table name
> is in SQL server and virtually refers to my Oracle table.  When I issue
> "SELECT * FROM MY_TABLE", it actually queries the data from oracle but
> to the end program, it looks like a SQL server table.
>
> make sense?
>

[ Post a follow-up to this message ]

Did you find a solution to do real time exchange ?
Because I have to do the same thing.
If yes, how ?

Geo

"RedHair" wrote:

> How to use BizTalk to sync data between SQL & Oracle in real time,
> where I can find the sample? and any built-in adapter?
>
> Thanks in advance!
>
>
>

[ Post a follow-up to this message ]