Azure SQL Data Warehouse DWU vs Azure SQL DTU

问题:

I am considering migration from Azure SQL to Azure SQL Data Warehouse. It seems to offer some of the features that we need, however price is a concern for starting small. 100 DWU Data Warehouse is priced considerably higher ($521/month) than a seemingly comparable 100 DTU Azure SQL S2 tier ($150/month).

To make sure I am comparing apples to apples, can someone shed some light on how DWU compare to DTU (assuming basic configuration with a single database)?

Edit: to everyone who is inclined to answer that Azure SQL DW and Azure SQL are not comparable and therefore it makes no sense to compare DTU to DWU: then how does it make sense to (talk about migration) to DW?

回答1:

For what it's worth, 1 DWU = 7.5 DTU with respect to server capacity

When you look at the server instance that you provision a DW instance on:

• 100 DWU instance consumes 750 DTUs of server capacity
• 400 DWU instance consumes 3,000 DTUs of server capacity

While this information is interesting, it may not be very useful in terms of comparing pricing because DW pricing is exclusively based on DWU, while Azure SQL pricing is the combination of DTU and database size.

回答2:

You can't and really shouldn't compare the two for the same workload; they're designed for different things based on completely different architectures. As such, DTU and DWU are not comparable measures. Also, how deeply have you looked into the technical differences? The high level features are not the major issue, details are what might wreck your app (e.g. can you live with a limited TSQL surface area or transaction isolation level?)

Azure SQL DB is intended to be a general purpose DB as a service. The few feature gaps aside, you should think about Azure SQL DB functionally the same way you do SQL Server, minus a lot of the administrative tasks and with a different programming model. Works great for OLTP apps and most reporting apps (or mixed) but not so great for complex analytical apps against with very large datasets (can't really store that much in SQL DB anyway).

SQL DW is intended for data warehousing, analytical type workloads. Its MPP architecture is particularly well suited for complex queries against very large data sets. It will not perform well for typical OLTP applications that have lots of small or singleton queries especially when it's a mix of insert, update and delete operations. If you get a trial instance of SQL DW, you can easily test and verify the behavior for your workload compared to what it currently looks like on SQL DB.

SQL DW also has some limitations on its TSQL surface area, types, concurrency, isolation levels (deal breaker for almost all OLTP apps), etc... so be sure to look into the documentation to get the whole picture as you evaluate feasibility. It might work great but I suspect it's not the best solution if you're running an OLTP workload. Reporting/analytical type workloads however might find a happy home in SQL DW.

回答3:

The best way to figure out what you need is to look at your current IO requirements. Data Warehouses tend to be IO hogs and consequently are optimized by maximizing IO throughput. The DWU Calculator site walks you through the process of capturing a your disk metrics and estimates how many DWUs you need to fulfill your workload.

http://dwucalculator.azurewebsites.net/