CODEWITHSUNDEEP
sql-serverazureazure-synapse
JAD
JAD

Reputation: 2170

How to connect to on-premise SQL Server from Azure Synapse

We are currently creating a new Azure Synapse Analytics environment to house our data. For this, we need to connect from Synapse to our on-premise SQL Server databases.

These databases are not reachable from the open internet, they are only accessible from within our own network. For our other applications on Azure, we have setup a hub-spoke model network, where the hub connects to our internal network, and VNets in the spoke are peered to the hub. This way we have been able to connect to on-premise database servers in other applications like AKS.

However, this doesn't seem to work for Azure Synapse. So how to do this then?

What I tried so far

The Azure Data Factory docs contain the following page outlining a guide for making this connection, using a private link service, a load balancer and a couple of Virtual Machines configured to forward traffic on specific ports towards the server using iptables.

I have implemented this solution, but I don't really trust this. Most of all, the iptables configuration doesn't seem to persist when a VM reboot. This doesn't inspire a lot of trust in this solution, since this doesn't feel like something that should be used in production as is.

At the same time, I can't imagine that connecting to an on-premise database server is such a stretch, that there are no better solutions around. We can't be the only one stumbling onto this problem.

So my question, is there really no other, better, way to set up this connection?

Upvotes: 4

Views: 6828

Answers (2)

Malcoln Dandaro
Malcoln Dandaro

Reputation: 59

We have the same problem's, and basically we only found 3 ways to copy data from on-premises SQL server to Synapse:

  1. In theory you could open all Microsoft Synapse public IP's in your on-premises firewall, then you can use Azure IR (It will be open to every Azure Synapse in the same region). List of IP addresses
  2. Install Self-Hosted IR in a on-premises computer
  3. Install a Self-Hosted IR in a VM in Azure which is in a VNET that have access to your on-premises network (VPN/Express Route)

Upvotes: 3

Utkarsh Pal
Utkarsh Pal

Reputation: 4544

Assuming your on-premises SQL Server running on Windows machine, you can go with Self-hosted Integration Runtime (IR) to connect Azure Synapse Analytics with on-premises SQL Server.

A self-hosted integration runtime can run copy activities between a cloud data store and a data store in a private network. It also can dispatch transform activities against compute resources in an on-premises network or an Azure virtual network. The installation of a self-hosted integration runtime needs an on-premises machine or a virtual machine inside a private network.

The considerations for using the self-hosted IR is mentioned here.

You can create and configure the self-hosted IR either through PowerShell or UI. Though UI is the easiest method to deploy it. You can refer the steps given in Microsoft official document to do the same using UI.

Upvotes: 4

Related Questions