Azure SQL Server SP connect to on-prem SQL Server?

Philip Robinson 1 Reputation point
2021-12-16T02:43:19.43+00:00

I'm fairly new to Azure Data Factory and have run up against the destination datasets, where each sink dataset must point to a single table. I have about 300 tables to handle overall, with various relationships and idiosyncrasies so a parameterised ForEach loop doesn't seem viable; I have additional sources coming soon and don't really want to have to set up potentially 1000 sink datasets.

To manage this I've been attempting to set up a stored procedure in an Azure SQL Server DB that will connect to an on-premise SQL Server instance, query a number of tables in the on-prem DB, and populate/update a number of other tables in the Azure DB. My thoughts are that if the SP is running in the Azure DB then it should be able to access and update those "local" tables, and all I need to work out is how to connect to the source (on-prem) DB from there.

I've got some basic ADF pipeline interaction happening - I've queried an on-prem table and populated a destination Azure table - but this is all within ADF where the source and destination datasets are set explicitly.

Are there any examples of an Azure SP querying the equivalent of a linked server? (OPENQUERY...?)

Thanks,
Phil

Azure FastTrack
Azure FastTrack
Azure: A cloud computing platform and infrastructure for building, deploying and managing applications and services through a worldwide network of Microsoft-managed datacenters.FastTrack: This tag is no longer in use. Please use 'Azure Startups' instead.
75 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. NWiddup-msft 1 Reputation point Microsoft Employee
    2022-04-03T04:41:45.323+00:00

    Hi PhillipRobinson-2008,

    For the situation you have described, generally we would recommend looking to use some kind of metadata-driven approach to your data copy activities. We have a native option within ADF to Build large-scale data copy pipelines with metadata-driven approach with the copy data tool . Otherwise, there are multiple other frameworks/options to implement this kind of metadata-driven approach, such as the ADS GoFast framework or the Procfwk.

    If I've not understood your requirement, please let me know.

    Thanks!

    0 comments