where is data stored in dedicated sql pool

Question

where is data stored in dedicated sql pool

Ram001 1

Hi Gurus,
I have a question regarding the data storage for dedicated sql pool in synapse analytics , as we will be mounting a data lake gen2 blob storage system for synapse workspace , whenever we try to ingest data in dedicated sql pool does the data get storages in Azure data lake gen2 as a csv or parquet file in different folders that we can visually see by opening the files? or like in traditional on-premise sql server world it gets stored as data and log files?

AnnuKumari-MSFT 21,166 Reputation points Microsoft Employee

2022-09-13T09:30:14.41+00:00

Hi @Ram001 ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

AnnuKumari-MSFT 21,166 Reputation points Microsoft Employee

2022-09-13T09:30:14.41+00:00

Hi @Ram001 ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

Answer 1

Ronen Ariely 14,141 MVP

Hi @Ram001

Azure dedicated sql pool is what formally named Azure SQL DW. It is a relational database.

Check the documentation here for more information, but the short answer is in this part:

Dedicated SQL pool (formerly SQL DW) stores data in relational tables with columnar storage. This format significantly reduces the data storage costs, and improves query performance. Once data is stored, you can run analytics at massive scale.

With that said, remember that dedicated sql pool can use external tables which mean that the data in this case is stored in the external source and not internally which is the meaning of external tables. Therefore, you can use data which is not stored directly in the tables inside the dedicated sql pool.

Is this answer your question or do you need more clarification?

Ram001 1 Reputation point

2022-08-19T20:17:32.807+00:00

Thanks for the response, so when data gets stored internally , does this data reside in a data file on the same data lake gen2 storage?
Ronen Ariely 14,141 Reputation points MVP

2022-08-19T23:30:25.857+00:00

"when data gets stored internally" then the data can be stored in tables structure (like on-premises SQL Server) and data can be also stored in the storage.

Check the exact architecture in the following document:

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/massively-parallel-processing-mpp-architecture?WT.mc_id=DP-MVP-5001699
Ram001 1 Reputation point

2022-08-21T15:13:29.587+00:00

Thanks for response, when you say storage what storage is this data getting stored into , the data lake gen2 storage that we mounted on synapse analytics? if so can we access those data files ? Are they stored as mdfand ldf(kind of data files on gen2 storage)
AnnuKumari-MSFT 21,166 Reputation points Microsoft Employee

2022-09-07T14:37:40.47+00:00

Hi @Ram001 ,
In dedicated sql pool, we have the capability to create physical table which would be stored in the reserved capacity of the server as well as we have the capability to create external table that would point to the azure datalake storage.

----------

I hope this helps. If so, please click Accept Answer as accepted answer helps community as well. Thankyou

where is data stored in dedicated sql pool

1 answer

Activity