1

We are using SQL Server 2016 and Oracle 11g (in the process of upgrading to Oracle 12c) . I'm not a .NET developer; therefore, examples will be very helpful.

Our developers are trying to combine data from both MSSSQL (in-house applications) and Oracle (ERP systems) to create a report in .NET (so just reading from both databases). The developers are adamant they need to install Oracle client on the server hosting MSSQL.

From the security perspective (surface area exposure), we are encouraged to minimize surface area exposure as much as possible. And I found a possible way on this blog. And this blog that seems to suggest creating a function as connection string.

Questions:

  1. Is there not an option in .NET (array, etc) to hold data from either MSSQL and / or Oracle in memory for comparison (using employee id) with another database?
  2. Since it's only a read, is it an overkill to have full Oracle client installed on the server?
  3. How are the experts here if you need to pull data from both DBs?

Thank you.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Student
  • 11
  • 2
  • 1
    At some point a machine will have to have the Oracle client. I don't know of a way to connect to Oracle without it. The managed client is more lightweight. – Michael Silver Nov 01 '18 at 19:36
  • To connect to Oracle you need an Oracle client. I suggest this one https://www.oracle.com/technetwork/topics/dotnet/index-085163.html. Don't use a DLL hack mentioned in a 2009 article, just use the ODP.Net driver. To connect to SQL you need a SQL client. It's just that it comes installed by default on windows. – Nick.Mc Nov 02 '18 at 10:14
  • How many records across the two systems will you be comparing? Are they sensitive records? How often do you need to compare them? – Nick.Mc Nov 02 '18 at 10:17
  • What does "create a report in .NET" mean? .RDL reports in a Windows Forms app? Dumping data to an HTML table via ASP.NET? SSRS? Other? – HardCode Nov 02 '18 at 16:06

2 Answers2

0

At some point a machine will have to have the Oracle client. I don't know of a way to connect to Oracle without it.

I can think of two ways to do this. You can link Oracle tables directly in SQLServer. I presume you can then use the SQLServer dialect to query the Oracle table. I'm sure there is a performance penalty for this, especially if joining across RDBMSes.

A second option is to have some sort of table on your SQLServer that has a copy of the Oracle data. Depending on how much data there is or whether or not you retain it, it could be faster than querying the linked table with a join. It could be a permanent table or a temp table. You'll still need to query the Oracle machine and this done as needed or on a schedule.

If you have very little data, you may not even need a table.

You could also have another machine that performs the query of the Oracle DB and moves the data to SQLServer. You wouldn't need the Oracle client on your SQLServer, but on that machine.

Michael Silver
  • 483
  • 5
  • 15
0

I'd look into building a windows service running on a different server from MSSQL server that would utilize the managed Oracle client to ETL data from Oracle to MSSQL server on a schedule which would allow your .NET report to access the report data from one location, your MSSQL server.

Dean Kuga
  • 11,878
  • 8
  • 54
  • 108