where is data stored in dedicated sql pool

Ram001 6 Reputation points
2022-08-19T17:52:05.707+00:00

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?

Azure Synapse Analytics
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.
4,255 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Ronen Ariely 15,081 Reputation points
    2022-08-19T19:59:51.733+00:00

    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?

    1 person found this answer helpful.

  2. ElectricLlama 116 Reputation points
    2023-11-07T11:59:28.1733333+00:00

    All these answers are useless. AFAIK the data is stored in a native database file (.MDF, .NDF) I imagine if you could get to the blob storage (which you can't) you'd see these MDF files. These are proprietary binary files which can only be written and read to by MS SQl Server. They are much faster and more efficient than CSV, parquet or delta files.

    There is no documentation supporting this anywhere. I am just assuming this because this product is an ofshoot of standard on prem SQL Server.

    1 person found this answer helpful.
    0 comments No comments