Using Azure Data Factory to Migrate Data

Quite simply the objective as follows: Move data from Azure SQL Database to Azure SQL DW via Azure Data Factory v2 (ADF).

Overview

Before I begin, what exactly is Azure Data Factory? At an extremely high level it is a managed cloud service that is built for complex hybrid extract-transform-load (ETL), extract-load-transform (ELT), and data integration projects. (Currently version 2). I am going to use this tool to load data into an Azure SQL Data Warehouse. How does Data Factory do this? A process called Copy Activity.  Copy Activity goes through the following stages to copy data from a source to a sink.

sinks

Read more about it here: https://docs.microsoft.com/en-us/azure/data-factory/copy-activity-overview

Enough of the Theory – Let’s Get Practical

This process assumes 3 things. You have an Azure SQL Database (source), an online Azure SQL DW (target) and a storage account. Under common tasks within the data warehouse you will see the Load Data option.

letsload

As it states below – ADF is a “Cloud-based data integration service that orchestrates and automates the movement and transformation of data. Use ADF to load data from various data sources and monitor your ingest, transform, and publish steps through a rich visual interface”

adfv2

Soon as you click ADF – Azure Data Factory you will be asked to create one.

create

Once created naturally we need the load data option.

up

Now it is a case of working through the wizard, more specifically the below steps 1-6.

steps

  1. Properties – where you set the schedule.

onceoff

2.  source – Have a browse through, many connectors are available.

souce

Here I select SQL Database hence need the tables.

tables

3. Destination – The Azure SQL DW and mapping where required.

dwh

mappinh

4. Further settings around performance – using polybase.

poly

5. A nice summary.

summary

6. Deploy – watch those pipelines get created.

pipeline

Once completed you will have an Azure SQL DW with data that has come from a SQL Database.

dwhload.JPG

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s