September 24, 2018 at 8:32 am
I'm a DBA. I got a request from one of our developers to set up an Oracle linked server. I have no technical issues executing the request, but I'm wondering if this is a generally good practice. Would it be better to have the application either connect directly to Oracle or have an SSIS package pull the Oracle data into SQL Server tables for manipulation? Any citations will be especially appreciated.
Thanks,
Bennett
September 24, 2018 at 11:12 am
Oracle linked servers require installing the Oracle provider software on the SQL Server that will have the linked server. It's not trivial, but it's not that bad.
Then security has to be set up for the Oracle login that will be used, firewall ports, etc.
At my company for instance, we pull SAP data from Oracle into SQL Server so it can be analyzed with various reporting tools. If you have developers who are skilled in SSAS, SSRS, PowerBI, QLIK, Tableau, etc, they probably want to get the data out of Oracle and into SQL so they can play with it. Not to mention the endless spreadsheets. Theoretically, all those consumers could connect to Oracle directly but then your installing and maintaining Oracle drivers all over the place. Office products and just about everything else can connect to SQL Server with no problem.
The Oracle team will have to set up security for whatever data the linked server is supposed to have access to. On the SQL Server side, a linked server can have security settings so that only certain logins will be able to use it. But a linked server open to all users would expand the Oracle security surface, possibly more than they would like. In some cases an application connection may be better from a security analysis perspective. If the data is sensitive, and the Oracle team and/or data owners control the application, they have a better idea of how the data is used than if they allow linked server access.
September 24, 2018 at 11:23 am
We are a mostly SQL Server company, with some applications (SAP mostly) in Oracle. So it makes sense for us to move data from SAP to SQL Server so it can be mixed in with all the other SQL data for reports and dashboards. We even have Simplement Oracle-to-SQL transactional replication set up so financial reports can be generated from the SQL target without interfering with SAP transactions in Oracle.
If you were a mostly Oracle shop, and a developer wants you to maintain an Oracle linked server because they don't want to deal with ODATA queries or IDOCs or whatever else they should be using, I would tell them to stick to Oracle.
Since my experience with Oracle linked servers is dealing with HR and accounting systems, it has all been read-only. I have no idea whether writing to an Oracle linked server has any special gotchas.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply