Post reply

Query Oracle DB from Azure SQL

  • May 6, 2023 at 1:39 am

    #4185042

     

    Folks,

    I'm investigating the possibility of migrating from Azure VM with SQL to Azure SQL Managed Instance but have hit a roadblock.  We depend heavily on data from Oracle DB accessed through Linked Servers, both for queries and also direct imports to internal tables.  My understanding is that Azure SQL does not support OLE DB connections to non-sql systems so seems to be a  major obstacle.

    I'm curious to see if others with the same or similar limitations have overcome this hurdle, and if so what was the solution.  I'm currently looking into Azure Data Factory which may be used to solve the issue of data import to our systems, but we are still faced with inability to run live queries to the Oracle system (which we don't own).

    Any insights appreciated.

    Thanks in advance,

    RGB

  • May 6, 2023 at 5:56 pm

    #4185335

    I have not needed to do this but can think of two options to get live Oracle data; neither of them are particularly appealing.

    Option one:

    1. Create web services to access the live data you need from Oracle.
    2. Call the web services from the CLR in the SQL Managed Instance.

    It sounds time consuming and might not perform very well.

    Option two:

    Query the Oracle server directly from the application; any joins to SQL Server would need to be done in the middle tier. May be the best approach if you ever want to move to Azure SQL database which I do not think includes the CLR.

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply