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.
Best Answer
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://docs.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.