Saskatchewan IT Summit 2018

SKITSUmiit

Excited and Happy to share that  I’m speaking at ‘Saskatchewan IT Summit 2018’  Conference on APRIL 30 – MAY 1 at Radisson Hotel Saskatoon -Canada.

I’m speaking on ‘Using PowerBI and Azure Datawarehouse in K-12’

Looking forward to hooked up with great speakers and talk about technologies.

My talk will be at Naples (TECH Stream) on Tuesday, May 1 at 1:15 – 2:15 P.M. 

Saskatchewan IT Summit 2018

Tech Talk Details

Register here

Here is abstract and Room Details :-

Session Abstract

Session Room Details

 

See You there…

Advertisements

Import your ‘On-Prem’ Data to Azure SQL Data Warehouse by SSIS

Introduction

We have already discussed many ways to import data in Azure DWH. Today I will discuss OLD SCHOOL approach of data import by SQL Server Integration Services (SSIS). In my opinion, this is the most promising way to import data from different data sets/ types like SQL Server, Oracle, MySQL, Flat File, CSV, DB2 etc…

Prerequisite

  • Azure Subscription.
  • Azure SQL Server and an Azure Data Warehouse Database
  • SQL Server Data Tools with SSIS installed
  • Visual Studio 2015 – Visual Studio 2013

Let’s Start,

FYI~,  I’m using  VS 2015

Azure

Go to File>New project and select Integration Services Project,

Azure

Add ‘Data Flow Task’ as shown below,

Azure

Double click in Data Flow Task, it will open Data Flow pane

Azure

Attendance Code Source is Data Flow Components

Azure

Give details of your Server and Table after Double Clicking Attendance Code Source,

Azure

 

Azure

You could click on the table to Preview Data,

Azure

Here I’m only inserting the New Rows at Lookup Editor,

Azure

At Destination Editor, You need to give Azure Data Warehouse Name at connection manager, You could find it after Login at your Azure Portal and Clicking at Azure Data Warehouse as highlighted below,

Azure
Azure

 

Azure

After this, click F5 or Run and Your SSIS package will execute.

Please note GREEN Color reflect successful run.

Azure

Hope this will be helpful, until next time happy coding!!

Learn Chat bot application using Microsoft Bot Framework and Secure Cloud Azure Storage 

Learn Chat bot application using Microsoft Bot Framework and Secure Cloud Azure Storage“.

Sat, Mar 24, 2018 10:00 AM – 12:00 PM CDT  / 9 AM Saskatchewan Time. 

Where: Online

Price: Free of cost

Event URL: https://global.gotomeeting.com/join/776269525

Note: There are 100 seats only. First come first serve.

Details of Event

Learn Chat bot application using Microsoft Bot Framework – Rahat Yasir

• Why Chat Bot

• How to Develop

• Accelerated development

• Q&A

Unboxing Secure Cloud Azure Storage – Deepak Kaushik

• Brief Introduction to Azure Storage

• Scenarios / Working with Blob Storage

• Key Concepts / Architecture

• Q&A
Please join my meeting from your computer, tablet or smartphone. 
https://global.gotomeeting.com/join/776269525 

You can also dial in using your phone. 
Canada: +1 (647) 497-9373 

Access Code: 776-269-525 

Before and after MVP Global Summit 2018

Being back at home and work, I wanted to convey my thanks to Microsoft for organizing such awesome events as #MSSummit at Seattle. I feel fortunate enough to meet industry legends and phenomenal figures. I learned fundamentals of the Technologies by *Following, Listening and Reading* from these legends.

I have mesmerizing memories to meet  Scott Hanselman, Mahesh Chand, Jen Stirrup, Magnus Mårtensson , DJ, AzureAdvocates and many more.

I’ve learned many new things and earned so many friends/connection here @ #MVPSummit 

Special thanks and gratitude to Microsoft Canada Community Program Manager,  Simran Chaudhry , you are the Best Lead Simran, Bundle of Love and Respect!!

Thank you Patrick Malone and team for MVPGlobalSummit, I’m  #ProudToBeMVP

Looking forward to meet you all very soon !! #CDNMVP #MVPBuzz  

Here is some of Treasures from my sweet memories :-

 

 

MVP Global Summit 2018

MVP Summit

I’m super excited to attend  Microsoft MVP Global Summit  in March 1st week.

MVPs from around the world meet on Bellevue, Redmond, and Seattle, Washington for several days of technical sessions on the Microsoft campus.

Thank you Microsoft for providing *Great Award* and having me to the MVP party.

I’ll continue to endeavor to be worthy of your trust and acknowledgement.

Looking forward to see you all fellow MVPs.

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

Options for the Compute Families

Azure

Azure Storage,

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