Total Page Preview: 000000008791
What is SQL Data Warehouse? Advantage of SQL Data Warehouse.
Combines the SQL Server relational database with Azure cloud scale-out capabilities. You can increase, decrease, pause, or resume compute in seconds. You save costs by scaling out CPU when you need it,and cutting back usage during non-peak times.
Leverages the Azure platform. It's easy to deploy, seamlessly maintained, and fully fault tolerant because of automatic back-ups.
Complements the SQL Server ecosystem. You can develop with familiar SQL Server Transact-SQL (TSQL) and tools.
SQL Data Werehouse Architeture:
Control node:
The Control node manages and optimizes queries. It is the front end that interacts with all applications and connections. Under the surface, the Control node coordinates all of the data movement and computation required to run parallel queries on your distributed data. When you submit a T-SQL query to SQL Data Warehouse, the Control node transforms it into separate queries that run on each Compute node in parallel.
Compute nodes:
The Compute nodes serve as the power behind SQL Data Warehouse. They are SQL Databases that store your data and process your query. The Compute nodes are the workers that run the parallel queries on your data. After processing, they pass the results back to the Control node. To finish the query, the Control node aggregates the results and returns the final result.
Storage:
Your data is stored in Azure Blob storage. When Compute nodes interact with your data, they write and read directly to and from blob storage. Since compute and storage are independent, SQL Data Warehouse can automatically scale storage separately from scaling compute, and vice-versa. Azure Blob storage is also fully fault tolerant, and streamlines the backup and restore process.
Data Movement Service (DMS):
Data Movement Service (DMS) moves data between the nodes. DMS gives the Compute nodes access to data they need for joins and aggregations. DMS is not an Azure service. It is a Windows service that runs alongside SQL Database on all the nodes.
Advantages of using SQL Data Warehouse:
-
SQL Data Warehouse is a massively parallel processing (MPP) distributed database system. By dividing data and processing capability across multiple nodes, SQL Data Warehouse can offer huge scalability - far beyond any single system.
-
SQL Data Warehouse separates storage and compute, which allows each to scale independently. SQL Data Warehouse can quickly and simply scale to add additional compute resources at a moment's notice
-
Using the combination of cloud-scale storage and Azure compute, SQL Data Warehouse allows you to pay for query performance and storage when you need it. Changing the amount of compute is as simple as moving a slider in the Azure portal to the left or right, or it can also be scheduled using T-SQL and PowerShell.
-
Allocation of resources to your SQL Data Warehouse is measured in Data Warehouse Units (DWUs). DWUs are a measure of underlying resources like CPU, memory, IOPS, which are allocated to your SQL Data Warehouse. Increasing the number of DWUs increases resources and performance.
-
When you need faster results, increase your DWUs and pay for greater performance. When you need less compute power, decrease your DWUs and pay only for what you need.
-
SQL Data Warehouse is based on the SQL Server relational database engine, and includes many of the features you expect from an enterprise data warehouse. If you already know TSQL, it's easy to transfer your knowledge to SQL Data Warehouse.
-
SQL Data Warehouse stores all data in Azure Premium locally redundant storage. Multiple synchronous copies of the data are maintained in the local data centre to guarantee transparent data protection in case of localized failures. In addition, SQL Data Warehouse automatically backs up your active (un-paused) databases at regular intervals using Azure Storage Snapshots.
-
SQL Data Warehouse also integrates many of the tools that SQL Server users may be familiar with. These include - Traditional SQL Server tools (SQL Server Analysis Services, Integration Services, and Reporting Services), Cloud-based tools (Azure Data Factory, Stream Analytics, Machine Learning, and Power BI) & Third-party tools which have certified integration with SQL Data Warehouse.
-
Using SQL Data Warehouse with PolyBase gives users unprecedented ability to move data across their ecosystem, unlocking the ability to setup advanced hybrid scenarios with nonrelational and on-premises data sources.
-
Finally, SQL Data Warehouse offers a product level service level agreement (SLA) as part of Microsoft Online Services SLA.
Thanks
Brijesh Kumar
Thank You
About Author
Brijesh Kumar
Database Developer
I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL
Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall
and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com