Re: replication / caching SQL Server data on Oracle
From: mhoys <matthias.hoys_at_gmail.com>
Date: Mon, 5 Dec 2011 11:58:12 -0800 (PST)
Message-ID: <19435439-4c5d-419a-b0b7-4618f5265fda_at_y7g2000vbe.googlegroups.com>
On Dec 5, 5:51 pm, Walt <walt_ask..._at_yahoo.com> wrote:
> Hi,
>
> For performance reasons, we replicate a small subset of a remote
> database on our Oracle instance (10.2 W2k3). Currently the remote
> database is Oracle and the replication is happening smoothly (and has
> for a decade or so).
>
> We just found out that the remote data is being migrated to a SQL Server
> database, so we'll need to find a different way to do the replication.
>
> The current replication method is rather "stone-age" - once a day we run
> a PL-SQL package that deletes the data, and then re-inserts from the
> remote database via a database link. This has worked for over a decade
> - it's a fairly small dataset, and a one-day latency is acceptable.
>
> One option is to implement a database link to SQL Server and tweak the
> PL-SQL so that the select clause will run on SQL Server. Suggestions for
> other, better approaches cheerfully appreciated.
>
> Thanks
>
> //Walt
Date: Mon, 5 Dec 2011 11:58:12 -0800 (PST)
Message-ID: <19435439-4c5d-419a-b0b7-4618f5265fda_at_y7g2000vbe.googlegroups.com>
On Dec 5, 5:51 pm, Walt <walt_ask..._at_yahoo.com> wrote:
> Hi,
>
> For performance reasons, we replicate a small subset of a remote
> database on our Oracle instance (10.2 W2k3). Currently the remote
> database is Oracle and the replication is happening smoothly (and has
> for a decade or so).
>
> We just found out that the remote data is being migrated to a SQL Server
> database, so we'll need to find a different way to do the replication.
>
> The current replication method is rather "stone-age" - once a day we run
> a PL-SQL package that deletes the data, and then re-inserts from the
> remote database via a database link. This has worked for over a decade
> - it's a fairly small dataset, and a one-day latency is acceptable.
>
> One option is to implement a database link to SQL Server and tweak the
> PL-SQL so that the select clause will run on SQL Server. Suggestions for
> other, better approaches cheerfully appreciated.
>
> Thanks
>
> //Walt
SQL Server used to have DTS (Data Transformation Services) - I'm not sure it's still called this way. Using DTS it was fairly easy to configure and schedule data transfer between SQL server and Oracle tables using OLE DB. You only needed to install the Oracle Net client software on the SQL Server box and define a Oracle Net alias, and then use the DTS wizard to set up the data transfer.
HTH,
Matthias Hoys
Received on Mon Dec 05 2011 - 13:58:12 CST