Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 610
  • Last Modified:

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
0
John Parker
Asked:
John Parker
  • 4
  • 2
  • 2
1 Solution
 
QlemoC++ DeveloperCommented:
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
 
John ParkerService Quality ManagerAuthor Commented:
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
 
QlemoC++ DeveloperCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
John ParkerService Quality ManagerAuthor Commented:
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
 
Geert GruwezOracle dbaCommented:
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
 
Geert GruwezOracle dbaCommented:
Are you looking for a BI (Business Intelligence) system  ?

http://www.oracle.com/technetwork/topics/bi/whatsnew/index.html
0
 
John ParkerService Quality ManagerAuthor Commented:
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
 
John ParkerService Quality ManagerAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now