Link to home
Create AccountLog in
Avatar of John Parker
John ParkerFlag for United States of America

asked on

Opinions Wanted: What's The Best Way To Get Data From Oracle 11g Database to SQL Server 2008 R2 Database

Experts,

I have an opinion question for you.  Multiple opinions welcome!  My site is preparing for a new control system to be deployed later this year.  This system uses an Oracle 11g database.  This database houses lots and lots of data that we on the business side need for analysis and reporting.  (We're talking probably over a million rows of data across 10 or so tables per day.)  Accessing the production database directly is not allowed.  I am currently writing requirements for getting a daily extract of the data we need, so I can insert it into my reporting database, which is SQL Server 2008 R2.  Both databases exist on the same network.

In the current environment I have direct access to the production Oracle 9i database. I run a query to pull the data I need from a particular table, then transform that data into simple XML, then pass that XML to a stored procedure on my SQL Server that parses it into tabular form, and inserts that data into the appropriate table.  This whole process takes several hours.

My question is this: if you were able to write the requirements for this new model that's coming, how would you design the transfer to happen?  I don't know Oracle database capabilities very well, and my knowledge of SQL Server, while growing, isn't complete yet... I don't know really anything about SSIS, but am open to solutions that would incorporate this.

Clarification:  I don't need super detailed answers here... just give me the general route you would go, and why you feel it's the most appropriate.  :-)

Thanks for your opinions!

John
Avatar of Qlemo
Qlemo
Flag of Germany image

Using remote queries in MSSQL would be my choice. Create a linked server on the MSSQL machine (requires to install Oracle drivers there) pointing to the Oracle DB  - let's call it OracleProd -  and use that to specify tables:
  insert into reportings select * from OracleProd...basetable
(the multiple dots are there intentionally). However, that's good only for simple selects, as joins and such will most likely be performed on MSSQL side.
If it gets more complex, and you need to execute the query on Oracle by any means, OpenQuery comes handy:
 insert into reportings select * from openquery(OracleProd, 'select ....')

Using SSIS for this is similar, but allows for flow definitions (what to do with errornous rows, ...) and complex data type convertions.
Avatar of John Parker

ASKER

Qlemo,

Thanks for the thoughts!  I tried the "Linked Server" model in our current environment, and wasn't really happy with the performance.  However, that was probably because I was essentially creating "views" of the data with the query written from the SQL Server side, which to your point are performed on the SQL Server side.  Possibly having the vendor create the views I need on the Oracle side would alleviate that bottleneck...  Good thoughts!

One possible road block...  Since I'm not going to be allowed direct database access in the new environment (changes to comply with new security policies) I will go out on a limb and guess they are not going to allow me to set this up as a linked server.

Experts (and Qlemo),

Any ideas on best solution that doesn't require direct access to the Oracle server?  Basically they want to "Push" data to me, rather than me "Pull" data from them.

Thanks!

John
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Qlemo,

Great info on the Oracle Gateway Service!  That sounds promising.  I'll talk that over with the vendor and see if that was the direction they would recommend as well...

I appreciate your opinions!  I'll give this question another 24 hours to see if anyone else wants to chime in, then I'll close and award points.

Thanks!

John
why have 2 sets of same data for only reporting ?

there is always direct access to the database at one point
> how does one extract (or even insert) the data without access ?

btw, having 2 databases will require all items like storage, tuning, etc to be done on both those databases with some added complexity of cross-db communication

i'd advise to work together with the dba to see what you need for reporting direct from the oracle db
dba's usually are the ones who have to look at why a report is running slow
off course by that time it's usually an urgent matter
Are you looking for a BI (Business Intelligence) system  ?

http://www.oracle.com/technetwork/topics/bi/whatsnew/index.html
Geert_Gruwez,

The two databases are not both for reporting purposes.  The Oracle system supports an automated facility, and is intensely business critical.  This is why any direct access to these databases by anyone but the DBAs will be prohibited.  This system, however, does log a tremendous amount of data regarding it's activities that are necessary for external, ad hoc and custom reporting.  This is why this key data will be pushed out to my Reporting DB.  Doing so gives my group full control over the reports needed and the analysis required as requested.  This piece of the model is locked in... what I'm trying to determine is the best way to handle the push of data from the Oracle System to my SQL Server DB.

Thanks for your thoughts,

John
I was hoping for more possible solutions, but Qlemo's suggestion seems logical, and probably the most likely route I'll go.  Thanks for your input!