Let's Move 'On-Prem' Data Warehouse To 'Azure SQL Data Warehouse' With Redgate Data Platform Studio (DPS)

troduction

In this article we will learn how to migrate ‘On-Prem’ data warehouse to the Cloud, specifically Azure SQL Data warehouse. I will share the lesson learned and some do’s and don’ts from my experience that may be helpful.

Let’s talk about a ‘Great Tool’ – ‘Redgate DPS Gateway’ which worked perfectly fine for real time data migration challenges for migration of 1000 Tables having 10 GB Data.

We will walk through the ‘REAL TIME’ example. This article is not referring to,

• Adventure Works Sample Data Warehouse migration
• A couple  of demo table migrations

This is a real time extensive migration example.

Prerequisite

1. SQL Server on-premises installed
2. Your Enterprise or Demo database available on SQL Server on-premises
3. An Azure SQL Data Warehouse account, if still don’t have Please Create ‘Azure SQL Data warehouse here’

Still if you have some questions on Azure SQL Data Warehouse like,

Why Enterprises Should Now Consider ASDWH Seriously please read here

Background

Most recently, I got an opportunity to migrate the ‘On-Prem’ Data warehouse to cloud at ‘Azure SQL Data Warehouse (ASDWH)’. This article is from that standpoint of view.   

Getting Started

I am sure that you already have an Azure SQL Data Warehouse (ASDWH), so please LOGIN there and go to the ASDWH

Under Common Task, please Click Load Data as shown below,

After Clicking Load Data, you would see below ‘Load Data’ screen appeared

Here we have ‘Two Options’

1. redgate Data Platform Studio
2. Azure Data Factory

For Azure Data factory details, please wait until my next article.

So now let’s click on redgate-Data Platform Studio. Please login with Azure login credentials and a new account will be created for you to login to the Data Platform Studio (DPS).

After login in the DPS, Click the ‘START NEW IMPORT’ button.

I am very happy to see ‘Jonathan’ – an online assistant, however, he was not responding except  for the first default message, seems he is vacationing

If you are a first time user with DPS, you need to create a ‘Gateway’ to connect SQL ‘on-prem’ Data warehouse to ‘Azure SQL Data warehouse’ like this,

Click the Download link to download the DPS Gateway installer,

Once downloaded, please install DPS gateway. After installation, the gateway will detect local SQL Server on-premises instance. Press enter to connect to it and press next:

 

  

Select your associated ‘Storage Account’, please remember whatever storage account you will create remain always.

In case you need more information on ‘Azure Storage’, please read my ‘Azure Storage Series of 7 articles’ from here  

Give your Azure Server Admin credentials

And this beautiful screen will appear. In my case, I select ‘DELETE ALL EXISTING OBJECTS BEFORE IMPORT’

After Continue, your Import screen will appear

Click, Start Import and you will land to this screen,

Please note, I am importing 951 tables and it took almost 40 minutes. Please note it also delete all records from Storage, I found it quite fast.

I hope this article will help you to understand how you can move your Complete Data into Azure SQL Data warehouse.

Please NOTE below points

1. Redgate – DPS –> Not able to migrate Views and Stored Procedures. It's meant to transfer your Whole Data and take all pain of migration and data conversion away

In case of any doubt or question, feel free to write message or connect by any social media, I will reply ASAP. You may also reach me at My Website from here. 

Until next time, keep learning.