Cross region Dedicated SQL Pool data consumption

Question

Cross region Dedicated SQL Pool data consumption

Muruga MuthuKrishnan 26

Hi,

I have a requirement as below kindly show some light.

We have Dedicated SQL pool in one resource group would say for global requirement.

For local country requirements, we are using serverless sql pool, azure sql DB in different RG.

Both resides in the same Azure subscription.

We need to join the data from both dedicated SQL pool & serverless sql pool in local country.

Please suggest.

0 comments

2 answers

Your answer

Answer 1

QuantumCache 20,681 Moderator

Hello @Muruga Muthukrishnan
Adding few more points to the previous response by Vahid.

To join data from a Dedicated SQL pool and a serverless SQL pool in Azure Synapse Analytics, you can use the PolyBase feature. PolyBase allows you to query data from external data sources, including other SQL pools and databases.

PolyBase is by far the fastest and most scalable SQL Data Warehouse loading method to date, so we recommend it as your default loading mechanism.

For more inof: Read: Using PolyBase for SQL Data Warehouse loads

By using PolyBase to join the data from both SQL pools, you can avoid the need to move the data between the pools, which can be time-consuming and costly.

Muruga MuthuKrishnan 26 Reputation points

2023-10-04T11:35:37.8833333+00:00

Thanks for your response, we don't want to load the data as it leads to data redundancy. Also when you say Polybase can be used here, will it work when Dedicated SQL pool & Serverless SQL pool are in different RG without loading the data. My understanding Polybase we can use only for data loading not for virtual conumption.

Synapse link provides an option to integrate dedicated SQL pool & serverless SQL pool but it has limitation to connect only Azure SQL & on-prem SQL.

We want to explore different options which is optimal without migrate the data from one DB to another.

Thanks
QuantumCache 20,681 Reputation points Moderator

2023-10-04T14:16:29.78+00:00
Hi @Muruga Muthukrishnan

You are correct that PolyBase is primarily used for loading data into Azure Synapse Analytics and not for virtual consumption.

To join data from a Dedicated SQL pool and a serverless SQL pool in Azure Synapse Analytics without loading the data, you can use the following approach:

Create external tables in Azure Synapse Analytics that reference the tables in the Dedicated SQL pool and the serverless SQL pool. This allows you to query the data in both pools without having to move it.

Use the external tables to join the data from both pools. You can use the appropriate SQL syntax to join the tables based on the common columns.

Define the relationships between the tables in the data model. This allows you to use the related data together in visuals, tables, and other report elements.

By using external tables and SQL joins, you can avoid the need to move the data between the pools, which can be time-consuming and costly. This approach allows you to query the data in both pools without having to migrate the data from one database to another.

I hope this helps to clarify the approach you can take to join data from a Dedicated SQL pool and a serverless SQL pool in Azure Synapse Analytics.

Please accept this Answer, so that we can close this case. If you have any further questions please feel free to open a new case on this forum.

Muruga MuthuKrishnan 26

Hi Sathish,

 Thanks for your response. Do we have any documentation/reference for the suggested solution? Do we have any data limitations to achieve better performance?

QuantumCache 20,681 Reputation points Moderator

2023-10-04T17:36:01.02+00:00

Hello @Muruga Muthukrishnan

Use external tables with Synapse SQL

Create and use native external tables using SQL pools in Azure Synapse Analytics
Also see this thread for more on the relationship on Synapse dedicated SQL pool tables.

Please accept the above response and we would be able to close this escalation. Thanks for understanding.

Answer 2

Vahid Ghafarpour 23,605

Azure Synapse Analytics (formerly SQL Data Warehouse) is a fully managed, cloud-based data warehouse service that can query and join data from various sources, including Dedicated SQL Pools and Serverless SQL Pools.

0 comments

Share via

Cross region Dedicated SQL Pool data consumption

2 answers

Your answer