Sunday 30 July 2017

Azure SQL Database vs Azure SQL Data Warehouse

Case
As we slowly move from on-premises data warehouses with Microsoft SQL Server to cloud data warehouses in Microsoft Azure, we need to know more about the various options in Azure. You probably already used an Azure SQL Database, but Microsoft also introduced Azure SQL Data Warehouse. What are the differences between these two databases?
Azure SQL DB vs Azure SQL DW














Solution
Back in 2013, Microsoft introduced Azure SQL Database which has its origin in the on-premises Microsoft SQL Server. 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). This was a Parallel Data Warehouse (PDW) combined with Massively Parallel Processing (MPP) technology and included standard hardware. It is the 'big brother' of SQL Server, but with a slightly different purpose.

In this post we will briefly describe the differences between these two Microsoft Azure Services, but first Microsofts own definitions:
  • Azure SQL Database is a relational database-as-a service using the Microsoft SQL Server Engine (more);
  • Azure SQL Data Warehouse is a massively parallel processing (MPP) cloud-based, scale-out, relational database capable of processing massive volumes of data (more);

Differences
1) Purpose: OLAP vs OLTP
Although both Azure SQL DB and Azure SQL DW are cloud based systems for hosting data, their purpose is different. The biggest difference is that 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.

On the other hand 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 often stored de-normalized with fewer tables using a star or snowflake schema.

2) Achitecture
In order to make the differences more clear a quick preview of the architecture of Azure SQL Data Warehouse, where you see a whole collection of Azure SQL Databases and separated storage. The maximum number of compute notes at the moment is 60.
    Architecture SQL DW: Decouples (Blob) storage from compute (SQL DB)





















    3) Storage size
    The current size limit of an Azure SQL Database is 4TB, but it has been getting bigger over the past few years and will probably end up around 10TB in the near future. On the other hand we have the 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.

    3) Pricing
    The pricing is also quite different. Where 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 is around €13500,- where SQL DW ends around a massive €57000,- (excl. storage). But when you take a look at the architecture above, it should be no surprise that SQL DW is more expensive than SQL DB, because it consists of multiple SQL DBs.

    However, SQL DW has one big trick up its sleeve that SQL DB hasn't: you can pause it completely and then you only pay for storage. If you start your SQL DW with your ETL job and pause it right after you processed your Azure Analysis Services then you only need it a small percentage of the month.

    Note: prices are from July 2017

    4) 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, because 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.

    Because both services have a different purpose it is a bit strange to compare the hardware, but according to this MSDN blog post 1 DWU is approximately 7,5 DTU.

    But there is also some similarity: for both services you can use the same script to change the pricing tier on the fly to either give the performance a real boost when needed or the save money in the quiet hours.

    5) Concurrent Connection
    Although SQL DW is a collection of SQL Databases the maximum number of concurrent connections is much lower than with SQL DB. SQL DW has a maximum of 1024 active connections where SQL DB can handle 6400 concurrent logins and 30000 concurrent sessions. This means that in the exceptional case where you have over a thousand active users for your dashboard you probably should consider SQL DB to host the data instead of SQL DW.
    For more details see the SQL DB Recource Limitations and SQL DW Recource Limitations.

    6) Concurrent Queries
    Besides the maximum connections, the number of concurrent queries is also much lower. SQL DW can execute a maximum of 32 queries at one moment where SQL DB can have 6400 concurrent workers (requests). This is where you see the differences between OLTP and OLAP.
    For more details see the SQL DB Recource Limitations and SQL DW Recource Limitations.

    7) PolyBase
    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. Although SQL Server 2016 also supports PolyBase, Azure SQL Database does not (yet?) support it.

    8) Query language differences
    Although SQL DW uses SQL DB in the background there are a few minor differences when quering or creating tables:
    - SQL DW cannot use cross databases queries. So all your data should be in the same database.
    - SQL DW can use IDENTITY, but only for INT or BIGINT. Moreover the IDENTITY column cannot be used as part of the distribution key.
    - Also see this SQL DW list of unsupported table features.

    9) 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.

    10) In Memory OLTP tables
    SQL DB supports in-memory OLTP. SQL DW is OLAP and does not support it.

    11) Always encrypted
    SQL DB supports Always Encrypted to protect sensitive data. SQL DW does not support it.

    Conclusion
    Although Azure SQL DB looks much cheaper on a monthly basis, this doesn't mean you should always choose SQL DB by default. One big advantage is that you can pause SQL DW. For example a stage database is only used during the ETL process. Why should you always have this database up an running? Or why should your datamart stay online 24*7 if your end users only use Analysis Services to browse the data.

    On the other hand the original purpose of both services is different (OLTP vs OLAP), but this doesn't mean you should always use Azure SQL DW for your data warehouses. Depending on several factors like data size, complexity, required up-time and budget, Azure SQL DB could also host your data warehouse. You could even mix them in your project. For example Stage and Historical/Persistent stage in Azure SQL DW and your Datamart in Azure SQL DB.



    Please leave a comment if you know more significant differences that are worth mentioning.

    23 comments:

    1. Hi Joost,

      Good blog and comparision! I'm curious how you look at the IaaS solution in Azure (VM + SQL Server) as alternative. It's not as flexible as the PaaS solutions, and you need someone to maintain the server, but the biggest advantage in my opinion is the fact that you can still use SSIS and SSAS however you please, and it's much cheaper. The gap between SQL DWH and SQL on-premise is still to big if you ask me. Migrating an on-prem DWH to a PaaS solution is not so evident, as you have to rewrite all your ETL to stored procedures. Whenever I tell a client that, in order to keep the costs down, they would 'pause' there DWH, this doesn't play well. Anno 2017 you want to have your data available all the time, all the layers (even staging). What's your take on this?

      ReplyDelete
      Replies
      1. Thanks! The short answer... It depends.... :-)

        Azure SQL DWH and an on premises SQL DB are hard to compare because SQL DWH is more like an appliance. Therefor hosting a small data warehouse in SQL DW could be way more expensive then the on-premises SQL DB. Especially for a 24*7 uptime system. In that case an Azure VM with SQL DB or just Azure SQL DB is a cheaper alternative. The Azure VM is an often seen (intermediate?) step to get ride of the on-premises machines.

        On the other hand, for an on-premises SQL DB you have much more costs then just the licensing costs. You have to maintain the database, Windows, the machine. The machine needs to stay somewhere, needs power, needs to be protected, needs a hardware upgrade once a while, etc. etc. It is often hard to compare the actual costs.

        And there are alternatives for pausing the system to save some money. You can also downscale it in the quiet hours. Or we just need to give up some of our requirements (like having staging available 24*7) to save money.

        And hopefully Microsoft will come up with an alternative for SSIS on the VM since they already introduced Azure Analysis Services this year (fingers crossed).

        Delete
      2. Hello Niek,
        Another option would be to place the Datawarehouse environment in a Cloud Solution. During one of my assignments I migrated the entire datawarehouse to a cloud environment which in turn was connected to cloud environment in which the workplaces (thin clients) for the business was placed. That enabled the customer to go from on-premise to a cloud solution and have their BI environment available at all times and up-to-date (without having to turn off the environment to reduce costs).

        Delete
    2. Also make sure to check the Azure SQL DW Anti-Patterns paragraph where Microsoft has 10 scenarios where Azure SQL DW isn't the best option. For example:

      -Small datasets (less than 250GB)
      -Power BI direct query requiring dashboard performance

      ReplyDelete
    3. Good blog, really helpfull, it covers most of the challenges for deciding dw and SQL db and sql dw. Thanks for sharing details.
      I have a question, on sql db and sql dw connection. As you mentioned that sql dw don't ha e capability to execute cross databases that meen dw to dw only or dw to db also?

      ReplyDelete
      Replies
      1. DW cannot query other databases (DW or DB) on the same server. Therefore most people will put various data layers in one DW database to overcome that.

        Delete
    4. Very Good Article

      ReplyDelete
    5. Very nice article. Thank You for sharing.

      ReplyDelete
    6. Awesome blog !!! Thansk for sharing :)

      ReplyDelete
    7. Hi Joost,

      I have a database of an application on Azure database. And i have a plan to create a datawarehouse with many other systems and my existing DB on azure is one of them.

      My plan is to create my DWH on azure DWH. By replicating Azure db (using its geo-replication feature) that already exist to the new datawarehouse(this will save part of my ETL efforts) and ETL other Databases which are not on azure to the azure DWH.

      My question here is on feasibility of the above? Specifically is it possible to replicate(real time) a database to datawarehouse on Azure?

      ReplyDelete
    8. Nice Article. Keep posting. Thanks

      ReplyDelete
    9. Hi Joost, great article! I have one question: is it possible to create an application on SQL DB and port it to SQL DWH for clients that require more resources? Are the data schemes and querying language compatible?

      ReplyDelete
      Replies
      1. SQL DWH is not suitable for applications / OLTP (Online Transactional Processing). For more resources you just have to give your SQL DB more power.

        Delete
    10. HI Joost, I was looking for some info on a case to put a DWH in Azure SQL database and google found your blog. Good blog. One of the issues I have with Azure SQL Database is the logging: it is full and for DWH you want it simple. So Azure SQL database (single) seems not an optimal solution for DWH?! What is your opinion on that?

      ReplyDelete
      Replies
      1. That's a big downside of using SQL DB. However I'm not experiencing a lot of problems with it especially with the smaller data warehouses, but for some clients we used an Azure SQL Managed Instance instead.
        You could upvote this suggestion

        Delete
    11. Hi joost, need your help, I have around 15 TB on-prem oracle data which I need to migrate to azure SQL.This data will double in each coming year. Pls suggest me which one I should select az SQL Vm or azure data warehouse? Considering cost, security, performance and migration easiness.

      ReplyDelete
    12. Nice article, thanks for sharing

      though i want to know how complicated process to move from SQL server to SQL DWH - does dump and load work from sql DB to SQL DWH

      ReplyDelete
    13. Can SQL DB support Cross database queries?

      ReplyDelete
    14. Cross database queries are not available for Azure SQL Databases (even if it looks they are on the same server). There are alternatives like Elastic Database Queries, but that one has drawback that it is only working with a SQL User account.

      ReplyDelete
    15. When SQL DW supports Polybase , then what is the need of placing data in lake/Blobs/SQL DB ..?

      ReplyDelete
    16. Polybase just allows you to store your data elsewhere or to use TSQL to query various sources such as files, oracle databases or teradata databases. Also see documentation https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver15

      ReplyDelete
    17. Millions thanks for this blog. Very helpful. I have client where I am creating data warehouse for Power Bi reporting database size is only 30 gb. I need to perform various ETL task using SSIS. Based on my understading Azure SQL Db will be cheaper option . How SSIS and DQS works with Azure SQl DB? Thanks in advanced

      ReplyDelete
    18. Hey, does hyperscale azure sql database supports cross database queries

      ReplyDelete

    All comments will be verified first to avoid URL spammers. यूआरएल स्पैमर से बचने के लिए सभी टिप्पणियों को पहले सत्यापित किया जाएगा।