Monthly Archives: September 2017

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

Leave a reply

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?

Leave a reply

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’!!