Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,172 questions
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(99.2, 65%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECB%3C/text%3E%3C/svg%3E)
Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!
I need to query tables from two distinct Serverless SQL Pools. Each Serverless SQL Pool lives in a separated Synapse Workspace. Is there a way to do that ?
Currently, querying tables across two distinct Serverless SQL Pools in separate Synapse Workspaces directly is not supported.
I see the new Elastic Query which is in preview state.
The Elastic Query feature you mentioned is in preview and primarily designed for Azure SQL Database, which may not directly apply to Serverless SQL Pools in Azure Synapse Analytics.
One workaround is to create external tables pointing to different data-sources/location, but I will be have to duplicate the table entries between the two Serverless SQL Pools.
As a workaround, creating external tables that point to different data sources is a viable option. However, this approach would require duplicating the table entries between the two Serverless SQL Pools, which may not be ideal.
Is there any other option available?
Alternative Approaches - You can query tables from two distinct Serverless SQL Pools in separate Synapse Workspaces by using Azure Data Lake Storage (ADLS) as a shared data source. Both Serverless SQL Pools can access the same underlying data files stored in ADLS. You can utilize external tables or the OPENROWSET function to query this shared data without duplicating the table entries.
This approach allows you to avoid data duplication while enabling both Serverless SQL Pools to query the same data. Additionally, you can implement data movement workflows using Azure Data Factory or Synapse Pipelines to consolidate data into a single SQL Pool or central storage for analysis.
Benefits of this approach
Centralized Data Management - ADLS serves as a single source of truth for your data, making it easier to manage and maintain.
Cost Efficiency - Reduces storage costs by avoiding data duplication.
Flexibility - Both Serverless SQL Pools can query the same data, allowing for diverse analytical workloads without data silos.
Scalability - ADLS can handle large volumes of data, making it suitable for big data scenarios.
Please refer to the links provided below, as they might offer some insights that could help you address your question.
- Best practices for serverless SQL pool in Azure Synapse Analytics
- Serverless SQL pool in Azure Synapse Analytics
- How to use OPENROWSET using serverless SQL pool in Azure Synapse Analytics
I hope this information helps. Please do let us know if you have any further queries.
If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.