Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-05-20
8
Medium Priority
?
607 Views
Last Modified: 2013-05-22
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
0
Comment
Question by:John Parker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 71

Expert Comment

by:Qlemo
ID: 39181941
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.
0
 

Author Comment

by:John Parker
ID: 39181974
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
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 39182057
Going the other way round is possible: Pushing data from Oracle to MSSQL via a linked server. Oracle supports something called "Heterogenous Gateway Service", which is essentially setting up an Oracle Listener with some specific OLEDB or ODBC definitions to get to other databases. For simple cases without need of transaction control and full support of distributed transactions it is free. Of course you would need MSSQL drivers on the Oracle Server, which gets difficult if not on Windows.

That feature of Oracle can also be used to create Excel files, flat files, or whatever ODBC allows.

Besides that, the only way I can think of is to generate e.g. XML files, as you do yourself at the moment. But you would have to leave that to the other side. Oracle supports XML generation, so there shouldn't be many issues with that, but of course it is inefficient with all that lot of overhead in XML files. Maybe CSV files are a better idea?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:John Parker
ID: 39182093
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
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 39184096
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
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 39184147
Are you looking for a BI (Business Intelligence) system  ?

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

Author Comment

by:John Parker
ID: 39185464
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
0
 

Author Closing Comment

by:John Parker
ID: 39187913
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!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question