Incrementally Moving to the Cloud Using Biml Scott

Incrementally Moving to the Cloud Using Biml Scott Currie Varigence Copyright 2016 Varigence, Inc.

Agenda • Azure Data Factory – What is Azure Data Factory? – Scenarios for using ADF with Biml – ADF in Biml – Azure Feature Pack for SSIS • Cloud Data Movement Workflows (in general) Copyright 2016 Varigence, Inc.

What is Azure Data Factory? “Data Factory is a cloud-based data integration service that orchestrates and automates the movement and transformation of data. Just like a manufacturing factory that runs equipment to take raw materials and transform them into finished goods, Data Factory orchestrates existing services that collect raw data and transform it into ready-to-use information. ” https: //azure. microsoft. com/en-us/documentation/articles/data-factory-introduction/ Copyright 2016 Varigence, Inc.

Ehhhh… • Think of Azure Data Factory (as it currently stands) as being like SQL Agent in the cloud – Data movement is pretty useful • https: //azure. microsoft. com/en-us/documentation/articles/data-factory-data-movement-activities/ – Data transformation? Not so much • https: //azure. microsoft. com/en-us/documentation/articles/data-factory-data-transformation-activities/ • Amount of configuration is rather heavy • Most of the development must be done by hand with JSON Copyright 2016 Varigence, Inc.

Let’s take a look at… AZURE DATA FACTORY Copyright 2016 Varigence, Inc.

Scenarios for Using ADF with Biml • Equivalent of simple staging for on-premises / cloud hybrid scenarios • Orchestration of Azure. DW workflows • Orchestration and autogeneration of big data workflows – Hadoop – U-SQL • Failover and surge strategies Copyright 2016 Varigence, Inc.

Let’s take a look at… AZURE DATA FACTORY IN BIML Copyright 2016 Varigence, Inc.

Biml Workflow Copyright 2016 Varigence, Inc.

Azure Feature Pack for SSIS • Connection Managers – Azure Storage Connection Manager – Azure Subscription Connection Manager • Tasks – – – Azure Blob Upload Task Azure Blob Download Task Azure HDInsight Hive Task Azure HDInsight Pig Task Azure HDInsight Create Cluster Task Azure HDInsight Delete Cluster Task • Data Flow Components – Azure Blob Source – Azure Blob Destination • Azure Blob Enumerator – Foreach Azure Blob Enumerator https: //msdn. microsoft. com/en-us/library/mt 146770. aspx Copyright 2016 Varigence, Inc.

Cloud Data Movement Workflows (in general) • Migrating Data Options • Real World Migration Scenario • Migrating Data to the Cloud Copyright 2016 Varigence, Inc.

Migrating Data Options (Azure) • Load Data with Azure Data Factory – Move data from On. Prem to Azure Storage Blob to SQL Data Warehouse • Load data with Poly. Base in SQL Data Warehouse – Load data into Azure Storage Blob using Az. Copy – Load data into SQL Data Warehouse using Poly. Base • Load data with BCP in SQL Data Warehouse – Import data into SQL Data Warehouse using BCP Copyright 2016 Varigence, Inc.

Loading Data Blob Storage Data Factory SQL DWH AWS Snowball Amazon S 3 Amazon Redshift Bucket with Objects AWS Import/Export Copyright 2016 Varigence, Inc.

Load data with Poly. Base in SQL Data Warehouse Blob Storage Poly. Base SQL DWH https: //msdn. microsoft. com/en-us/library/mt 143171. aspx Copyright 2016 Varigence, Inc.

Load data with BCP in SQL Data Warehouse bcp Dim. Date 2 in C: TempDim. Date 2. txt -S <Server Name> -d <Database Name> -U <Username> -P <password> -q -c -t ', ' BCP SQL DWH Data Migration Wizard Copyright 2016 Varigence, Inc.

Aren’t you the guy that lives codes in presentations? SHOW ME THE CODE ALREADY!!! Copyright 2016 Varigence, Inc.

I TRIED THAT, BUT IT WAS SLOW Copyright 2016 Varigence, Inc.

The Need for Speed 10 terabytes of data will take more than 10 days to transfer over a dedicated 100 Mbps connection. Copyright 2016 Varigence, Inc.

Real World Example of Moving to the Cloud Copyright 2016 Varigence, Inc.

Load Data with SSIS (ETL) STUFF Copyright 2016 Varigence, Inc.

Load Data with SSIS (ETL) without STUFF Ado. Net Postgres Ole. Db Copyright 2016 Varigence, Inc.

Load Data Pattern without STUFF UTF-8 10 X Blob Storage Poly. Base SQL DWH Copyright 2016 Varigence, Inc.

New Load Data Pattern without STUFF Copyright 2016 Varigence, Inc.

QUESTIONS? Copyright 2016 Varigence, Inc.

Load Data with Biml Pattern without STUFF UTF-8 Blob Storage Poly. Base SQL DWH Copyright 2016 Varigence, Inc.