How can I query on-premise SQL Server database from Azure SQL database using stored procedure?

Question

I have a Azure SQL database and it has a stored procedure from where I am trying to join a table which resides in on-premise SQL server database. Essentially, I am trying to query a table which sits in on-premise SQL server's database.

Are there any options to make cross-server queries from Azure SQL database?

Edit: I looked into the linked servers as mentioned in the comments but it seems linked servers do not work with Azure SQL Database Singleton and Elastic pools. They are enabled for SQL Server Database Engine and Azure SQL Managed Instance.

• Filburt: Voted to move to Database Administrators
• Darkwing: You want to look into linked servers learn.microsoft.com/en-us/sql/relational-databases/… that being said, an on premise instance is likely behind a firewall etc. so you will have networking considerations
• Filburt: You'll need an On-Premise Data Gateway - I found this tutorial.
• Filburt: Reversing the direction and creating a linked server from on-premise to Azure SQL is considerably easier if that is an option for your process.

Answer 1

Azure SQL database does not support linked servers or Polybase so you cannot directly virtualize the data from your on prem to your cloud db.

If the requirement is to access the data in the on-prem instance from the Azure SQL database and some delay on the latest data is acceptable, your options are:

• Replicate the data from the on prem instance to the azure db. This is most "automated" way to do it. See https://learn.microsoft.com/en-us/azure/azure-sql/database/replication-to-sql-database

• Use DataFactory, SSIS or other ingestion method to copy the data yourself.

Hope this helps.

• Anirud Thapliyal: Thanks Fernando for your prompt response. In my requirement I don't want to replicate the data of on-premise SQL. I want to access it by joining it in a stored procedure. Any ways to do that?
• Fernando Sibaja: As far as I know Azure SQL database does not support linked servers or Polybase so you cannot directly virtualize the data from your on prem to your cloud db.
• Tibor Karaszi: There are elastic queries for SQL Database in preview, but I don't think you can link to on-prem. The documentation isn't very clear on this point, though: learn.microsoft.com/en-us/azure/azure-sql/database/…. Or maybe I didn't find the info when reading...
• Fernando Sibaja: The elastic pool deals with the problem of horizontal scaling and there has to be a good degree of control over a database cluster to do sharding. I don't believe an on prem instance will ever be supported. My understanding of the question is that it is about data virtualization, not scaling.
• Tibor Karaszi: Was that a reply to me? I didn't mention elastic pools, I referred to elastic queries. Anyhow, I agree that it is unlikely that on prem will be supported for elastic queries.

Answer 2

I am not aware of a process for doing this natively.

You can use other services (I understand that is not what is asked in the question). For example (not a complete example):

• Azure Data Factory
• Azure Functions
• Azure automation (Notebooks)