| | Azure SQL Database | Azure SQL Data Warehouse |
| Definition and Release | - In 2013, Microsoft introduced Azure SQL Database which has its origin in the on-premises Microsoft SQL Server
- Azure SQL Database is a relational database-as-a service using the Microsoft SQL Server Engine. SQL Database is a high-performance, reliable, and secure database you can use to build data-driven applications and websites in the programming language of your choice, without needing to manage infrastructure. Read More
| - In 2015 (however public availability was in July 2016) Microsoft added SQL Data Warehouse to the Azure cloud portfolio which has its origin in the on-premises Microsoft Analytics Platform System (APS).
- Azure SQL Data Warehouse is a massively parallel processing (MPP) cloud-based, scale-out, relational database capable of processing massive volumes of data. Read More
|
| Purpose: OLAP vs OLTP | - SQL DB is specifically for Online Transaction Processing (OLTP)
- This means operational data with a lot of short transactions like INSERT, UPDATE and DELETE by multiple people and/or processes. The data is most often highly normalized stored in many tables.
| - SQL DW is specifically for Online Analytical Processing (OLAP) for data warehouses
- This means consolidation data with a lower volume, but more complex queries. The data is most
|
| Storage size | - The current size limit of an Azure SQL Database is 4TB, and will probably end up around 10TB in the near future.
| - Azure SQL Data Warehouse which has no storage limit at all (only the limit of your wallet), because the storage is separated from the compute.
|
| Concurrent Queries | - SQL DB can have 6400 concurrent workers (requests)
| - SQL DW can execute a maximum of 32 queries at one moment
|
| Pricing (Update Till July 2017) | - Azure SQL DB starts with €4,20 a month
| - Azure SQL DW starts around €900, - a month excluding the cost of storage which is included in SQL DB. The storage costs for Azure SQL DW are around €125, - per TB per month. And the maximum costs of a single SQL DB are around €13500.
|
| DTU vs DWU | - SQL DB has 15 different pricing tiers which specify the number of Database Transaction Units (DTU) and the storage size/type:
- Basic
- Standard (S0, S1, S2, S3)
- Premium (P1, P2, P3, P4, P6, P11, P15)
- Premium RS (PRS1, PRS2, PRS4, PRS6)
- Basic has only 5 DTUs and the highest number of DTUs is, at the time of writing, 4000.
- the term DTU is a bit vague.
- It is a mysterious combination of RAM, CPU and read-write rates, but basically if you want to double the performance of your current database you just need to double the number of DTU's for your database.
| - SQL DW has 12 different pricing tiers and uses Data Warehouse Units (DWU) to specify the performance level.
- DWU100, 200, 300, 400, 500, 600, 1000, 1200, 1500, 2000, 3000, 6000
- The term DWU is a little less vague
- if you divide that number by 100 you have the number of compute nodes available for that pricing tier. On the other hand, the exact combination of CPU, memory and IOPS per compute note is unknown.
|
| Concurrent Connection | - SQL DB can handle 6400 concurrent logins and 30000 concurrent sessions.
| - SQL DW has a maximum of 1024 active connections
|
| PolyBase | - Azure SQL Database does not support it.
| - Azure SQL Data Warehouse supports PolyBase.
- This technology allows you to access data outside the database with regular Transact SQL. It can for example use a file in an Azure Blob Storage container as a (external) table. Other options are importing and exporting data from Hadoop or Azure Data Lake Store
|
| Replication | - SQL DB supports active geo-replication. This enables you to configure up to four readable secondary databases in the same or different location.
| - SQL DW does not support active geo-replication, only Azure Storage replication. However, this is not a live, readable, synchronized copy of your database! It's more like a backup.
|
| In Memory OLTP tables | - SQL DB supports in-memory OLTP
| - SQL DW is OLAP and does not support it.
|
| Always encrypted | - SQL DB supports Always Encrypted to protect sensitive data.
| - SQL DW does not support it.
|