|
|
Synapse |
Azure SQL DB |
|
Use Case |
Azure Dedicated SQL pool
(formerly SQL DW) best fit for online database query answering system (fit
for OLAP and Data Warehousing). |
Azure SQL Database is optimized
for doing CRUD operations (Create, Read, Update and Delete) that you
typically perform from an application. |
|
Architecture |
Azure Synapse Analytics
uses massively parallel processing
(MPP). This means that a query is processed by a
dedicated node that has its own CPU and Memory. Azure Dedicated SQL pool
can be stored in multiple machines and provide better Scale demands. |
Azure SQL uses symmetric
multiprocessing (SMP)
|
|
Active geo-replication |
No |
Yes |
|
Dynamic Data Masking |
No |
Yes |
|
Data Encryption at rest |
Yes |
Yes |
|
Polybase T-SQL queries |
Yes |
Yes |
|
Automatic Tuning |
No |
Yes |
|
Ability to pause and resume |
Yes |
No |
|
Max amount of data per
database |
1PB |
4TB |
|
Max concurrent open
sessions |
1024 |
30000 |
|
Max concurrent queries |
32 |
6400 |
|
Use for application
database |
No |
Yes |
|
Use for data warehouse with
large amounts of data and small amounts of users |
Yes |
No |
|
Use for data warehouse with
data max 4TB and large amount of users |
No |
Yes |
|
Triggers |
No |
Yes |
|
Partitioning methods |
Sharding, horizontal
partitioning |
tables can be distributed
across several files (horizontal partitioning); sharding through federation |
|
Foreign keys |
No |
Yes |
|
Scalability |
Scale Out (Horizontal) |
Scale Up (Vertical) |
|
Units of Performance |
Data Warehouse Units (DWU)
and Compute DWU (cDWU) |
Database Transaction Units
(DTU) |
|
Max Size |
Up to 1 Petabyte with
compression (240TB on disk) |
4TB |
|
Pricing Range per month
(approx.): |
£900 - £130k but you are
able to pause it to reduce cost |
£4 - £15k per month |
|
Backup & Recovery |
Geo Redundancy Backup every
24hrs |
Geo Redundant Backup every
5-10 minutes, point in time recovery, readable replicas |
|
Concurrent Sessions |
1024 |
30000 |
|
Concurrent Queries |
32 |
6400 |
|
JSON, XML and Graph Support |
No |
YES |
|
Encryption |
TDE |
Always Encrypted and TDE |
|
In Memory OLTP |
No |
YES |
|
Cross database queries |
No |
YES |
