By: Ken Adams
Have you wondered what the difference is between Azure SQL Database and Azure SQL Data Warehouse? In this post we’re going to highlight the key differences between the two Microsoft product offerings and point out what you need to know.
Microsoft added SQL Data Warehouse (SQL DW) to their Azure cloud platform in 2015 and kept it in limited public preview for a year. Starting in July of 2016, the product became available to the public, and it now joins SQL Database in the Azure cloud family. Comparatively, SQL Database (SQL DB) has been around for 6+ years (released in 2010) and has gone though a handful of name changes – SQL Azure, SQL Server Data Services, and now Azure SQL Database.
The main similarities between the two platforms are that both are cloud services and both are capable of hosting data warehouses. Here’s one of the biggest differences between the two: SQL DW is specifically geared towards OLAP (Online Analytical Processing) AKA data warehouses, whereas SQL DB is geared towards OLTP (Online Transaction Processing).
Depending on the kind of work you do, utilizing SQL DB instead of SQL DW and vice versa can be a key to your success. If, in your role, you’re building an application, managing an existing application, or handling where an application is performing individual inserts, updates, and deletes, then the best Azure Cloud offering for you is SQL DB. However, if you’re interested in building a data warehouse, then SQL DW is the better option. It’s important to note that you could use SQL DB to create a Data Warehouse as well.
Another key differentiator between SQL DB and SQL DW is that they are sold differently. SQL DB is sold as a relational database service that provides scalability, consistent performance, and data security. These features are available with almost no administration required from the user. This is a huge benefit because it removes the need to have on-site staff for performing database-related tasks such as managing hardware and performing database pickups. To add, SQL DB also gives you the ability to utilize traditional SQL Server tools that help ease one’s transition to the Cloud.
SQL DW, on the other hand, is sold as a “scale-out” database solution that’s capable of processing very large amounts of data. It’s built on the Microsoft massively parallel processing (MPP) architecture, which means that a task is handled by multiple processors, or nodes. To quickly explain this, each node has its own operating system as well as its own memory. The individual nodes communicate using some form of messaging interface. Data and processing in SQL DW is spread across multiple nodes so that tasks are optimized and handled in parallel.
Additionally, resources in SQL DW can be increased, decreased, paused, and resumed in matter of seconds, all with zero downtime. This capability is extremely valuable because cutting back on resources during times when loads are light means cost savings for an organization. And one last functionality of SQL DW to point out: much like SQL DB, you can also use traditional SQL Server Tools for development.
An illustration of the MPP Architecture:
Here is a side-by-side comparison of some of the features of SQL DB and SQL DW:
This post was a brief overview into the 2 cloud database offerings from Microsoft Azure – SQL Database and SQL Data Warehouse. Both are attractive in that they reduce the need for housing and managing infrastructure as well as the need for database administration. Of course, these capabilities don’t come without a price, and utilizing them could very well require you to migrate an existing database from a traditional SQL Server database.
We’ll be following up this blog post with a more technical, deeper dive into the newer SQL DW. It’s one of the newest and most intriguing offerings on the Azure platform and in case you missed it, Microsoft announced at SQL PASS 2016 that they’re offering customers a 1-month trial of Azure SQL DW!
Credit to:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-technical-overview
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-overview-what-is
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-concurrency
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage-compute-overview
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-geo-replication-overview
http://www.jamesserra.com/archive/2016/08/azure-sql-database-vs-sql-data-warehouse/