Move & Load Data to Azure SQL DataWarehouse using Data Platform Studio

There are multiple ways to populate data into Azure SQL DataWarehouse PAAS (Platform as a Service). The primary way is to use Polybase (with T-SQL) or Azure Data Factory. If you wanted to populate and load data to Azure SQL DataWarehouse directly, then SSIS (SQL Server Integration Services) was your alternative. In SSIS 2016, you can choose to have Azure SQL DataWarehouse as your destination. With Polybase, you could read the data from an Azure Blob storage and load it into an Azure DW database. But there is also an easier GUI driven method which any user with an Azure account can do.  One of Microsoft’s long-time Gold Partner Redgate has designed a GUI tool called Data Platform Studio to perform just that. In this post, I will walk you through the steps to load data to Azure SQL DataWarehouse using Redgate’s Data Platform Studio.

Also Read: Introduction to Azure SQL DataWarehouse PAAS

Load Data to Azure SQL DataWarehouse using Data Platform Studio

Redgate’s Data Platform Studio is totally free and is confirmed by the Redgate Team. See below:

Tom from the Redgate team did mention explicitly that the basic import function would be free. This could also mean that a more feature-rich paid functionality could be coming since the tool is currently in beta. In order to access the tool, you need to go to https://beta.dataplatformstudio.com and sign in with your Microsoft account tagged with an Azure subscription. If you don’t have one, go to this URL and sign up for Azure. Here is what the Login screen looks like:

As soon as you log into the portal, you will find two options, “imports” and “gateways“. If you have not done any imports earlier, then you would see a blank screen. In order to get started, click on “New Import” located on the top menu. 

This takes you to the next step where you will have to create a “Gateway“.

The DPS Gateway is a small lightweight standalone application which needs to be installed on the same computer on which you will have the datasource. If You are familiar with the PowerBI gateways, you would feel right at home. It uses a secure rest api to connect to the Redgate service from your on premises network. Since it is a rest based app, it does not need any specific firewall ports to be opened. Do keep in mind that the gateway app is a Windows only app.

Once you have installed the Gateway app on your server, you can select the gateway from the drop down menu option. See the above screenshot for your reference. There is also a tiny icon that would be running on your taskbar. See below:

Quick Note: If the DPS-Gateway status shows as disconnected, just exit the app from the app tray and rerun it from the “Start Menu“

On the next page, select the database that you would want to export to Azure. Enter in the “ServernameSQLInstance” name format. If you are using a localhost with a default SQL instance, just put a dot (.) instead. Optionally, you can also toggle the menu to select “SQL Authentication” in case if you are using one. See the below screenshot for your reference:

On the next screen “Select a Database” that you want to move. In our example, we would be using the “AdventureworksDW2016CTP3” sample database to move to the cloud. If you do not want to export the entire table, the tool will allow you to select individual tables as additional granularity.

Note: If you want to download SQL Server sample databases, go here to download a complete list of SQL Server sample databases.

We have only selected a subset of tables that we need to do data modeling on. Do keep in mind that the sample that we are using as an example here contains several large Clustered Column Store enabled tables. On the next step, you will have to select the Storage account on Azure. If you do not have a storage account, go to https://portal.microsoft.com and create one. Currently, only standard storage accounts are supported for some reason. Once you have selected the storage account, click on next.

On the next page, select the DataWarehouse which is your destination. Do keep in mind that you should have both your storage account and your Azure SQL DataWarehouse instance in the same region. In out case, we selected “East-US“. Otherwise, there would be latency issues and the import process could take more time than usual. If you do not have one created, go here to create and deploy an Azure SQL DataWarehouse. See our final step.

Click next and then click on “Start Import“. The next step will show you graphically, much much progress has been done and in what time.

That’s it! Once the import/export process is done, you can use any tool like Excel, PowerBI Desktop or PowerBI.com to access the Azure SQL DataWarehouse. The guys at Redgate are quite helpful and if you face any issues, you can initiate a chat support request. Cheers!