SQL Server to Oracle via Oracle and Back..

In many environments SQL Server Data Warehouses and Analysis Services Databases need to extract data from remote Human Resource and Finance ERP and other systems where Oracle is used for large scale OLTP databases. Connecting to remote Oracle Database Hosts for Data Extracts via OLE DB based Linked Server Objects in SQL Server can be slow. Increased connect times to remote ERP System Host Oracle Databases often result in greater risks for timeouts and related problems when using OLE DB Linked Server Objects in SQL Server.

Over the last few years I have found that using a Native Oracle to Oracle connection greatly improves speed of extracts in such an environment - usually in the neighborhood of 50-60%.

One method is to install an Oracle Database on the same server housing SQL Server and extract the data directly from the ERP Oracle Database directly to the Oracle Database on the Data Warehouse/Data Mart Server which also houses SQL Server. The Oracle Database footprint can be small from a memory standpoint and disk space only needs to be large enough to hold the staged data. SQL Server can then be used to read directly from staged data in the Local Oracle Database.

SQL Server Linked Server Objects to remote Oracle via OLE DB contain large performance and network reliability issues. The method above introduces an additional step in the Data Warehouse/Analysis Services Repository update process, however total processing time will remain about the same in most instances wherein a remote Oracle Database Host is involved.