0

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.

enter image description here

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:

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.

Logic Apps: Needs a Virtual Gateway (OR) ASE

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.

Please confirm on these and provide a suggestion.

Thank You.

Rahul
  • 76,197
  • 13
  • 71
  • 125

2 Answers2

1

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 Docs

GeethaThatipatri-MSFT
  • 307
  • 1
  • 4
0

There are 2 solutions which could work for your scenario but even for them to work ,you would need access to on prem SQL server machine access to some extent atleast for one time config and Azure SQL db should be accessible via SSMS installed on on-prem machine.

  1. Using linked server

You can create a linked server ( process explained here https://www.sqlshack.com/create-linked-server-azure-sql-database/ ) on on-prem server and create a agent server job to insert data to azure SQL db table.

  1. Via Python Script

This would need Python installation on on-prem machine. Once installed you can write script to transfer data between on-prem SQL server and Azure SQL db. You can schedule this script again by using an agent server job.

PankajSanwal
  • 956
  • 7
  • 14