2

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.

Improve this question
4
  • 1
    Voted to move to Database Administrators
    – Filburt
    Commented Feb 25, 2021 at 12:57
  • 1
    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
    – Matt Evans
    Commented Feb 25, 2021 at 12:58
  • You'll need an On-Premise Data Gateway - I found this tutorial.
    – Filburt
    Commented Feb 25, 2021 at 13:01
  • 1
    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.
    – Filburt
    Commented Feb 25, 2021 at 13:02

2 Answers 2

Reset to default
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:

Hope this helps.

Improve this answer
5
  • 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?
    – Anirud Thapliyal
    Commented Feb 25, 2021 at 14:47
  • 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.
    – Fernando Sibaja
    Commented Feb 25, 2021 at 14:48
  • 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...
    – Tibor Karaszi
    Commented Feb 25, 2021 at 15:02
  • 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.
    – Fernando Sibaja
    Commented Feb 25, 2021 at 15:08
  • 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.
    – Tibor Karaszi
    Commented Feb 25, 2021 at 18:26
0

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)
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.