question

GrantCampbell-2628 avatar image
GrantCampbell-2628 asked ·

Synapse Workspaces SQL Pool Storage - New vs Existing

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?

azure-synapse-analytics
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
PRADEEPCHEEKATLA-MSFT answered ·

Welcome to the Microsoft Q&A platform.

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

8577-azuresynapse-sqlpoolvsdemand.jpg

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.



3 comments Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@GrantCampbell-2628 Just checking in to see if the above answer helped. If this answers your query, do click “Accept Answer" and Up-Vote for the same. And, if you have any further query do let us know.


0 Votes 0 · ·

@GrantCampbell-2628 Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.

0 Votes 0 · ·

Hi @PRADEEPCHEEKATLA-MSFT

Thank you for your answer but my question is more to do with the storage structure of a SQL Pool Gen2; if there's any difference between a Synapse workspace Synapse SQL Pool or an independent Synapse SQL Pool as to where data is stored. Documentation states that a Synapse SQL Pool & Synape Spark Pools share the same data lake when working together and I was wondering if that meant that a workspace SQL Pool 'lived' ontop of the workspace data lake. If so, this would provide a large performance uplift in ELT processes. While your answer is appreciated; I need a little more detail to satisfy my question.

0 Votes 0 · ·