There is always a question when going through the resources available in Azure when you see both the components Azure SQL Database and Azure SQL DW .May be because of this confusion ,Microsoft renamed Azure SQL DW resource as Azure Synapse Analytics .
But looking closely at practical usage of both the resources ,it is completely different from one another in terms of Azure pricing ,performance and the unique purpose by which one cannot be used instead of the other .
Lets examine the features one by one
Azure SQL Database is a DaaS(Database as a service) using the SQL Server Engine where as Azure SQL Data warehouse can parallel process huge volumes of data .
When do we use Azure SQL Database ?
Azure SQL Databases are commonly used when we have huge OLTP transactions which are normalized and the results might need a quick turn around time as well .
For eg : if we are building the back-end for a banking /consumer products website then ,the main criteria will be to meet all the ACID properties and the tables are all highly normalized with a quicker DML turnaround.In such scenarios it is good to go with the a SQL Server /the Azure equivalent of it which is Azure SQL Server
When do we use Azure SQL DW
Azure SQL DW mainly works on the OLAP environment where huge volumes of denormalized data is stored .The data could be organized in Dim/Fact method using either star or snowflake schema as in a data warehouse .Transaction updates are very less in OLAP environment and hence these type of warehouses are mainly built for building reports such as annual report for sales ,monthly report on revenues ,yearly budget analysis and so on .
Size :The max size limit for Azure SQL DB is 4 TB whereas for Azure SQL DW there is no size limit
Pricing : In Azure SQL DB the pricing is based on DTU (Data Transaction Unit) where as in Azure SQL DW the pricing is DWU (Data Warehousing Unit) .At a high level DWU is more expensive than DTU (which is actually based on number of transactions only )
Parellel Connections : The number of concurrent sessions in Azure SQL DB is much higher than the warehouse .It can handle 6400 concurrent logins and 30000 concurrent sessions where as Azure SQL DW can handle only 1024 active connections .
Concurrent Queries : Azure SQL DB can execute 6400 concurrent queries at a time where as in Azure SQL DW a maximum of 128 concurrent queries get executed and the remaining are queued up
Polybase : Azure SQL DW supports polybase where as Azure SQL DB does not
Encryption : Azure SQL DW data is not encrypted where as Azure SQL DB supports encryption for the sensitive data
Replication : Azure SQL Database lets us replicate the Data using Geo Redundant storage whereas an Azure SQL DW does not have replication mechanism
Hope the article was helpful in understanding the basic differences between these two resources in the cloud .Based on the business needs ,one can decide whether to go with Azure SQL DB /Azure SQL DW
Note : the Azure SQL DW is renamed now in the portal(portal.azure.com) as Azure Synapse Analytics
But looking closely at practical usage of both the resources ,it is completely different from one another in terms of Azure pricing ,performance and the unique purpose by which one cannot be used instead of the other .
Lets examine the features one by one
Azure SQL Database is a DaaS(Database as a service) using the SQL Server Engine where as Azure SQL Data warehouse can parallel process huge volumes of data .
When do we use Azure SQL Database ?
Azure SQL Databases are commonly used when we have huge OLTP transactions which are normalized and the results might need a quick turn around time as well .
For eg : if we are building the back-end for a banking /consumer products website then ,the main criteria will be to meet all the ACID properties and the tables are all highly normalized with a quicker DML turnaround.In such scenarios it is good to go with the a SQL Server /the Azure equivalent of it which is Azure SQL Server
When do we use Azure SQL DW
Azure SQL DW mainly works on the OLAP environment where huge volumes of denormalized data is stored .The data could be organized in Dim/Fact method using either star or snowflake schema as in a data warehouse .Transaction updates are very less in OLAP environment and hence these type of warehouses are mainly built for building reports such as annual report for sales ,monthly report on revenues ,yearly budget analysis and so on .
Size :The max size limit for Azure SQL DB is 4 TB whereas for Azure SQL DW there is no size limit
Pricing : In Azure SQL DB the pricing is based on DTU (Data Transaction Unit) where as in Azure SQL DW the pricing is DWU (Data Warehousing Unit) .At a high level DWU is more expensive than DTU (which is actually based on number of transactions only )
Parellel Connections : The number of concurrent sessions in Azure SQL DB is much higher than the warehouse .It can handle 6400 concurrent logins and 30000 concurrent sessions where as Azure SQL DW can handle only 1024 active connections .
Concurrent Queries : Azure SQL DB can execute 6400 concurrent queries at a time where as in Azure SQL DW a maximum of 128 concurrent queries get executed and the remaining are queued up
Polybase : Azure SQL DW supports polybase where as Azure SQL DB does not
Encryption : Azure SQL DW data is not encrypted where as Azure SQL DB supports encryption for the sensitive data
Replication : Azure SQL Database lets us replicate the Data using Geo Redundant storage whereas an Azure SQL DW does not have replication mechanism
Hope the article was helpful in understanding the basic differences between these two resources in the cloud .Based on the business needs ,one can decide whether to go with Azure SQL DB /Azure SQL DW
Note : the Azure SQL DW is renamed now in the portal(portal.azure.com) as Azure Synapse Analytics
No comments:
Post a Comment