azure sql server vs synapse dedicated sql pool
Azure Synapse Dedicated SQL Pool
Azure Synapse Dedicated SQL Pool (Previously Azure SQL Data Warehouse), a massively parallel processing database similar to other columnar-based scale-out database technologies such as Snowflake, Amazon Redshift, and Google BigQuery. To the end-user it is much like a traditional SQL Server, however, behind the scenes it distributes the storage and processing of data across multiple nodes. While this can drastically improve performance for data warehouses larger than a few terabytes, It may not be an ideal solution for smaller implementations. Because the underlying architecture is so drastically different, the syntax and development methodologies also differ from traditional SQL Server
Compute vs Storage Costs
Azure Synapse Analytics helps users better manage costs by separating computation and storage of their data. Users can pause the service, releasing the compute resources back into Azure. While paused, users are only charged for the storage currently in use (roughly $125 USD/Month/Terabyte). During this time, your data remains intact but unavailable via queries. Resuming the SQL Pool re-allocates compute resources to your account, your data comes back online and charges resume.
Similar to Azure SQL DB’s DTUs, Computation in a Synapse SQL Pool is Measured by Data Warehouse Units (DWUs). Adjusting DWUs will increase or decrease the number of available compute nodes as well as the relative performance and cost of the service.
Architecture
Processing of data in a Synapse SQL Pool is distributed across many nodes of different types.
The control node accepts end-user queries, then optimizes and coordinates these queries to run in parallel across the multiple compute nodes.
While a traditional SQL database is dependent on the computational resources of a single machine, a Synapse SQL Pool can distribute the processing of tables across up to 60 compute nodes depending on the service level. The more DWU’s you’ve assigned, the more compute nodes will be used.
To maintain data integrity while scaling, data is maintained in Azure Storage separate from the control & compute nodes. In addition, to further optimize the processing of large data sets, tables are always spread across 60 distributions (more about this in the next section).
Data Movement Service (DMS) manages the movement of this data across the compute nodes.
Azure Synapse dedicated SQL pool (formerly SQL DW)
Dedicated SQL pool (formerly SQL DW) refers to the enterprise data warehousing features that are generally available with Azure Synapse
Dedicated SQL pool (formerly SQL DW) represents a collection of analytic resources that are provisioned when using Synapse SQL. The size of a dedicated SQL pool (formerly SQL DW) is determined by Data Warehousing Units (DWU).
Once your dedicated SQL pool is created, you can import big data with simple PolyBase T-SQL queries, and then use the power of the distributed query engine to run high-performance analytics. As you integrate and analyze the data, dedicated SQL pool (formerly SQL DW) will become the single version of truth your business can count on for faster and more robust insights.
Please note: Azure Synapse Analytics dedicated SQL pool is different from Azure Synapse Dedicated SQL Pools (formerly SQL DW) .
Azure Synapse Dedicated SQL Pools (formerly SQL DW) is on the Azure SQL logical server.
Synapse Serverless SQL pool
A serverless SQL pool stores and computes large-scale data in a distributing data processing system. The Azure serverless SQL pool doesn’t mandate users to set up infrastructure and maintain clusters. Instead, it uses a pay-per-use model, which means there is no cost incurred for resources not used, and the charge made is based on the data processed by each query run.
Azure SQL Database
Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement. Azure SQL Database is always running on the latest stable version of the SQL Server database engine and patched OS with 99.99% availability. PaaS capabilities built into Azure SQL Database enable you to focus on the domain-specific database administration and optimization activities that are critical for your business.
Deployment models
Azure SQL Database provides the following deployment options for a database:
•Single database represents a fully managed, isolated database. You might use this option if you have modern cloud applications and microservices that need a single reliable data source. A single database is similar to a contained database in the SQL Server database engine.
•Elastic pool is a collection of single databases with a shared set of resources, such as CPU or memory. Single databases can be moved into and out of an elastic pool.
Purchasing models
SQL Database offers the following purchasing models:
•The vCore-based purchasing model lets you choose the number of vCores, the amount of memory, and the amount and speed of storage. The vCore-based purchasing model also allows you to use Azure Hybrid Benefit for SQL Server to gain cost savings. For more information about the Azure Hybrid Benefit, see the Frequently asked questions section later in this article.
•The DTU-based purchasing model offers a blend of compute, memory, and I/O resources in three service tiers, to support light to heavy database workloads. Compute sizes within each tier provide a different mix of these resources, to which you can add additional storage resources.
Service tiers
Azure SQL Database offers three service tiers:
• The General Purpose/Standard service tier is designed for common workloads. It offers budget-oriented balanced compute and storage options.
• The Business Critical/Premium service tier is designed for OLTP applications with high transaction rates and low latency I/O requirements. It offers the highest resilience to failures by using several isolated replicas.
• The Hyperscale service tier is designed for most business workloads. Hyperscale provides great flexibility and high performance with independently scalable compute and storage resources. It offers higher resilience to failures by allowing configuration of more than one isolated database replica.
When should I consider Azure Synapse Analytics
Greater than 1TB Database
Since Tables in Azure Synapse are always spread across 60 distributions, performance gains are not typically realized until your data warehouse is more than 1-5 TB. As a general rule data warehouses of less than 1 TB will perform better on Azure SQL DB than on DW.
Greater than Billion Row Tables
Database size is not the only consideration. Since Distribution happens at the table level if all your tables are less than 100 Milllion rows, You may not see a significant performance boost from Azure Synapse.
128 Concurrent Queries
Once the SQL Pool has receives more than 128 concurrent queries, it will begin to queue them in a first-in-first-out basis. Azure SQL DB and Analysis Services can support much more concurrent queries. To resolve this limitation, Microsoft recommends feeding data into an Analysis Service Server for larger demand.
DWH Tuning
How tables are distributed should be based on how users query the data and the approach can drastically effect performance. So the Synapse SQL Pool is not a magic fix to all your query performance problems. Just like a data warehouse running on traditional SQL Server, it requires monitoring and tuning of the distribution keys, indexes, caching, and partitions to ensure best performance.
****Reference documents:****
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-overview-what-is
https://support.timextender.com/hc/en-us/articles/360033341932-What-is-Azure-Synapse-Dedicated-SQL-Pool-
https://learn.microsoft.com/en-us/answers/questions/649144/azure-synapse-vs-azure-sql-db.html
https://www.royalcyber.com/blog/data-services/dedicated-sql-pool-vs-serverless-sql/
https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-database-paas-overview?view=azuresql