Pragmatic Works Blog

Getting to Know Azure Offerings

Written by Delora Bradish | Sep 13, 2016

As Azure offerings mature and Azure becomes less bleeding edge and more cutting edge, many of us are getting ready to entertain the idea of jumping on the Azure wagon.  However, when I first started to wrap my head around Azure offerings, I needed to learn a few basics.  Is anyone out there with me?  For example:

 

1. How does my current infrastructure match up to current Azure technologies?

2. What is the difference between DWIs, DTUs and DSUs?

3. If I don’t have TBs of data, is Azure even for me?

If you are looking at Azure for the first time, I am going to assume for a moment that you may be just a tiny bit like me.  So, let’s start with some basics. First comes a new set of acronyms!

Acronym & Link

Definition

The Short Version of “What is it?”

Azure

Not an acronym

This is the name for Microsoft's cloud computing platform

ADF

Azure Data Factory

A data integration service; used to migrate from on-premises to Azure DW

APS

Analytics Data Platform

Formerly PDW

CETAS

Create External Table As SELECT

How APS and Azure DW interact (query, export or import data) when working with  Hadoop or Azure Storage Blobs

CTAS

Create Table As SELECT

How APS and Azure DW interact (select, copy, move) data, create a columnstore index or choose a different hash distribution

DBaaS

Database as a Service

Often used in reference to Azure Database.  See chart below.

DMG

Data Management Gateway

Used by Power BI and Data Factory to read or move data from on-premises data stores to the cloud

DMS

Data Movement Service

A Windows service used by Azure DW to run each query in parallel

DSU

Database Stretch Unit

Used for pricing SQL Server Stretch Database

DSU

Database Stretch Unit

How compute usage is billed by Stretch Database

DTU

Database Transaction Unit (Azure DB)

Used for pricing Azure Database

DWI

Data Warehouse Units (Azure DW)

Used for pricing Azure Data Warehouse

eDTU

Elastic Database Transaction Unit

Use for pricing Azure DB elastic pools (when multiple databases share a common set of resources)

ETL

Extract, Transform and Load

A typical SMP way of data integration

ELT

Extract, Load and Transform

A typical MPP way of data integration

HDFS

Hadoop Distributed File System

Polybase is Microsoft’s bridge between Hadoop and SQL Server allowing you to use SSMS to access unstructured Hadoop (blob) storage.

IaaS

Infrastructure as a Service

Often used in reference to SQL Server in Azure Virtual Machines

ML

Machine Learning

Microsoft's cloud-based predictive analytics solution

MPP

Massively Parallel Processing (APS and Azure DW)

Architecture that sits under Microsoft's APS and Azure DW technologies; allows for processing TBs of data, both relational and non-relational; associated with column store indexes

PaaS

Platform as a Service

Often used in reference to Azure Database.  See chart below

PDW

Parallel Data Warehouse

Now APS

SaaS

Software as a Service (Azure DBs)

Often used in reference to Azure Database

SMP

Symmetrically Parallel System (SQL Server 2016, Azure DB and SQL Server on Azure Virtual Machines)

What we all know and love; think “row store” although column store arrived with SQL 2012.

 

Next, let’s take a high level look at the difference between Azure DW (Data Warehouse) and Azure DB (Database).  After all, we can currently store both OLAP and OLTP on the same SQL Server instance we have now, correct?  Well, the difference is first hardware, but then also the method SQL Server uses to both store and retrieve data – this is the short version. You cannot take a current SQL Server 2015 ENT based data-anything and drop it into APS or Azure DW without first writing some CTAS scripts, considering your index strategy and (smartly) selecting a distribution key.  Don’t let this discourage you, however.  Please review the following table: 

Azure Data Warehouse

Azure Database

OLAP

OLTP

MPP

SMP

Sold in DWIs (Data Warehouse Units)

Sold in DTUs (Database Transaction Units)

1 DWU =

7.5 DTUs

Optimized for analytics

Optimized for SaaS App Development

Limited t-sql features

All t-sql features

Migrate from APS (Analytics Platform System) formerly known as PDW (Parallel Data Warehouse)

Migrate from on-premises SQL Server Databases (including those OLAP databases smaller than 1 TB)

Billed Hourly

Billed Hourly

Decrease costs by pausing compute resources whereby not paying when not needed.

Decrease costs by adjusting performance and scale, but you cannot pause it

Think “column store”

Think “row store” although column store indexes did become available beginning in SQL 2012

CTAS() or CETAS()

INSERT INTO…

Choose this when you have > 1 TB of data or unstructured data

Choose this when you have < 1 TB of data or when do not need the added benefit of MPP.

 

There is a pretty good write-up by MSDN comparing the differences between Azure DB and SQL Server in Azure VMs <click here>, so I won’t reiterate it. Assuming that the reader does not have an APS system humming in their back computer room, right now and for the majority of us, a good place to start is with a small Azure Database. I am going to pretend to be a Nike commercial this year and “Just do it!” This is not a marriage, after all, although now that I have said it, Azure DB does have some limitations. Read about them here, but this is not an excuse not to try! 

If you want help beginning, you know who to call. (No… not Azure Busters!)  Reach out to Pragmatic Works at www.pragmaticworks.com, or contact our sales team at sales@pragmaticworks.com