Microsoft Azure SQL Data Warehouse - Architecture and SQL

Tom Coffing, Todd Wilson

• Publisher: Coffing Publishing
• Year: 2015
• eBook ISBN: 9781940540320
• Topic: Computer Science
• Subtopic: Data Warehousing
• Index: Computer Science

One of the most popular databases worldwide is Microsoft's SQL Server. Now Microsoft has introduced their MPP data warehouse system, designed for the cloud, called the Microsoft Azure SQL Data Warehouse. The Microsoft Azure Cloud is rapidly making T-SQL one of the standards of SQL among millions of companies. This book details the architecture of the Azure SQL Data Warehouse and the SQL commands available. This book is perfect for anyone who works with the Microsoft Azure SQL Data Warehouse. This book educates readers on how to create tables and indexes, how the data is distributed, and how the system process the data. Plus, it is followed up with over 700 pages of SQL examples and explanations. This book is a must have for anyone designing, implementing or querying the system. The Authors of Tera-Tom Coffing, who has written over 70 successful books and Todd Wilson, CTO of Coffing Data Warehousing bring a combination of minds together made up of experience and real business world knowledge.

Chapter 1 – Introduction to the Azure SQL Data Warehouse

“The man who has no imagination has no wings.”

– Muhammad Ali

Introduction to the Family of SQL Server Products

Microsoft SQL Server Compact 4.0

Microsoft SQL Server Compact 4.0 is a compact database that is embedded inside Nexus and other desktops around the world. It is ideal for also embedding in web applications. SQL Server Compact 4.0 provides developers a common programming model with other SQL Server editions. This is important for developing both native and managed applications. SQL Server Compact provides outstanding flexibility, but in a small footprint.

SQL Server 2014 Express Edition

Microsoft provides this for free! This powerful database engine is perfect for embedded applications or for redistribution with other solutions. Independent software vendors (ISV's) use it to build desktop applications. If you need support for greater than 10 GB databases, SQL Server Express is compatible with other editions of SQL Server.

SQL Server Standard Edition

Microsoft's robust data management and business intelligence database is ideal for departments and small workgroups. It supports common development tools for both on premise and cloud applications. This edition enables effective database management with minimal IT resources and it is compatible with other editions.

Above, are the first three offerings from Microsoft on SQL Server.

Introduction to the Family Continued

Microsoft SQL Server Web Edition

Microsoft's Web edition is a low total-cost-of-ownership option to host Web applications that provides scalability, affordability, and manageability capabilities for small to large scale Web initiatives.

SQL Server 2014 Business Intelligence Edition

Microsoft's Business Intelligence edition is for the BI intelligence community and delivers a comprehensive platform. This empowers organizations to build and deploy secure, scalable and manageable BI solutions. It has browser based data exploration and visualization, plus includes powerful integration capabilities.

SQL Server 2014 Enterprise Edition

Microsoft's SQL Server 2014 Enterprise edition delivers high-end datacenter capabilities with performance that has been enhanced for virtualization, business intelligence and integration capabilities. This enables high service levels for mission-critical workloads and end user access to data insights.

Above, are the next three offerings from Microsoft on SQL Server.

Microsoft Azure SQL Data Warehouse

Azure SQL Data Warehouse

Microsoft's Azure SQL Data Warehouse is a massively parallel processing (MPP) data warehousing appliance built for any volume of relational data and provides integration to Hadoop. Azure SQL Data Warehouse can provide up to 100x performance gains over other SQL Server platforms. This is the MPP platform that provides linear scalability for when data volumes grow and the number of users increases.

Azure SQL Data Warehouse is designed to parallelize and distribute the processing across multiple Symmetric Multi-Processing (SMP) compute nodes. Azure SQL Data Warehouse is only available as part of Microsoft's Analytics Platform System (APS) appliance.

Azure SQL Data Warehouse is a shared-nothing architecture, which means each processor has its own operating system, memory and set of disks. Nothing is shared! Data is “horizontally partitioned” across nodes. This means that each node has a subset of the rows from each table in the database. Each node is then responsible for processing only the rows on its own disks.

Above is the information about Microsoft's Azure SQL Data Warehouse, which is Microsoft's MPP system

Symmetric Multi-Processing (SMP)

A Symmetric Multi-Processing system has multiple processors for extra power, but these processors share a single operating system, memory pool and they share access to the disks. This is a great architecture for speed, similar to a restaurant that is quick and organized, but it lack the ability for unlimited expansion. When there are too many cooks in the kitchen you need an MPP system that scales many SMP systems together as one parallel processing data warehouse.

A Symmetric Multi-Processing (SMP) system is what Microsoft is known for in their SQL Server suite of products. The only product that does not use SMP design is the new Azure SQL Data Warehouse. It uses a Massively Parallel Design (MPP).

What is Parallel Processing?

“After enlightenment, the laundry”

- Zen Proverb

“After parallel processing the laundry, enlightenment!”

- Azure SQL Data Warehouse Zen Proverb

Two guys were having fun on a Saturday night when one said, “ I’ve got to go and do my laundry.” The other said, “What?!” The man explained that if he went to the laundromat the next morning, he would be lucky to get one machine and then would be there all day. But, if he went on Saturday night he could get all the machines and he could do all his wash and dry in two hours. Now that’s parallel processing mixed in with a little dry humor!

The Basics of a Single Computer

“When you are courting a nice girl, an hour seems like a second. When you sit on a red-hot cinder, a second seems like an hour. That’s relativity.”

– Albert Einstein

Data on disk does absolutely nothing. When data is requested, the computer moves the data one block at a time from disk into memory. Once the data is in memory, it is processed by the CPU at lightning speed. All computers work this way. The "Achilles Heel" of every computer is the slow process of moving data from disk to memory. The real theory of relativity is to find out how to get blocks of data from the disk into memory faster!

Data in Memory is Fast as Lightning

“You can observe a lot by watching.”

– Yogi Berra

Once the data block is ...

Table of contents

• Cover
• The Tera-Tom Video Series
• Current Books in the Tera-Tom Genius Series
• Our Recommended Book In The Tera-Tom Genius Series
• Tera-Tom- Author of over 50 Books
• The Best Query Tool Works on all Systems
• Trademarks and Copyrights
• About Tom Coffing
• About Todd Wilson
• Contents
• Chapter 1 – Introduction to the Azure SQL Data Warehouse
• Chapter 2 – The Azure SQL Data Warehouse Table Structures
• Chapter 3 – Hashing and Data Distribution
• Chapter 4 – The Technical Details
• Chapter 5 – CREATE Statistics
• Chapter 6 - The Basics of SQL
• Chapter 7 – The Where Clause
• Chapter 8 – Distinct, Group By and TOP
• Chapter 9 – Aggregation
• Chapter 10 - Join Functions
• Chapter 11 – Date Function
• Chapter 12 - Temporary Tables
• Chapter 13 – Sub-query Functions
• Chapter 14 – Window Functions OLAP
• Chapter 15 - Working with Strings
• Chapter 16 - Interrogating the Data
• Chapter 17 – Table Create and Data Types
• Chapter 18 – View Functions
• Chapter 19 – Data Manipulation Language (DML)
• Chapter 20 – Set Operators Functions
• Chapter 21 – Stored Procedure Functions
• Chapter 22 – Statistical Aggregate Functions
• Chapter 23 – Systems Views
• Chapter 24 – Nexus