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.

4
  • 1
    Voted to move to Database Administrators
    – Filburt
    Feb 25, 2021 at 12:57
  • 1
    You want to look into linked servers docs.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
    Feb 25, 2021 at 12:58
  • You'll need an On-Premise Data Gateway - I found this tutorial.
    – Filburt
    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
    Feb 25, 2021 at 13:02

2 Answers 2

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.

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? 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. 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: docs.microsoft.com/en-us/azure/azure-sql/database/…. Or maybe I didn't find the info when reading... 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. 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. 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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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