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
598 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
  • 4
  • 2
  • 2
8 Comments
 
LVL 68

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 68

Accepted Solution

by:
Qlemo earned 500 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
 

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 36

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 36

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now