Blog

STEP by STEP ‘Extract, Transform and Load’ Data into Azure Data warehouse with Azure Data Factory

Introduction

In this article we will learn how to use Azure Data Factory to Extract, Transform, Load (ETL) data especially for the data warehousing purposes.

If you are new to Azure Data warehouse, I would like to suggest the below prerequisites.

Prerequisites

  1. SQL Server on-premises installed
  2. Your Enterprise or Demo database available on SQL Server on-premises
  3. Let’s Move ‘On-Prem’ Data Warehouse To ‘Azure SQL Data Warehouse’ With Redgate Data Platform Studio (DPS)
  4. An Azure SQL Data Warehouse account, if you still don’t have one please create ‘Azure SQL Data warehouse here’

If you have some questions on Azure SQL Data Warehouse such as

Why Enterprises Should Now Consider ASDWH Seriously please read here

This is a real time example to schedule ETL from beginning.

Background

Most recently, I got an opportunity to migrate and schedule ETL job from ‘On-Prem’ DWH to ‘Azure SQL Data Warehouse (ASDWH)’.

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 SQL Data Warehouse

Under Common Task of ‘Azure Data Warehouse’, please click on ‘Load Data’ as shown below:

Azure SQL Data Warehouse

After you click the load data the below screen will open, please click Azure Data Factory:-

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Click on the Create Button.

Provide the required details, please note currently 2 versions of Azure Data Factory are available

Azure SQL Data Warehouse

However, I will choose V1 – Version 1, V2 Version which is currently in preview will be used in next article as shown below :-

Azure SQL Data Warehouse

Select location nearest to geography as per your business and click Create

Azure SQL Data Warehouse

And Azure Data Factory is ready

Now we need to schedule ‘Azure Data Factory’ to update the ‘Data warehouse database’, so click ‘Copy Data’,

Azure SQL Data Warehouse

Azure SQL Data Warehouse

As you click on the Copy Data, the below screen will open:-

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Please note ‘Task cadence on Task schedule’ has the following options:-

  1. Run once now
  2. Run regularly on schedule

I’m considering my ‘AzureDataFactory’ as ETL to repeat every day at 8:25 PM until end date time of ‘12/31/2099’. If you want to copy only once, similar to ‘Redgate- Data Copy’   as described here Let’s Move ‘On-Prem’ Data Warehouse To ‘Azure SQL Data Warehouse’ With Redgate Data Platform Studio (DPS)

Click next

Azure SQL Data Warehouse

As my ‘On-Prem’ data warehouse source is SQL Server, so select ‘SQL Server’ as shown above, and click next

Azure SQL Data Warehouse

Here you will be landed to the page and it will ask to Configure Integration.  Click at ‘Launch express setup on this computer’ as shown below,

Azure SQL Data Warehouse

And it will download the gateway as shown below

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Azure SQL Data Warehouse

And Click Next

Azure SQL Data Warehouse

Azure SQL Data Warehouse

If no changes are required click Next. As my destination is Azure SQL Data warehouse, I will select it and click next

Azure SQL Data Warehouse

Below screen will appear

Azure SQL Data Warehouse

Below Mapping will be displayed to verify

Azure SQL Data Warehouse

Azure SQL Data Warehouse

Click Next For Final Deployment

Azure SQL Data Warehouse

And with this, you just deployed your first ETL Job- ‘Azure Data Factory’. Just to recall, this job will execute daily. You could change the frequency anytime as per business need.

Thanks again for reading the blog.

In case of any doubt or question, feel free to write message or connect by any social media, I will reply ASAP.

Until next time, keep learning….

Move your ‘On-Prem’ SQL Data Warehouse To ‘Azure SQL Data Warehouse’ With Redgate Data Platform Studio (DPS)

Introduction

 

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

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’

  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).

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:

Azure

Azure

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

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

  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.

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

DownLoad- DataWarehouseMigrationUtility

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 DataWarehouseMigrationUtility‘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.

Azure SQL Data Warehouse – Why Enterprises should consider it seriously?

Today we would further explore some of the reasons why enterprise should choose Azure SQL Data Warehouse. If you want to understand the nitty gritty of the Azure Data Warehouse, please go through the ‘Introduction to Azure SQL Data Warehouse’

 

Platform as a Service (PaaS) Offering 

Data Warehouse is offered as a platform as a service in Microsoft’s Azure cloud infrastructure. The service is fully managed, which means you don’t have to pay for hardware or don’t need to spend time on software updates or bother about security.

The users need to pay only for what they need and when they need it.

Built on the SQL Server 

The SQL Data Warehouse extends the T-SQL constructs to create indexes, partitions and stored procedures, which allows to easily migrate to the cloud.  Azure SQL Data Warehouse have complete support and compatibility with SQL tools and offerings.

Elasticity

Azure SQL Data Warehouse is highly ELASTIC in nature. Azure SQL Data Warehouse independently scales compute and storage so customers only pay for the query performance.

You only need to pay for compute power what you used. Scale it up for large queries and scale it back when your production demand is less. If you ‘Pause the service’, you only pay for the storage.

SQL Transparent Data Encryption (TDE) with Bring Your Own Key support

Transparent Data Encryption (TDE) with Bring Your Own Key (BYOK) support for Azure SQL Database and Azure SQL Data Warehouse is under preview. Now you can have control of the keys used for encryption at rest with TDE by storing these master keys in Azure Key Vault.

Polybase

Nowadays, essentially the need to combine structured and unstructured data is quite common. With PolyBase, SQL Data Warehouse offers the ability to combine data sets easily.

In addition, SQL Data Warehouse can query unstructured and semi-structured data stored in Azure Storage or Cloudera using familiar T-SQL skills making it easy to combine data sets no matter where it is stored.

Azure Data Warehouse supports Massively Parallel Processing

Another reason for Azure is its support for Massively Parallel Processing (MPP ) nature.

MPP means one to many relationship. Say if 80% servers got down then also request could be fulfilled.  MPP doesn’t depends on the single machine/limited hardware resources.

Hybrid infra supports

With SQL Data Warehouse you are able to quickly move to the cloud without having to move all of your infrastructure along with it. With the Analytics Platform System, Microsoft Azure and Azure SQL Data Warehouse, you can have the data warehouse solution you need on-premises, in the cloud or a hybrid solution. Microsoft has built-in support for a number of their other tools and data services.

Built-in Backups

Azure SQL Data Warehouse automatically backs up your data by taking database snapshots and creating restore points. You could also do a self-service restore from these backup

Support for Azure Machine Learning

Azure SQL Data Warehouse is supported as both a source and destination for Azure ML Models.

Azure Stream Analytics

Azure Stream Analytics provides real-time, stream processing in the cloud. With ASA, you can query and analyze stream-based data while it is moving. Azure SQL Data Warehouse can be the output destination for Azure Stream Analytics.

 

Why Choose Cloud Over On-Premise Traditional Warehousing?

Some of the reasons:-

  • We do not need large capital expenses to get started with Azure Data Warehouse. We don’t need to procure servers, worry about server configuration, security compliances and also it save a lot of approvals.
  • In another means we do not need staff to maintain hardware, virtualization and operating system.
  • We can scale storage or performance and compute it up or down ‘ON DEMAND’*, while same thing is very cumbersome in the traditional ware house system.
  • Cloud has very High Uptime (99.9%)

 

Hope aforesaid reasons would help you to make your mind for Azure SQL Data Warehouse.

Till next time, ‘Happy Learning’!!

Use Azure IaaS As A Starting Point On Your Cloud Journey

In this article , we will discuss how to leverage business with Azure IaaS (Infrastructure as a Service). We will take a comparative look between Traditional Model vs. Cloud Model.

If you have quesitons about the clous Cloud , please look over the following:

I am sure by the end of this article, we will have a sound rationale for using Cloud Services

Azure

As shown above , in the Traditional on-premise Service Model we have to take care of the Application, Data, Runtime, Server, Storage etc.

But we could use either IaaS, PaaS or SaaS based on individual and business usage

Pay per use

Azure

For Platform as a Service (PaaS), we can benefit from one or all of the Security & Management, Platform Services or Infrastructure Services.

Worldwide Azure Regions Availability

Azure

As of today, there are a total of 34 Azure regions to give support 24/7 to customers in almost every continent.

Geo Redundancy

Azure

Highly promising Geo replications options are available across the globe for Disaster Recovery.

Security

It would not be wrong to say that the data is highly secure in the Cloud, owing to the Security mechanisms at the levels of:

  1. Physical
  2. Infra
  3. Network and
  4. VM

It is shown below.

Azure

Azure Security Center

For details, please refer to this related article:

Azure Security – Solution To Digital Transformation

Azure security center is available to keep an eye out every time for Cloud Resources, as shown below.

Azure

Now, let’s dive deep down into IaaS Core Services.

It’s a combination of the following

  1. Compute
  2. Storage
  3. Networking and
  4. Management

    Azure

Options for the Compute Families

Azure

Azure Storage,

For more details, please refer to my Azure Storage series from 1 to 7,

Azure NoSQL DocumentDB

Azure DocumentDB is a NoSQL document database-as-a-Service from Microsoft Azure is designed for saving, retrieving and managing each record in an internal structure of fields (semi-structured data, documents or keys). DocumentDB is a schema free JSON document database and also support JavaScript. DocumentDB includes built-in high availability, offers predictable performance and supports elastic scale-out. DocumentDB has some exciting features.

  • Tunable, high performance.
  • Multi document transactions.
  • Query over schema free JSON.
  • Automatic Indexing.
  • Easy to learn query grammar.
  • Low-latency access around the globe.
  • Rich SQL queries over schema-free JSON storage.
  • Server side execution of the Application defined JavaScript as stored procedures and triggers.
  • JavaScript language integration for multi-document transactions.
  • Tunable consistency levels for optimal Application performance.
  • Standard JSON storage with RESTful HTTP interfaces.

    Azure

Let’s understand it by performing something practical on the portal. Enter your credentials and login to Azure portal. Go to New, select Storage and click See All because it seems there is no DocumentDB in Featured Apps.

Azure

Select NoSQL and click it. Here, I need to create Document Account.

Azure

Give a name to your account and select API for it. There are two options for selecting API DocumentDB and MongoDB. I’ll discuss MongoDB in my further articles. All steps are same and are related to the previous articles. Click to create DocumentDB Account.

Azure

Under DocumentDB account, I have to create a Collection. It is a logical container for the physical partitions. A collection is like a container for JSON documents and JavaScript Application logic. Click Add Collection to add a new one.

Azure

For a collection, give a name to your collection and its storage capacity to handle collection is up to 10 TB and set a storage capacity for it. Now, there is a Partition key, which is a JSON property within your documents. Partition key is an element within your document. Give a database name to it and click OK button to create a Collection.

Azure

Thus, my collection is ready now.

Azure

Go to Document Explorer, where we can create and upload our documents under our Collection.

Azure

Click Create and you will see a JSON format script with ID and People (partion). You can upload JSON script as well. I’ve given an Id and some data in People Partition. Click Save to save this script.

Azure

Now, go to Query Explorer and run a query.

Azure

The query will be executed successfully and produces the expected result.

Azure

Creating And Using Azure SQL Server And Azure SQL Database

I am going to explain SQL Server and SQL database in Microsoft Azure portal. You can create a database and Server from Azure portal and access it in Visual Studio. Afterwards, you can use it as Entity Framework Designer from the database in any Application.

Let’s login to Azure new portal and create a SQL database.

SQL Server

For creating a database on Azure portal, you have to fill in some details of it. Give a name to your database, subscription name, resource group whatever you want to use, blank database and a Server.

Click Servers, if you want to use an existing SQL Server from the list of the available Servers into your Azure, then you can select it (you must know name and password of the Server). Here, I am going to create a new one.

SQL Server

To create a new Server, follow the steps given below.

  • Server Name – Give a name to your Server.
  • Server Admin Login – Enter user name but make sure that your login name meets the requirements, given below – it’s a SQL identifier and not a typical system name such as an admin, administrator, sa, root, dbmanager, loginmanager etc.
  • Password- Create a password for your Server, which would be used when you connect with it from MS SQL Server Management.
  • Confirm Password – The password should match with the password.
  • Location- Your Server location.

Click to select button to create a new Server.

SQL Server

Just click to create button to create a Server and database.

SQL Server

I want to add it with Microsoft SQL Server. To generate a client IP, click Add Client IP in Firewall Setting.

SQL Server

Here, I am using Visual Studio 2015. My Azure subscription is already connected with Visual Studio Server Explorer. Open SQL Databases under your Azure subscription. You’ll get your database, which you created on Azure portal. I want to open it in Microsoft SQL Server Explorer where we can create the scripts.

SQL Server

In next popup, give a Server name into Server name section, which you created in Azure portal along with the user name and password.

SQL Server

After clicking Connect button, you connect with Explorer. Now, you can create tables.

SQL Server

Add a new table.

SQL Server

After writing table script in to design section, click Update button.

SQL Server

Click Update database to update whole database.

SQL Server

Refresh the table and you will get the expected result.

SQL Server

If you want to connect with it again, then you have to enter only Server password.

SQL Server

Now, you can connect with ADO.NET Entity Data model. Also, you can use an Entity Framework approach.

Fundamentals Of Microsoft Azure Mobile Service

Fundamentals Of Microsoft Azure Mobile Service

Azure

Microsoft Azure Mobile Service gives us the authority to create a Cloud Service Mobile Application. This will leverage us with more flexibility and make an Application more portable.

This is a short introduction of Azure Mobile Service. Now, let’s create and integrate new mobile Service with new or an existing Application. Thus, let’s understand Azure Mobile Service and I am sure, it would mesmerize the users.

Let’s create a new mobile Service

First go to your Azure portal and select the Mobile Service option. Click plus new icon in the bottom of the page.

Azure

Create a new Mobile Service.

After creating Mobile Service, open the Mobile Service and select the dropdown option “CONNECT AN EXISTING WINDOWS OR WINDOWS PHONE APP” below the Get Started option.

Azure

Now, copy the code and paste it in the App.xaml.cs file of your Application. Please focus on the additional instruction, which is right above the code.

Now, create a new class named MyItems, which will make all the necessary tables of your Mobile Service.

  1. class MyItems
  2. {
  3.     [JsonProperty(PropertyName = “id”)]
  4.     public string Id { get; set; }
  5.     [JsonProperty(PropertyName = “message”)]
  6.     public string Message { get; set; }
  7.     [JsonProperty(PropertyName = “location”)]
  8.     public string Location { get; set; }
  9.     [JsonProperty(PropertyName = “Date”)]
  10.     public DateTime Date { get; set; }
  11. }

I have created four attributes namely fieldsID, Message, Location and Date. Please remember for JsonProperty attribute, which we have to install ‘Newtonsoft.JsonNuGet package manager’.

Now, to perform any CRUD operation like insert, update and delete the data in your SQL table, let’s write the code in .cs files.

To begin with, let’s create MainPage.xaml. We need a ListBox to display the data from out Mobile Service table. As there are multiple attributes and columns, here we re showing only Message, Location and Date values.

  1. <ScrollViewer>
  2.     <StackPanel Margin=“10,10,0,0”>
  3.         <ListBox x:Name=“listBox”
  4.                         Margin=“10,10”
  5.                         RequestedTheme=“Dark”
  6.                         FontSize=“20”
  7.                         Background=“White”
  8.                         Foreground=“Black” BorderThickness=“1”
  9.                         BorderBrush=“Transparent” >
  10.             <ListBox.ItemContainerStyle>
  11.                 <Style TargetType=“ListBoxItem”>
  12.                     <Setter Property=“HorizontalContentAlignment” Value=“Stretch”></Setter>
  13.                 </Style>
  14.             </ListBox.ItemContainerStyle>
  15.             <ListBox.ItemTemplate>
  16.                 <DataTemplate>
  17.                     <Border BorderThickness=“0,0,0,1” BorderBrush=“#c0c0c0”>
  18.                         <StackPanel>
  19.                             <TextBlock Foreground=“Black” Text=“{Binding Message}” />
  20.                             <TextBlock Foreground=“Black” Text=“{Binding Location}” />
  21.                             <TextBlock Foreground=“Black” Text=“{Binding Date}” />
  22.                         </StackPanel>
  23.                     </Border>
  24.                 </DataTemplate>
  25.             </ListBox.ItemTemplate>
  26.         </ListBox>
  27.     </StackPanel>
  28. </ScrollViewer>

Listing: 2

Put the code given below in MainPage.cs, which is above the constructor.

  1. /// <summary>
  2. /// Mobile Service
  3. /// </summary>
  4. private MobileServiceCollection<Item, Item> Myitems;
  5. private IMobileServiceTable<Item> itemTable = App.MobileService.GetTable<Item>();

Listing: 3

Now, in constructor; initialize MainPage_Loaded method and implement, as shown below.

  1. public MainPage()
  2. {
  3.     this.InitializeComponent();
  4.     this.Loaded += MainPage_Loaded;
  5. }
  6. async void MainPage_Loaded(object sender, RoutedEventArgs e)
  7. {
  8.     Myitems = await itemTable.ToCollectionAsync();
  9.     this.listBox.ItemsSource = Myitems;
  10. }

Listing: 4

Here, listbox is the name of out ListBox control. Thus, we set an item source as Myitems, which sets Mobile Service table’s values.

If you run the Application, it will show all the data in the ListBox control.

Conclusion

I hope, you understood the basics of Microsoft Windows Azure Mobile Service. Till next time, keep learning.