Azure SQL Data Warehouse
Azure SQL Data Warehouse is a managed Data Warehouse-as-a Service (DWaaS) offering provided by Microsoft Azure. A data warehouse is a federated repository for data collected by an enterprise's operational systems. Data systems emphasize the capturing of data from different sources for both access and analysis. Azure SQL Data Warehouse is based on the latest general release of SQL Server and it provides enterprise level data warehouse capabilities.
Azure SQL Data Warehouse was first released in July of 2016 and enables businesses to centrally consolidate and provide global access to their data warehouse for analytics and reporting. This service includes scalability with Azure cloud resources and utilizes Massively Parallel Processing (MPP) to deliver rapid query execution over large amounts of data.
Azure SQL Data Warehouse can be used by data analysts, data scientists and end-users. Data scientists and data analysts design data storage, access and queries that will retrieve data from relational and non-relational data stores. End users run queries and dashboards to process information stored in Azure SQL Data Warehouse to make business decisions.
How Azure SQL Data Warehouse works
Azure SQL Data Warehouse is designed for enterprise-level data warehouse implementations, and stores large amounts of data (up to Petabytes) in Microsoft Azure. It uses MPP to process analytical queries so it can provide fast query results for large data sets. It also uses a single SQL-based view across both relational databases and non-relational Big Data stores enabling businesses to unify structured, unstructured and streaming data within the cloud-based data warehouse. Users can manage Azure SQL Data Warehouse using SQL Server Management Studio (SSMS) or write queries using Azure Data Studio (ADS).
SQL Data Warehouse uses PolyBase to query the big data stores, such as Hadoop systems, directly. Polybase enables an organization to use standard T-SQL queries to bring data into SQL Data Warehouse providing a single SQL-based query surface for all your data. SQL Data Warehouse stores data in relational tables using columnar storage which reduces the data storage costs, and improves query performance.
SQL Data Warehouse leverages a scale-out architecture to distribute computational processing of data across multiple nodes. The Azure SQL Data Warehouse architecture separates compute and storage enabling users to independently scale them and only pay for the processing and storage that the organization requires.
Advantages and disadvantages of Azure SQL Data Warehouse
The advantages that come with Azure SQL Data Warehouse include:
- Cost effective pay-as-you-go model when compared to the cost of an organization implementing their own enterprise-level data warehouse.
- Leverages Azure cloud compute and storage resources.
- Scalable compute power.
- System management is performed by Microsoft.
- Microsoft guarantees that Azure SQL Data Warehouse will provide 99.9 percent availability.
- Full compliance with standards and regulations like PCI-DSS, SOX and HIPAA.
- Built-in advanced security using Azure Threat Detection.
- Data at rest is secured by Transparent Data Encryption (TDE).
- Integration with Azure Active Directory, Data Factory, Data Lake Storage, Databricks, and Microsoft Power BI.
Disadvantages that come along with Azure SQL Data Warehouse include:
Pricing
Understanding the pricing structure that is used by Azure SQL Data Warehouse is important to be sure that a customer is purchasing the correct capacity that their business will require. Unlike most cloud services where there is a single change, with Azure SQL Data Warehouse user pay for two different levels of resources: compute and storage. Data storage is charged at the rate of $122.88/1 TB/month, which includes the size of their data warehouse and 7-days of incremental snapshot storage. Geo-redundant storage for disaster recovery is billed, starting at $0.12/GB/month. Compute is provided using a sliding scale based on Data Warehouse Units (DWUs) which ranges from DW100c $1.20/hour to DW30000c $360/hour. Discounts are available for multiyear agreements.
