Connecting ADF to On-Premises Data Sources

Learn how to connect on-premises data sources, particularly MS SQL Server and PostgreSQL, for operations on Azure Data Factory.

Azure Data Factory allows connections to various data sources both on-premises and in the cloud. This lesson will walk you through detailed steps for connecting ADF to the most commonly used data sources, such as Microsoft SQL Server, Oracle, PostgreSQL, and others.

Note: To connect Azure Data Factory to an on-premise SQL Server database or PostgreSQL DB, a live server host of the database is required. In this lesson, we'll learn how to connect SQL Server and PostgreSQL with ADF and the purpose of this lesson is to give users knowledge about this connection for their on-premises data sources.

To connect ADF with external data sources, ensure the following prerequisites are set:

  • An active Azure account and subscription

  • An on-premises data source like SQL Server or PostgreSQL

  • A data factory instance (follow the steps in the previous lesson)

Connecting to on-prem data resources

Connecting to on-premises data resources in Azure Data Factory (ADF) involves setting up a data gateway to establish a secure and reliable connection between ADF and the on-premises data source. The data gateway acts as a bridge between the cloud and on-premises data sources, allowing data to flow securely between them. Once the data gateway is set up, users can create linked services in ADF to connect to on-premises data sources such as databases, file systems, and applications. ADF also supports scheduling and orchestrating data integration pipelines that involve on-premises data sources. Azure Data Factory provides a powerful and flexible solution for connecting to and integrating on-premises data resources into cloud-based data workflows and ensures real-time updates from on-prem to the connected cloud source.

Connecting to MS SQL server

To connect to an SQL Server database, we first need to create an Azure Integration Runtime (IR) that connects to the on-premises SQL Server instance. Here are the steps to create the Integration Runtime:

  1. Open the Azure Data Factory studio page linked here and select the Data Factory created earlier.

  2. Click the “Manage” button to launch the ADF editor.

  3. Now, click “Integration runtimes” and select “New.”

  4. Select “Azure, Self-Hosted” and give the Integration Runtime a name. Leave the remaining settings as their default values and click the “Create” button.

Below are the steps needed to create an Integration Runtime (IR):

Get hands-on with 1300+ tech skills courses.