Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Refresh from Oracle Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 7:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
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
Post #1340616
Posted Wednesday, August 8, 2012 11:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1342043
Posted Thursday, August 9, 2012 12:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 2, 2014 8:56 AM
Points: 6, Visits: 253
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?


Post #1342919
Posted Tuesday, August 21, 2012 5:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
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
Post #1347729
Posted Wednesday, August 22, 2012 7:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1348394
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse