replication / caching SQL Server data on Oracle

Walt

unread,

Dec 5, 2011, 11:51:20 AM12/5/11

to

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

mhoys

unread,

Dec 5, 2011, 2:58:12 PM12/5/11

to

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

Walt

unread,

Dec 6, 2011, 2:34:50 PM12/6/11

to

On 12/5/2011 2:58 PM, mhoys wrote:
> On Dec 5, 5:51 pm, Walt <walt_ask...@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.

>

> 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.

I guess I should have added that we don't "own" the SQL Server database
- we've been given an account and granted select privileges on certain
tables, but no more. Getting the admins of the SQL Server database to
install/configure anything is probably a non-starter. So, whatever
solution we implement needs to be at the Oracle end only (where we can
install & configure pretty much anything we want)

//Walt

onedbguru

unread,

Dec 6, 2011, 6:00:21 PM12/6/11

to

As has been stated, I found that pushing to Oracle from SQLServer via
DTS to be a much more viable option, but, given your description, you
may need to use heterogeneous services. Here is a good place to
start: http://docs.oracle.com/cd/B19306_01/server.102/b14232/gencon.htm

A quick google search will turn up some examples of how to configure
heterogeneous services to pull data from MSS.

CarlosAL

unread,

Dec 7, 2011, 3:14:26 AM12/7/11

to

If you take the Heterogeneous Services route you must keep in mind
that you will not be able to do INSERT...SELECT with a dblink, you
will have to do it row by row with a cursor.

HTH.

Cheers.

Carlos.

CarlosAL

unread,

Dec 7, 2011, 3:43:00 AM12/7/11

to

Oooops!

Sorry, I misread the question.

This was only if you want to insert into SQLServer from Oracle an not
vice-versa.

Cheers.

Carlos.