Cross Database query from Azure SQL database to SQL server on Azure VM

Question

Hi,

Is is possible to run a query from Azure SQL Database to SQL server on Azure VM?
If not what will be the best way to select data from Azure SQL database to a table on Azure VM ?

Thanks,
Sruthi

Answer 1

You cannot use a cross database query from Azure SQL that access data on a table located on Azure SQL VM. However, you can use SQL Data Sync to sync data tables between SQL Server VM and Azure SQL. Data sync can be one direction or bi-directional. You can then consume the data locally as it is synced by SQL Data Sync

If you want to run queries from SQL Server Azure VM involving tables located on Azure SQL, you can create linked servers as explained here, but performance may be an issue with large data sets.

You can also considerate Transactional replication as an alternative.

Hope this helps.

Answer 2

@Sruthi Kathara As confirmed by Alberto, it is not possible to query an on-premises SQL Server from an Azure SQL Database.

If you must query the on-premises SQL Server within the context of a transaction and cannot afford any latency with replication or data sync, you may want to choose Azure SQL Managed Instance instead of Azure SQL Database.

With Azure SQL Managed Instance, you can add a linked server to your on-premises SQL Server Database

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" which might help other community members reading this thread.