Refresh from Oracle

Question

Refresh from Oracle

newbieuser

SSCrazy Eights

Points: 9423
More actions
August 6, 2012 at 7:51 am

#392899

Hi friends,
We need to refresh the data from Oracle database to SQL Server 2005 every hour or so.. We have a linked server from SQL Server database to Oracle.. Can you please advice most efficient ways to do this?
Thanks much

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

newbieuser (8/6/2012)
We need to refresh the data from Oracle database to SQL Server 2005 every hour or so.. We have a linked server from SQL Server database to Oracle.. Can you please advice most efficient ways to do this?

How much data?

Are both servers on the same subnet?

What's the size of the pipe?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

emensj SSC Rookie Points: 46 More actions · Answer 2

Many times the most efficient way to do something is to avoid having to do it at all. In your case, trying to avoid dumping the entire table and loading it again.

Is it possible to only copy new records and/or delete old ones?

Can you use a view that points to the Oracle data?

What else can you tell us about what you are trying to do?

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 3

Thanks for your replies.. we need to migrate 3-4 tables in Oracle to MSSQL and refresh the data on an ongoing basis.

There are about 50000 records in each table and the script should be able to truncate the table first and then refresh the data from Oracle via the existing linked server..

We cannot use a view to point to the Oracle data as they are on different network and every time we query the view, it goes through the linked server and we are trying to avoid that.... Not sure if there is something like materialized views in MSSQl..

Please help..

Thanks a lot

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

newbieuser (8/21/2012)
Thanks for your replies.. we need to migrate 3-4 tables in Oracle to MSSQL and refresh the data on an ongoing basis.
There are about 50000 records in each table and the script should be able to truncate the table first and then refresh the data from Oracle via the existing linked server..
We cannot use a view to point to the Oracle data as they are on different network and every time we query the view, it goes through the linked server and we are trying to avoid that.... Not sure if there is something like materialized views in MSSQl..

For 50K rown in four to five tables I would probably consider doing it the easy way. Just write the T-SQL script on SQL Server side refreshing the tables through the linked server connection then schedule it as a job to automatically execute when appropriate.

As a side note, the closest thing to an Oracle Materialized View offered bh SQL Server is "Indexed View".

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.