How To Migrate Your On-Premises / Enterprise Data Warehouse Into Azure SQL Data Warehouse

In my previous articles, Introduction To Azure SQL Data Warehouse and Azure SQL Data Warehouse - Why Enterprises Should Now Consider It Seriously, I shared -

  1. What Azure SQL Data Warehouse is
  2. Why Azure SQL Data Warehouse is critical for the enterprise and reasons for the migration

Now, I will share how you can start migrating your data into the Azure SQL Data Warehouse once you have decided to do so. The prerequisites are mentioned below.

Prerequisite

  1. Azure Subscription
  2. Azure SQL Data Warehouse knowledge, refer to Introduction To Azure SQL Data Warehouse and Azure SQL Data Warehouse - Why Enterprises Should Now Consider It Seriously

Once you have decided to migrate your on-premises or existing Data Warehouse, the next thing that comes to mind is ‘What Next,’ or how to import data. Fortunately, we have a couple of options for importing the data.

  1. Data Warehouse Migration Utility (Preview) that can be downloaded from here
  2. Azure Data Factory
  3. Move the data from ADLS to Azure Blob Storage and use PolyBase to import the data.
  4. SSIS

Considering the reasons from the following scenarios

Azure SQL Data Warehouse

Now, we will discuss how to import data from Data Warehouse Migration Utility. By clicking here the download will start. You need to extract and install it.

Azure SQL Data Warehouse

Click "Next" and check ‘I Accept’.

Azure SQL Data Warehouse

It will install DataWarehouseMigrationUtility on your desktop.

Azure SQL Data Warehouse

Open DataWarehouseMigrationUtility, please note that ‘SOURCE TYPE’ has 2 options:-

  1. SQL Server
  2. Azure SQL Database

‘DESTINATION TYPE’ has ‘Azure SQL Data Warehouse’ as shown below.

Azure SQL Data Warehouse

I am choosing ‘Azure SQL Database’ for this demo.

Azure SQL Data Warehouse

Click "Connect" and look over ‘Migration Settings’ and click ‘Save’.

Azure SQL Data Warehouse

Select ‘Your Database’ database and click ‘check Database Compatibility’. In case of any compatibility issues, it will give ‘Database Compatibility Report’ in Excel sheet, like this.

Azure SQL Data Warehouse

Type of errors

  1. Code Errors – Errors in code like ‘Stored Procedures’ and ‘User Defined Functions’
  2. Object Errors – Errors in Schemas etc...

Fix the related issue and click ‘Migrated Selected’. It will show the objects/ tables needed for migration. Select all or some as per your business need

Azure SQL Data Warehouse

Round Robin – will distribute data equally in the Compute Node. I always choose this as I don’t have to explicitly distribute it. We could check Skew Status, which helps us with ‘Distributed options’.

Once you are done, click on the "Migrate Schema" as shown below.

Azure SQL Data Warehouse

And, this will create ‘Database Schema’.

Azure SQL Data Warehouse

After the Schema gets generated, click "Run Script".

Azure SQL Data Warehouse

And you have to give your SOURCE, i.e., SQL Server credentials to connect.  It will apply the script like this.

Azure SQL Data Warehouse

Once the schema is applied successfully, the only remaining thing is to migrate the data. So, click "Migrate Data" at the extreme right of the page.

Azure SQL Data Warehouse

Click "Generate". It will generate a BCP Package at your specified directory.

Azure SQL Data Warehouse

There are two packages that need to be run in sequence, as shown below.

  1. Run Export Package – Export Data from SQL Instance to the place where we are running it.
  2. Run Import Package – Import Data into Azure SQL Data warehouse

You have just migrated your ‘On-Premises/Enterprise Data Warehouse’ to  ‘Azure SQL Data Warehouse’ successfully.


IotCoast2Coast
Improving the World of Tomorrow :- We offer application development and support services.