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

SQL Server on-premises installed
Your Enterprise or Demo database available on SQL Server on-premises
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

Azure

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

Azure

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

Azure

Here we have ‘Two Options’

redgate Data Platform Studio
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).

Azure

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

Azure

Azure
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,

Azure

Click the Download link to download the DPS Gateway installer,

Azure

Azure

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

Azure

Give your Azure Server Admin credentials

Azure

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

Azure

After Continue, your Import screen will appear

Azure

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

Azure

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.

Azure

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

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.

1 Comment

Join the conversation! Your thoughts help the community grow.

sanjeet jha8y ago
Hey Deepak, I do not see any briefing about the design changes between on prem sql DWH (sql server) and Azure Sql DWH(MPP structure) and I am pretty sure you would have got issues especially with surrogate keys. Can you put some light on it pls?