Cross Database Queries in Azure Synapse, Azure SQL Database, Azure Managed Instance and On Premise SQL Server

1
votes

We are looking at options for moving our on premise SQL Server(s) to Azure and trying to understand whether we will be able to run cross database queries should we have data residing across multiple database technologies both in Azure ( specifically Azure Managed Instance, Azure Synapse Analytics, Azure SQL Database), and in an on-premise SQL Server instance.

We cannot find much information anywhere on whether these are supported and would appreciate if any of ye could help in filling out the table below:

TO-> Azure SQL DB Azure Managed Instance Azure Synapse Analytics On Premise SQL Server
Azure SQL DB Supported through Elastic Search Query (Ref: https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/) ? Azure Data Share supports sharing of both tables and views from Azure SQL Database and Azure Synapse Analytics (formerly Azure SQL DW), and sharing of tables from Azure Synapse Analytics (workspace) dedicated SQL pool. Sharing from Azure Synapse Analytics (workspace) serverless SQL pool is not currently supported. (Ref: https://docs.microsoft.com/en-us/azure/data-share/how-to-share-from-sql) Azure SQL database doesn't support the linked server property so you wont be able to access on prem tables in Azure SQL database and the elastic query in Azure SQL database is to query tables between 2 Azure SQL databases and not On prem. (Ref: https://docs.microsoft.com/en-us/answers/questions/289105/how-can-i-query-on-premise-sql-server-database-fro.html)
Azure Managed Instance ? ? ? Available through the use of Linked Servers (Ref: http://thewindowsupdate.com/2019/03/22/lesson-learned-81-how-to-create-a-linked-server-from-azure-sql-managed-instance-to-sql-server-onpremise-or-azure-vm/)
Azure Synapse Analytics ? ? ? ?
On Premise SQL Server ? ? ? Using a linked server you can query data in an Azure SQL database from an on premised SQL Server (Ref: https://docs.microsoft.com/en-us/answers/questions/289105/how-can-i-query-on-premise-sql-server-database-fro.html)
2
azureazure-sql-databaseazure-synapseazure-sql-managed-instancecross-database
Hi @vavacious, If my answer is helpful for you, hope you can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. – Leon Yue

2 Answers

0
votes

Azure SQL database need elastic query to achieve cross database query. It doesn't support create linked server.

Azure Managed instance has almost same feature with on-premise SQL server, you could use USE statement to execute cross database query. It's same with local SQL Server.

Azure Synapse Analytics also doesn't support cross database query.

Per my knowledge and experiense, I will put ✔ or X in table to express support or not supported. please ref:

TO-> Azure SQL DB Azure Managed Instance Azure Synapse Analytics On Premise SQL Server
Azure SQL DB Supported through Elastic Search Query (Ref: https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/) X, not supported Azure Data Share supports sharing of both tables and views from Azure SQL Database and Azure Synapse Analytics (formerly Azure SQL DW), and sharing of tables from Azure Synapse Analytics (workspace) dedicated SQL pool. Sharing from Azure Synapse Analytics (workspace) serverless SQL pool is not currently supported. (Ref: https://docs.microsoft.com/en-us/azure/data-share/how-to-share-from-sql) Azure SQL database doesn't support the linked server property so you wont be able to access on prem tables in Azure SQL database and the elastic query in Azure SQL database is to query tables between 2 Azure SQL databases and not On prem. (Ref: https://docs.microsoft.com/en-us/answers/questions/289105/how-can-i-query-on-premise-sql-server-database-fro.html)
Azure Managed Instance X, not supported ✔,same with local SQL Server X, not supported Available through the use of Linked Servers (Ref: http://thewindowsupdate.com/2019/03/22/lesson-learned-81-how-to-create-a-linked-server-from-azure-sql-managed-instance-to-sql-server-onpremise-or-azure-vm/)
Azure Synapse Analytics Azure Data Share X, not supported X, not supported X, not supported
On Premise SQL Server ✔,linked server ✔,linked server X, not supported Using a linked server you can query data in an Azure SQL database from an on premised SQL Server (Ref: https://docs.microsoft.com/en-us/answers/questions/289105/how-can-i-query-on-premise-sql-server-database-fro.html)

Please choose the suitable Azure database version according your request.

HTH.

1
votes

AFAIK there is no cross-DB facade that provides a single interface to talk to multiple Databases at the same time. Be it on-prem/in-cloud or SQL-Server/Synapse/MySQL/...

There are individual ways and means by which you can access a single Database from somewhere/anywhere. E.g. accessing an on-prem DB from code in cloud or access a cloud DB from code running on on-prem "servers". List of interfaces available is specific to each "source" and "target" combination.