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 a company’s operational systems. Data systems emphasize capturing data from different sources for both access and analysis. Azure SQL Data Warehouse is based on the latest general release of SQL Server and provides enterprise level data warehouse functionality.
Azure SQL Data Warehouse was first released in July 2016 and enables companies to centrally consolidate and provide global access to their data warehouse for analysis and reporting. This service includes scalability with Azure cloud resources and uses massively parallel processing (MPP) to provide fast query execution on 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 to provide fast query results for large datasets. It also uses a SQL-based single view for relational databases and non-relational big data stores allowing enterprises 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 directly query large data stores, such as Hadoop systems. PolyBase enables an organization to use standard T-SQL queries to import data into SQL Data Warehouse by providing a single SQL-based query surface for all your data. SQL Data Warehouse stores data in relational tables using columnar storage, which reduces data storage costs and improves query performance.
SQL Data Warehouse leverages a scalable architecture to distribute computing data processing across multiple nodes. The Azure SQL Data Warehouse architecture separates compute and storage, allowing users to scale them independently and only pay for the processing and storage the organization needs.
Advantages and disadvantages of Azure SQL Data Warehouse
Benefits of Azure SQL Data Warehouse include:
- Cost-effective pay-as-you-go model for an organization implementing its own enterprise-level data warehouse.
- Leverages Azure cloud compute and storage resources.
- Scalable computing power.
- System management is performed by Microsoft.
- Microsoft guarantees that Azure SQL Data Warehouse will provide 99.9% availability.
- Full compliance with standards and regulations such as 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 with Azure SQL Data Warehouse include:
- Moving data into the cloud service can be difficult.
- Moving data from IaaS can be difficult.
- Only supports 32 connections at a time.
- Only supports up to 1024 active connections.
- Lack of support for In-Memory OLTP.
- Some functions only work in the classic Azure SQL Data Warehouse portal.
Understanding the pricing structure used by Azure SQL Data Warehouse is important to ensure that a customer purchases the correct capacity that their business will need. Unlike most cloud services where there is only one change, with Azure SQL Data Warehouse the user pays for two different tiers of resources: compute and storage. Data storage is priced at $122.88/1TB/month which includes their data warehouse size and 7 days of incremental snapshot storage. Geo-redundant storage for disaster recovery is priced starting at $0.12/GB/month. The calculation is provided using a sliding scale based on Data Warehouse Units (DWUs) which range from DW100c $1.20/hour to DW30000c $360/hour. Discounts are available for multi-year agreements.
This was last updated in August 2019
Continue Reading About Azure SQL Data Warehouse