0

I have 2 cloud environments AWS and Azure. In AWS, I have a SQL Server instance inside a VPC (192.168.0.0/24). In Azure, I have a VNet (10.0.0.0/24). Both these 2 Virtual Network are connected through Site-2-Site VPN. I have tested connectivity to SQL server on AWS (192.168.0.234) from Azure VM inside Azure VNet. In Azure I have Azure Data Factory instance. From ADF, i need to access the SQL server instance within that AWS VM to run a Data Flow. How should I achieve this?

I already created a site-2-site VPN and test the SQL server conenctivity from Azure to AWS via SQLCmd utility running on Azure VM inside Azure VNet. Created internal load balancer (frontend IP: 10.0.0.4) while pointing traffic to AWS VM as a backend pool. Load balancer doesn't route the traffic to AWS VM SQL server.

This what I'm trying to achieve similarly based on Microsoft Document. Instead On-prem SQL server, I have AWS SQL server behind a VPC. https://learn.microsoft.com/en-us/azure/data-factory/tutorial-managed-virtual-network-on-premise-sql-server

enter image description here

What's already being tried:

  1. Azure Self-Hosted Integration Runtime - This way i can install self-hosted IR on the AWS environment, which allow me to securely connect ADF to SQL Server DB in AWS VNet through SQL connector. But it only support simple data movements. Copy Tasks. ADF data flows does not support.
  2. WhiteListing Azure IPs on AWS VNet - This way i can allow all the Azure ADF used IP ranges in AWS VNet. With this ADF IR environment can access the AWS SQL server DB. But i don't think enabling such number of Azure Public IPs in Prod environment is practical and secure.
Dhanuka Jayasinghe
  • 147
  • 2
  • 7

2 Answers2

1

It seems like you have created a load balancer in Azure.

The backend pool in Azure has some limitations, one being that backend resources must be in the same VNET as the LB.

I suggest instantiating the load balancer on the AWS side, which should solve your connectivity issue.

nickdoesstuff
  • 496
  • 7
  • Thanks for the response Nick. Yes. I read that one too. But how do we connect Azure Data Factory with that Load Balancer? So my data flow can connect AWS MSSQL DB behind that LB? – Dhanuka Jayasinghe Apr 26 '23 at 05:32
  • just to try getting more context here: - why did you deploy the LB? as a Two-Tier application? - Can the AWS design be depicted like: AWS LB ->AWS VM (10.0.0.4) -> SQL server (192.168.0.234)? - getting 2 VPNs between Azure and AWS be an option? – Nico Apr 27 '23 at 12:42
  • Hey @NicoMarino, The microsoft document says in order to connect to sql server on prem, behind a VNet is using a private link. And creating a private link requires a LB on the forwarding VNet. That's why I created a LB. Simply what I need to connect to the SQL server DB in AWS VM through ADF. I added a diagram and the microsoft document link as well. Azure VM (10.0.0.4) put there to test the VPN connection and the LB. – Dhanuka Jayasinghe Apr 28 '23 at 01:15
0

Agreed with Nick comment. You won't be able to achieve such a design with an Azure LB and backend outside of that vnet.

A schema as asked by Nico would be great because i don't see the point of pointing an LB on Azure side to take care of a VM on the AWS side.

I'm not an expert of ADF but is this what you are trying to achieve : https://learn.microsoft.com/en-us/azure/data-factory/connector-sql-server?tabs=data-factory

Worth looking at the integration runtime ? https://learn.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime?tabs=data-factory

alexw-aviatrix
  • 1
  • 1
  • @alexwaviatrox Thanks for the response alex. I have added Microsoft Link to original question on what i'm trying to achieve. Yes, the SQL connector is what I have to add, but in this case the SQL connector destination is behind a VNet. So, we need a special implementation like integrating with VNet. 2nd link you provided was the first option i tried. But it limits the ADF functionaliy to do just the data movements. Copying data from one source to another. I need advance feature in ADF called Data Flows. Which allow us to do more thinks like Joins, Filters, Merge, etc. – Dhanuka Jayasinghe Apr 28 '23 at 01:22