Microsoft launched Azure SQL Data Warehouse when it made the decision to use the cloud. Microsoft is pushing the Azure SQL Data Warehouse with great energy. It is a flexible database management service that combines elastic data warehouse features with it.
What is Azure SQL Data Warehouse?
Azure SQL Data Warehouse (now called Azure Synapse Dedicated SQL Pool, a cloud-based data warehouse, allows you to create and deliver a data warehouse on Microsoft Azure. Azure Data Warehouse can process large amounts of relational and non-relational data. It offers SQL data warehouse capabilities and a cloud computing platform. It supports SQL servers natively and can migrate existing SQL servers to SQL Data Warehouse. You can also use the same queries and constructs. In addition, subscribers have instant access to scale, pause, and shrink their data warehouse resources. It’s used to provide a complete enterprise-class SQL-based data warehouse solution. It can also be used in the following ways:
Migration of existing data warehouses (on-premises) to the cloud Provide a data warehouse solution to applications and services that need data storage and retrieval at run-time – such as web applications. A hybrid data warehouse solution that connects to Azure-hosted Data Warehouse and SQL Server on site.
Azure SQL Data Warehouse’s best feature is its elastic nature. It allows for scalability and the ability to manage both storage and compute resources separately. It creates a win/win platform where users only pay for what they use and when they use it. Azure SQL Data Warehouse’s computing side is based on Data Warehouse Unit (DWU), which tracks computational resources such as storage I/O and memory across all participating compute nodes. Azure SQL Data Warehouse offers a connection security feature. This allows you to limit access to specific IP addresses or IP ranges using firewall rules. Integrating with Azure Active Directory authentication (AAD) will enable you to connect to Azure SQL Data Warehouse by using identities from Azure AD. Multi-layer encryption provides protection at rest, in motion, and in use to protect your data from being misused. Additional tools are available to audit and monitor data and identify security breaches. When combined with other Microsoft tools, Azure SQL Data Warehouse offers unparalleled performance, which is a major advantage over other comparable services on the market.
Azure Synapse Dedicated SQL Pool
This can dramatically improve the performance of data warehouses that are larger than a few Terabytes in size, but there may be better solutions for smaller implementations. The underlying architecture is very different from traditional SQL Servers. This means that syntax and development methods are also different. Distributions are mapped to Compute nodes in a dedicated SQL pool. The pool remaps your distributions to the Compute nodes as you purchase more computing resources. You can import large data using simple PolyBase SQL queries and then use the distributed query engine for high-performance analytics. The dedicated SQL pool, formerly SQL DW, will provide your business with a single source of truth that allows you to integrate and analyze data faster and provides more robust insights.
What is the difference between Azure Synapse dedicated SQL pools and dedicated SQL pools in an Azure Synapse Analytics workspace?
PowerShell is one of the most confusing areas in documentation between “the dedicated SQL Pool (formerly SQLDW) and “Synapse Analytics” dedicated SQL pools. SQL DW’s original implementation uses a logical server similar to Azure SQL DB. A PowerShell module named Az.Sql is shared. This module creates a new SQL pool (formerly SQLDW) using the cmdlet New.AzSqlDatabase. It has an “Edition” parameter that allows you to specify that you want a DataWarehouse. Synapse Analytics came with a new PowerShell module from Az.Synapse when it was first released. To create a dedicated SQL pool in a Synapse Analytics Workspace, you would use New-AzSynapseSqlPool. This PowerShell module does not require you to include the “Edition” parameter, as it is only used for Synapse artifacts. A dedicated SQL pool provides T-SQL-based computing and storage. Data can be loaded, modeled, and processed in Synapse to provide faster insight. Azure Synapse offers Serverless SQL and Apache Spark pools in addition to Dedicated SQL pools. You can choose the right one based on your requirements.A serverless SQL pool allows you to query the data stored in your data lake.
What does Azure Synapse Dedicated SQL Pool do?
Azure Synapse Dedicated SQL Pool uses a scale-out architecture to distribute the computation of data across multiple nodes. You can scale compute independently of storage because compute is distinct from storage. Serverless SQL pools are serverless and scale automatically to meet query resource requirements. It adapts to changing topologies by adding, removing, or failing over nodes. This ensures that your query has sufficient resources and can be completed successfully. Synapse SQL is based on a node-based architecture. Synapse SQL uses a node-based architecture. Applications can connect to the Control node and issue T-SQL commands. This is the single point for Synapse SQL. Azure Synapse SQL control nodes use a distributed query engine that optimizes queries for parallel processing and then passes operations on to Compute nodes so they can do their work in parallel. The serverless SQL pool Control Node uses Distributed Query Processing engine (DQP), to optimize and orchestrate distributed execution. This is done by splitting the user query into smaller queries that can be executed on Compute nodes. Each task is a distributed execution unit. It retrieves data from other tasks, groups files, and reads them from storage. Compute nodes store all user data and run parallel queries. Data Movement Service (DMS), a system-level internal service, moves data between nodes to allow for parallel queries and returns accurate results. Synapse SQL uses Azure Storage for user data security. Azure Storage stores and manages your data. There is a separate charge for storage usage.
Features of Azure Synapse Dedicated SQL Pools
Here are the top features of Azure Synapse SQL Pool:
You can inquire about the data in various formats such as Parquet, JSON, and CSV in the data lake. Users can view the most recent data by using a relational abstraction. T-SQL allows you to transform the data in the lake in a simple, scalable way Data scientists can quickly examine the structure and content of lake data using OPENROWSET or automatic schema inference features. Data engineers can use the pool to explore the lake and transform, create, or simplify their data transformation pipelines. Data analysts can access the data and spark external tables via T-SQL language and other familiar tools. These tools can also be connected to a serverless SQL pool. Instantly generate BI reports by business intelligence professionals over Spark tables or data lake.
Dedicated SQL Pool Vs. Serverless SQL Pool
Serverless SQL Pool
Azure Synapse workspaces have a serverless SQL pool that acts as a query service over data lakes. It does not require additional configuration to access the data. It is entirely serverless and requires no infrastructure to set up or maintain. Scaling can be done automatically to meet resource requirements. The user pays only for the data processed and not for any reserved resources. The serverless SQL pool also creates statistics to optimize query executions. For example, when we run a query twice or run two queries with similar execution plans, these statistics can be reused.These features allow us to quickly analyze large quantities of data without copying or loading it into a particular store.
Dedicated SQL Pool
The Synapse dedicated SQL Pool is the successor to Azure SQL Data Warehouse and offers all of the enterprise data warehousing features. However, there is no serverless SQL pool. Instead, users must create and delete the Synapse dedicated SQL pool. We can also choose the resources that it will use. These resources are measured using Synapse’s dedicated SQL pools. They are called Data Warehousing Units (DWUs). A DWU refers to a combination of CPU, memory, and IO resources. The number of DWUs determines the pool’s performance and cost. Instead of being charged per query, we will be charged for any time the pool is active, regardless of how much work it has done. To avoid extra costs, dedicated pools can be stopped and restarted. We created a 100 DWU dedicated SQL pool for our test. After the pool is created, data can be loaded into it using the COPY command, PolyBase with T–SQL queries, or a pipeline. These data will be stored in columnar storage in relational tables.
Conclusion
So, that was all about Azure SQL Data Warehouse (now called Azure Synapse Dedicated SQL Pool). While a Dedicated SQL Pool may look similar to a traditional SQL Server from many angles, the underlying architecture (Massively Parallel Processing), is entirely different. This means that certain concepts and techniques are only applicable to a Dedicated SQL Pool. You may also explore the Differences between Data Lake and Data Warehouse.
