I'm trying to query an on-premises SQL Server database from an Azure SQL database. I understand that the elastic query feature allows you to query across multiple databases in Azure SQL Database. But, it doesn't work with querying an on-premises database. Is there any way to do this?
0
votes
1 Answers
0
votes
Elastic database queries work between Azure SQL Databases and to query Azure SQL Data Warehouse only. From an architecture point of view on the scenario you describe, Azure would be relying on a server outside of its infrastructure to satisfy a query, if elastic queries were allowing creating external data sources to on-premises SQL Server instances. A better architecture may be replicating the on-premises database to a hub database on Azure SQL using SQL Data Sync and then create elastic queries using that hub database. Even from a performance point of view makes more sense, since queries won't be executing against your on-premises databases and latency will be less a factor.
Another option you have is to consider using Azure Managed Instance with allows you to create Linked Servers that can connect to on-premises databases as you can read here. Azure Managed Instance supports running on a private VNet, from which it's reasonable to connect to on-premises SQL Server instances. Azure SQL Database would have no way to communicate with your on-premises servers.
