Synapse SQL Pools
Azure Synapse Analytics provides two SQL engines, called pools, that share a name and T-SQL syntax but work very differently. A dedicated SQL pool — formerly Azure SQL Data Warehouse — is provisioned warehouse compute with its own storage. A serverless SQL pool is an on-demand query service over files in the data lake.
How they differ
| Dedicated SQL pool | Serverless SQL pool | |
|---|---|---|
| What it is | A collection of analytic resources provisioned for a warehouse | A query endpoint provided with every Synapse workspace |
| Data | Loaded into its own relational tables — clustered columnstore by default, though heap and clustered rowstore index tables are also possible | Stays in the lake; only metadata objects are kept |
| Sizing | Chosen in Data Warehouse Units (DWUs) | No infrastructure to set up or clusters to maintain |
| Billing | For the provisioned resources | For the amount of data each query processes |
| Cost risk | Paying for reserved compute that sits idle | Inefficient queries that scan more data than needed |
| Suits | Steady, performance-sensitive warehouse workloads | Exploration, ad hoc queries, and logical views over lake files |
The billing difference drives design. With a dedicated pool, the question is whether the reservation is used; with serverless, it is whether each query reads only what it needs — which puts file formats, partitioning, and filtering in front of cost.
Where they stand now
Azure Synapse Analytics remains in support, while Microsoft recommends Fabric Data Warehouse for new data-warehousing workloads and provides a migration path for existing dedicated pools. Support status and the recommended starting platform answer different planning questions. Existing teams can assess migration against their required features and operating conditions.
For teams considering the move, Microsoft’s migration guidance notes that typically only data and metadata are migrated, with elements such as indexes handled by Fabric automatically, and that differences in T-SQL surface area and data types can require code changes. Tables do not all share one structure either, so record each table’s actual definition before migrating. Those differences, together with how capacity units replace DWUs as the unit of compute, are worked through in Microsoft Fabric Architecture: OneLake, Capacity, and the Move from Synapse.
References
- Microsoft Learn, What is dedicated SQL pool (formerly SQL DW)?
- Microsoft Learn, Serverless SQL pool in Azure Synapse Analytics
- Microsoft Lifecycle, Azure Synapse Analytics
- Microsoft Learn, Indexing tables in dedicated SQL pool
- Microsoft Learn, Migration Strategy and Planning for Azure Synapse Dedicated SQL Pools to Fabric Migration
Discover more from Insightful Data Lab
Subscribe to get the latest posts sent to your email.