Azure Synapse vs Azure SQL DB

Question

Hello,
we are evaluating options for migrating our Datawarehouse onto Azure Cloud.

THe DW size is around 700gb.

Currently, our options that we are looking at are Azure Synapse Dedicated pools and Azure SQL DB.

Is there any guidance on what factors to consider before choosing Azure Synapse dedicated pools ? I am not sure if Synapse is suitable for any workloads.

So any pointers/guidance would be appreciated.

Thanks

Answer 1

Hi,

I recommend to use google to find posts on this topic as we cannot cover the topic in the forum. There are multiple articles which make this comparation in deep.

https://www.google.com/search?q=Azure+Synapse+vs+Azure+SQL+DB

In addition I recommend to search for the different between OLAP and OLTP, since the answer to your question directly related.

And finely, check articles about Data Warehousing and articles about big data solutions

The maximum we can do here is to point to some of these articles (searching like you) and discuss specific points. It will never be the same as an article which someone wrote in a few days or even weeks.

In any case the short answer will be that this depend on your specific needs.

Here are some points for start, but again, I recommend to search for articles on the topic. Everything that I write here are golden rule and not an advice or שbsolute facts!

(1) Azure SQL Database is basically a solution for online database modifying system (fit for OLTP) while Azure Dedicated SQL pool (formerly SQL DW) best fit for online database query answering system (fit for OLAP and Data Warehousing).

(2) When we use Azure SQL DB then we usually follows the Relational Model and try to think about normalization, but when we work with Data Warehousing then we focus on the reporting and we will store duplicate data which is ready for faster reporting (data after ETL) - In general in Data Warehousing we think about the size of the data much less.

(3) The big question that you need to ask yourself is How do you use the data? Dedicated SQL pool best fit for Data Warehousing while Azure Synapse Analytics better fit for data mining and Azure SQL Database best fit for transactional processing

(4) Dedicated SQL pool fits best for big data solution

(5) Azure SQL DB is stored in single specific machine while Azure Dedicated SQL pool can be stored in multiple machines and provide better Scale demands.

(6) The queries languages is a bit different. Azure Dedicated SQL pool is based on SQL Server Parallel Data Warehouse (PDW) and Azure SQL DB is based on SQL Server.

(7) Azure SQL DB can handle much more concurrent query users

and more...

We will need a lot more information in order to know what best fit your needs

(*) In your case you do not speak about a big database in size and you should mostly focus on the usage probably (how you use the data) and cost