question

AnirudThapliyal-9057 avatar image
1 Vote"
AnirudThapliyal-9057 asked NandanHegde-7720 commented

How can I query on-premise SQL server database from Azure SQL database?

We have an existing on-premise SQL server database. We are trying to build a ASP.NET core web app and deploy on Azure as App Service. Along with it we will also have a new Azure SQL database singleton.

How can I query our on-premise SQL server database table from Azure SQL database through a stored procedure?
Should we use Azure SQL managed instance instead of Azure SQL database singleton for this?


Thanks In Advance!

sql-server-generalazure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

NandanHegde-7720 avatar image
1 Vote"
NandanHegde-7720 answered NandanHegde-7720 commented

Hey @AnirudThapliyal-9057 ,
Is is not possible to directly query an On prem table in Azure SQL database but vice versa is possible.

Azure SQL database doesn't support the linked server property so you wont be able to access on prem tables in Azure SQL database and the elastic query in Azure SQL database is to query tables between 2 Azure SQL databases and not On prem.

But the vice versa of querying the Azure SQL database table in On prem is possible via the concept of linked server .

In case if you want the on prem table in Azure SQL, you can either use the concept of data sync to sync data between on prem and cloud or you can use Azure data factory to copy from on prem to Azure SQL.

But unfortunately based on my understanding as of now, it is not directly possible to have your requirement

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @NandanHegde-7720 for prompt response.

If I use Azure SQL managed instance instead of Azure SQL database singleton, will I be able to achieve my requirement?

0 Votes 0 ·

Hey @AnirudThapliyal-9057 ,
Theoretically it is possible via Azure SQL managed instance.
Azure SQL MI would reside in a vnet and in case if there is accessibility across the SQL mi vnet and your on prem vnet, you should be able to create a linked server. This is more of a networking aspect to allow communication across onprem and SQL mi vnet.
Just on a side note, SQL mi is very costly and not recommended just for a sake of 1 database.
It would be better to think of syncing some data ,though your call :)

Please mark it as answer in case if it was helpful as it might benefit other community members as well

1 Vote 1 ·