Load data from SQL Server On-Prem to Azure SQL Database using Azure Data Factory

...
Kartheek T
November 16, 2018
...

In my previous blog on Data Factory, I have explained how to extract data from Azure SQL Database (Source) to Azure SQL Database (Destination) where I uploaded the output of On-Premise data into a Blob Storage and then use the Azure Data Factory to load data from Blob Storage to Azure SQL Database.

There are several ways to import and export data from Azure SQL Database. In this blog, I will be sharing insights on how to load data from SQL ServerOn-Premise using Integration Runtime (IR) software to Azure SQL Database.

With an On-premise Integration Runtime (IR) Data Gateway, it is more comfortable, secure and less complicated way to move corporate On-Premise data into the cloud.

What is Integration Runtime (IR)?

The Azure Integration Runtime is the compute infrastructure used by Azure Data Factory to provide the following data integration capabilities across different network environments.

  • Data movement: This helps in moving data from data stores which are in public network to data stores in a private network (virtual private network or on-premise). It also provides support for column mapping, format conversions, built-in-connectors and data transfers.
  • Activity dispatch: This helps in monitoring activities running across Azure Machine Learning, Azure HDInsight, Azure SQL Database, SQL Server, and more.
  • SSIS package execution: This helps in execution of SQL Server Integration Services (SSIS) packages in a managed Azure compute environment.

The Integration Runtime or IR is the compute infrastructure used by ADF V2. It determines where your activity runs on or gets dispatched. The Data Factory offers following types of Integration Runtime.

  1. Azure: Public Azure (PaaS) network, with accessible public endpoints
  2. Self-hosted: On-premises machine or Azure VM (IaaS) inside a private network (VNet)
  3. Azure-SSIS: Provisioned in both public Azure or private Azure VNet network. On-premises data is accessible if you connect your VNet to your on-premises network.

The usage of these IR depends on data integration capabilities and the network environment requirements.

The below diagram shows how the different IR can be used in combination to offer network support and data integration capabilities.

Azure Data Factory

Image courtesy – Microsoft

Please follow this step-by-step guide to learn how you can load data from SQL Server On-Premise to Azure SQL Database using Azure Data Factory.