Azure Synapse dedicated SQL pool vs. Azure SQL vs SQL

%3CLINGO-SUB%20id%3D%22lingo-sub-2195347%22%20slang%3D%22en-US%22%3EAzure%20Synapse%20dedicated%20SQL%20pool%20vs.%20Azure%20SQL%20vs%20SQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2195347%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3Ecurrently%20we%20operate%20on-premises%20PostgreSQL%20DB%20that%20we%20use%20as%20our%20data%20warehouse.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20set%20up%20a%20data%20warehouse%20in%20Azure%2C%20but%20the%20Azure%20portal%20is%20bit%20confusing%2C%20as%20there%20are%20multiple%20options%3A%3C%2FP%3E%3CP%3E1)%20in%20Azure%20Synapse%2C%20there%20is%20dedicated%20SQL%20pool%20(formerly%20labeled%20as%20Azure%20Data%20Warehouse)%3C%2FP%3E%3CP%3EThen%2C%20outside%20Azure%20Synapse%20there%20are%20two%20additional%20options%3A%3C%2FP%3E%3CP%3E2)%26nbsp%3B%20Azure%20SQL%20database%20(%26nbsp%3B%2FBrowseResource%2FresourceType%2FMicrosoft.Sql%252Fazuresql%20)%20and%3C%2FP%3E%3CP%3E3)%20%22SQL%20database%22%26nbsp%3B%26nbsp%3B%20(%2FBrowseResource%2FresourceType%2FMicrosoft.Sql%252Fservers%252Fdatabases)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Farchitecture%2Fdata-guide%2Frelational-data%2Fdata-warehousing%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Farchitecture%2Fdata-guide%2Frelational-data%2Fdata-warehousing%3C%2FA%3E%3C%2FP%3E%3CP%3Edescribes%20that%20Azure%20SQL%20(%232%20above)%20uses%20symmetric%20multiprocessing%20(SMP)%20while%20%22Azure%20Synapse%20Analytics%22%20(%231)%20above%20uses%20massively%20parallel%20processing%20(MPP).%3C%2FP%3E%3CP%3EMy%20data%20needs%20are%20not%20so%20vast%20to%20utilize%20the%20MPP.%20Thus%20it%20seems%20I%20should%20be%20considering%20%232%2C%20i.e.%20outside%20the%20Synapse%20Analytics.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAzure%20SQL%20(%232)%20above%20further%20branches%20into%20%22Single%20Database%22%2C%20%22Elastic%20pool%22%20and%20%22Single%20instance%20managed%20DB%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20guessing%20that%20for%20my%20scenario%2C%20%22Azure%20SQL%20-%20Single%20Database%22%20is%20the%20best%20option.%3C%2FP%3E%3CP%3EIf%20I%20choose%20this%2C%20will%20I%20be%20able%20to%20use%20this%20storage%20in%20Azure%20Synapse%20Analytics%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFurthermore%2C%20to%20use%20Azure%20Data%20Factory%20(ADF)%20-%20if%20I%20set%20up%20%22Azure%20SQL%20-%20Single%20Database%22%2C%20should%20I%20be%20aiming%20to%20use%20Data%20Factory%20within%20Azure%20Synapse%20Analytics%2C%20or%20outside%20it%20(i.e.%20use%20ADF%20in%20Azure%20portal)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2195347%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20Database%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20dedicated%20pool%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2225917%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Synapse%20dedicated%20SQL%20pool%20vs.%20Azure%20SQL%20vs%20SQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2225917%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CBR%20%2F%3E%3CBR%20%2F%3EYou're%20right%20Azure%20SQL%20database%20is%20well%20suited%20for%20small%20needs%20.%20However%20i%20would%20suggest%20to%20see%20the%20comparison%20below%20to%20have%20better%20visibility%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fstackify.com%2Fazure-sql-database-vs-warehouse%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fstackify.com%2Fazure-sql-database-vs-warehouse%2F%3C%2FA%3E%3C%2FP%3E%3CP%3EHave%20you%20tried%20to%20estimate%20how%20much%20cost%20ASA%20with%20the%20least%20compute%20nodes%20%2FDWU%20%3F%26nbsp%3B%3CBR%20%2F%3EAzure%20Synapse%20analytics%20uses%20Azure%20storage%20to%20store%20data%20so%20No%20you%20cannot%20use%20another%20service%20for%20storage%20purpose%20.%3CBR%20%2F%3EYou%20will%20be%20able%20to%20use%20both%20in%20Azure%20Data%20Factory%20.%3CBR%20%2F%3EYou%20can%20find%20the%20supported%20linked%20services%20types%20here%20%3B%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fparameterize-linked-services%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fparameterize-linked-services%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

currently we operate on-premises PostgreSQL DB that we use as our data warehouse.

 

I would like to set up a data warehouse in Azure, but the Azure portal is bit confusing, as there are multiple options:

1) in Azure Synapse, there is dedicated SQL pool (formerly labeled as Azure Data Warehouse)

Then, outside Azure Synapse there are two additional options:

2)  Azure SQL database ( /BrowseResource/resourceType/Microsoft.Sql%2Fazuresql ) and

3) "SQL database"   (/BrowseResource/resourceType/Microsoft.Sql%2Fservers%2Fdatabases)

 

https://docs.microsoft.com/en-us/azure/architecture/data-guide/relational-data/data-warehousing

describes that Azure SQL (#2 above) uses symmetric multiprocessing (SMP) while "Azure Synapse Analytics" (#1) above uses massively parallel processing (MPP).

My data needs are not so vast to utilize the MPP. Thus it seems I should be considering #2, i.e. outside the Synapse Analytics.

 

Azure SQL (#2) above further branches into "Single Database", "Elastic pool" and "Single instance managed DB".

 

I am guessing that for my scenario, "Azure SQL - Single Database" is the best option.

If I choose this, will I be able to use this storage in Azure Synapse Analytics?

 

Furthermore, to use Azure Data Factory (ADF) - if I set up "Azure SQL - Single Database", should I be aiming to use Data Factory within Azure Synapse Analytics, or outside it (i.e. use ADF in Azure portal)?

 

 

 

1 Reply

Hi

You're right Azure SQL database is well suited for small needs . However i would suggest to see the comparison below to have better visibility
https://stackify.com/azure-sql-database-vs-warehouse/

Have you tried to estimate how much cost ASA with the least compute nodes /DWU ? 
Azure Synapse analytics uses Azure storage to store data so No you cannot use another service for storage purpose .
You will be able to use both in Azure Data Factory .
You can find the supported linked services types here ;
https://docs.microsoft.com/en-us/azure/data-factory/parameterize-linked-services