Azure SQL Server database Linked Server functionality to connect on-premises and cloud Oracle database

K M 1 Reputation point
2022-09-16T00:39:17.54+00:00

Hi there

Currently our company has on-premises Microsoft SQL Server 2016. This SQL Server instance has Linked Server connection to other on-premises database i.e. Oracle database. We use OPENQUERY inside the Store Procedures to do remote live SELECT query.

We intend to move on-premises SQL Server 2016 instances to Azure with below design options and while also many of the on-premises Oracle databases will migrate to AWS Cloud and few will remains on-premises.

(Prefer Design Option 1) Does Azure Managed SQL Server Instance has linked server connection functionality to connect on-premises Oracle database and also cloud AWS Oracle database? If yes, any supporting document please?

(Design Option 2) We get Azure Virtual Machine (VM) with Microsoft SQL Server on it. Does this Azure VM SQL Server has linked server connection functionality to connect on-premises Oracle database and also AWS Cloud Oracle database? If yes, any supporting document please?

NOTE - As Linked Server feature enables us to query remote database tables as live feeds, we are not taking ADF Pipeline into consideration for any data movement.

Thank you!

Azure Database Migration service
Azure SQL Database

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,886 Reputation points MVP
    2022-09-16T19:50:28.82+00:00

    You can use you can use a Linked Service in Azure Data Factory (ADF) as a Source to extract data from Oracle sources in AWS as explained here. A self-hosted integration runtime on ADF can give you access to SQL Server instances and Oracle serves on-premises, both as source and destination (sink).

    About your Option 1, when creating a linked server on Azure Managed Instance, you are only able to use the SQL provider (driver) to connect to Azure SQL Database, SQL Server, Azure Synapse, SQL serverless or Azure SQL Managed Instance. In addition, You cannot install other providers on Azure Managed Instance. It is a limitation. You cannot have a linked server to an Oracle server on Azure Managed Instance.

    About your Option 2, you can indeed create a linked server to an Oracle server on an Azure SQL VM as explained here. Make sure you connect the Azure VNet to on-premises network as explained here.

    0 comments
  2. K M 1 Reputation point
    2022-09-23T01:30:49.317+00:00

    Thanks for that @Alberto Morillo

    For option 2 - my understanding that hyperlink you have provided are for Azure VM SQL Server to On-Prem Oracle linked server. We are after Azure VM SQL Server linked server connection to cloud AWS Oracle database (aka cloud to cloud linked server). Any help please?

    0 comments