Synapse Workspaces SQL Pool Storage - New vs Existing

Question

Hi All,

Would a SQL Pool created as part of a Synapse Workspace perform any differently to an exiting SQL Pool from a different region? Documentation states that the SQL Pool uses Azure Storage but does not specifically state if that's server specific storage, or say the Data Lake that the workspace exists on.

Would a SQL Pool created as part of a workspace perform any better at ingestion data from the workspace data lake; than an existing SQL pool in the same region? Would that performance degrade if that existing SQL Pool was in a neighbouring region?

Answer 1

Welcome to the Microsoft Q&A platform.

There are two types on SQL Pools in Azure Synapse Analytics.

Q: What is a good use case for Synapse SQL pool?

SQL pool is the heart of your data warehouse needs. It's the leading data warehouse solution in price/performance. SQL pool is the industry-leading cloud data warehouse solution because you can:

• serve a large and mixed variety of workloads without impact in performance thanks to high concurrency and workload isolation
• secure your data easily through advanced features ranging from network security to fine-grain access
• benefit from a wide range of eco-system

Q: What is a good use case for SQL on-demand in Synapse?

SQL on-demand is a query service over the data in your data lake. It enables you to democratize access to all your data by providing a familiar T-SQL syntax to query data in place, without a need to copy or load data into a specialized store.

Use case examples include the following:

• basic discovery and exploration - provides data analysts, emerging data scientists and data engineers with an easy path to first insight into data living in their data lake with schema on read T-SQL queries
• logical data warehouse - data analysts can run full expressiveness of T-SQL language to directly query and analyze the data residing in Azure Storage and use familiar BI tools (e.g., Azure Analyses Services, Power BI Premium, etc.) to refresh dashboards by rerunning Starlight Query queries
• "single query" ETL - allows data engineers to transform Azure Storage based data from one format to another, filter, aggregate, etc. in massively parallel processing fashion, persist query results to Azure Storage and make them immediately available for further processing in Starlight Query or other services of interest

Reference: Azure Synapse SQL architecture

Hope this helps. Do let us know if you any further queries.

----------------------------------------------------------------------------------------

Do click on "Accept Answer" and Upvote on the post that helps you, this can be beneficial to other community members.