Update February 2020: Azure SQL Data Warehouse is now part of the Azure Synapse analytics service.
Recently I was asked what the difference was between Azure SQL Database (SQLDB) and Azure SQL Data Warehouse (SQLDW). If you connect to them both via Management Studio there doesn't seem to be much difference, but the real answer is 'a lot'.
In traditional on-premise installations of SQL Server, the production transactional (OLTP) databases are sat on SQL Server Database Engine and the data warehouse (OLAP) system is commonly sat on a different server but using the same installation media. These one server systems with shared resources are known as SMP (Symmetric Multiprocessing) and in this scenario, there is no difference in the software installed on the OLTP and the OLAP servers; but in Azure this is not true.
SQL Database is the Platform as a Service (PaaS) offering of an OLTP database. If you are not familiar with PaaS this means you do not have to maintain the infrastructure or the operating system, you just need to be concerned with the SQL Server side. It is important to note that Azure SQL Database is a single database; Azure still has the concept of a 'SQL Server' but this can be thought of more as a container for a number of Azure SQL Databases which sit on it.
SQL Data Warehouse on the surface behaves the same way, it is also a SQL Server PaaS offering of a single database but under the covers it’s a very different story. SQLDW was built for OLAP systems. These systems behave very differently to the single lookups performed by OLTP databases, instead performing large scans over many rows to bring back aggregations. SQL Data Warehouse has been designed as a Massively Parallel Processing (MPP) system, something that is more unusual in relational database systems, although Microsoft does have an on-premise MPP offering in the form of the Analytics Platform System (APS).
MPP systems are made up of multiple 'nodes' each with their own resources and they work together to provide increased performance. In the case of SQLDW these nodes are actually Azure SQL Databases.
Because of these differences in architecture this leads to differences elsewhere. MPP is designed with large queries and batch processes in mind, but it doesn't expect there to be hundreds of users running them which again is reflected in the different offerings in Azure. It's no surprise that SQLDW starts off more expensive and, given that to increase capacity you just add more 'nodes' (Scale Out) rather than the traditional way of adding more memory or faster disks to servers (Scale Up), it means the top limit of cost is a lot higher. There are also some challenges when implementing MPP which results in some of the functionality not being available yet, below I've put the highlights so you can easily see the differences.
| SQL Database | SQL Data Warehouse | |
| Scalability | Scale Up (Vertical) | Scale Out (Horizontal) |
| Units of Performance | Database Transaction Units (DTU) | Data Warehouse Units (DWU) and Compute DWU (cDWU) |
| Max Size | 4TB | Up to 1 Petabyte with compression (240TB on disk) |
| Pricing Range per month (approx.): | £4 - £15k per month | £900 - £130k but you are able to pause it to reduce cost |
| Designed for | OLTP | OLAP |
| Architecture | SMP | MPP |
| Backup & Recovery | Geo Redundant Backup every 5-10 minutes, point in time recovery, readable replicas | Geo Redundancy Backup every 24hrs |
| Concurrent Sessions | 30000 | 1024 |
| Concurrent Queries | 6400 | 32 |
| PolyBase Support | NO | YES |
| JSON, XML and Graph Support | YES | NO |
| Encryption | Always Encrypted and TDE | TDE |
| In Memory OLTP | YES | NO |
| Cross database queries | YES | NO |
For more information on capacity limits please see the following
In summary the names are fairly self-explanatory, SQLDW is built to replace a traditional SQL Server Data Warehouse Database and SQL Database is built to replace the traditional OLTP database. So, although they are both built on the same technology you should ensure you pick the appropriate option for your workload needs.
Coeo's Architecture Workshop can help you discover the most cost-effective and appropriate data platform strategy for your business. Click below to find out more: