Azure SQL Server Instance with Linked Server features to Oracle database on-prem and cloud

Question

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.

(Prefer Design Option 1) Does Azure Managed SQL Server Insance has linked server connection functionality to connect on-premises Oracle database and also cloud AWS Oracle database? Our understanding it does not provide this feature. But if yes, any supporting document please?

(Design Option 2) We get Azure Virtual Machine (VM) with Microsoft SQL Server 2022 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!

Answer 1

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.

Answer 2

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 from Azure cloud to AWS cloud linked server). Any help please?