Copy Data From On-Premise SQL Server To Azure SQL - Azure Private Network

Question

Requirement: I wanted to copy data from a specific table/view residing on a on-premise SQL Server to Azure SQL DB.

Infrastructure: As depicted in below picture. Essentially, the Azure network is directly connected with corporate network over Express Route. Thus it's a pure private network connection; as good as the corporate network itself.

Issue/Question: I know there are multiple approaches present to get this operation done and I am not restricted to use ADF copy Data tool only. BUT, for all of these I see some cavets or extra steps needed to be done as below:

1. ADF Copy Data Tool: Needs a SH-IR and a small MSI package needs to be installed on on-premise machine which hosts the SQL server for registration purpose.
2. Logic Apps: Needs a Virtual Gateway (OR) ASE
3. App Service: If the operation is wrapped in a C# application and I choose to deploy to a Azure Web Apps. Then in-order to connect to on-premise SQL Server we need to setup hybrid connection manager and as in #1 we need to install something in on-premise machine.

For my case, none of these extra steps can be done. essentially, the on-premise SQL Server comes under a different BU and thus I don't have any permission there; except they have given grant to a table/view. Thus, none of these extra shitty steps can be done.

Moreover, as mentioned above; since it's connected over express route as direct connection, As can be seen in above picture, both the on-premise and azure SQL are essentially inside the same corporate network. THUS, I should be able to access them directly without configuring any of these extra steps as mentioned above.

Can someone from MS Azure Team, confirm on these and provide a solution ASAP.

Thank You.

Answer 1

Hi, @Mukherjee, Sirsendu Welcome to the Microsoft Q&A forum, Thanks for posting your query.

As we understand the ask here is to copy data from a specific table/view residing on an on-premise SQL Server to Azure SQL DB and you are on a private network. Please do let me know if that is not accurate.
you can still go with the ADF scenario without a SHIR by creating ADF in a Managed VNET using Private Endpoint. As you already have an ER circuit and have the flexibility to configure the Azure side, can you do this with Azure IR: Access on-premises SQL Server from Data Factory Managed VNet using Private Endpoint - Azure Data Factory | Microsoft Learn

Regards
Geetha