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



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.

Click "Next" and check ‘I Accept’.

It will install DataWarehouseMigrationUtility on your desktop.

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.

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

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

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.

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

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.

And, this will create ‘Database Schema’.

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

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

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.

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

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.