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

Sruthi Kathara 26 Reputation points
2021-08-26T17:28:56.823+00:00

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

Azure SQL Database
{count} vote

Accepted answer
  1. Alberto Morillo 33,251 Reputation points MVP
    2021-08-26T17:50:31.057+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. KalyanChanumolu-MSFT 8,316 Reputation points
    2021-08-30T05:59:29.177+00:00

    @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.

    0 comments No comments