question

Ivo-7530 avatar image
0 Votes"
Ivo-7530 asked annamalai commented

Azure Synapse Analytics VS Dedicated SQL Pool + ADF on A Azure SQL Server

Hello people,

I am currently investigating the added value of Azure Synapse Analytics as a DWH solution versus doing it the traditional way with a Azure SQL database and Azure Data Factory. I also noticed in Azure SQL Server there is an option to create a Dedicated SQL Pool so my question is: What is the added benefit of using Azure Synapse Analytics versus just making a Dedicated SQL Pool in your Azure SQL Server and using Azure Data factory for your ETL, since Synapse seems to be using ADF as well but a watered down version in their Synapse studio. Is there something I'm missing here? Because to me it looks like they're very similar and I don't see what value using Synapse brings to my DWH.

For context the data model is a dimensional model that isn't that complex. The data amount is also not extremely huge and the purpose this DWH mainly serves is historization and a central data source for PowerBI Reports.

Thank in advance!

sql-server-generalazure-data-factoryazure-sql-databaseazure-synapse-analytics
· 1
5 |1600 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.

Both are more or less the same. please refer to the below image for details.
a small example could be, purchasing cooking items from a small retail store(s) (Dedicated SQL Pool) vs purchasing things from the supermarket(Dedicated Pool in Synapse with bundled features in one place).163368-dedicatedsqlpoolvssynapse.png



0 Votes 0 ·

1 Answer

RohitKulkarni-6062 avatar image
0 Votes"
RohitKulkarni-6062 answered SamaraSoucy-MSFT commented

Hello Ivo

Azure Synapse Analytics is an analytics service that brings together enterprise data warehousing and Big Data analytics.
Dedicated SQL pool (formerly SQL DW) refers to the enterprise data warehousing features that are available in Azure Synapse Analytics.



Regards
RK

· 3
5 |1600 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.

Hello RohitKilkarni,
So what you're saying is that if I don't do anything with big data analytics there is no added value of Synapse analytics?
Assuming if that is what you are saying I have a follow up question that you may also be able to answer.
If my data size isn't extremely huge and my data model isn't that complex would you recommend getting a SQL database or dedicated server pool for my DWH. I know both are optimized for different things. Also is there a big difference in the Synapse version of ADF and the standalone version of ADF?

0 Votes 0 ·

The main advantage of Synapse is the integration between the dedicated pools, Spark pools, and Pipelines. Pipelines are partially based on Data Factory, but they aren't quite the same.

If your data set is small then you will likely have a better experience with Azure SQL + ADF. The minimum cost of a dedicated pool is significantly higher than Azure SQl, and you won't be making use of the big data analytics and integration the service was designed for. If you know that your dataset will become large enough, then it may be worthwhile starting with Synapse, but you can also migrate there if needed in the future.

0 Votes 0 ·

Hello Ivo,

If the data is simple OLTP then will prefer simple SQL database. Or if the data is more analytical then would prefer synapse analytics.

As per my experience i came across :

In synapse Analytics(Server less) is a Bundle we can have access to Power BI+ADF.

In standalone version of ADF we can only ETL Part.

Regards
RK

0 Votes 0 ·