Azure SQL Data Warehouse is a cloud-based relational database that can efficiently process large data volumes using scale-out capabilities of the Azure platform. The storage size in Azur SQL Data Warehouse is adjusted independently of the computing power, which offers great flexibility and allows to fine-tune the usage based on the demand.
The costs of SQL Data Warehouse depend on the chosen tier, but it’s a relatively expensive resource. Monthly fees range from about $1100 USD/month for the cheapest tier (100 DWU) to over $65’000 USD/month for the highest one (6000 DWU).
A great way to save on Azure SQL Data Warehouse is to always choose the lowest tier that can efficiently handle the current load. In this article, we’ll show how you can automatically adjust the tier (auto-scale Azure SQL Data Warehouse) based on the demand using Netreo.
If SQL Data Warehouse is only needed during specific times of the day/week/month, it may also be beneficial to Pause/Resume it on a schedule. Read this article to learn more.
To scale Azure SQL Data Warehouse based on the load, follow these steps:
If you aren’t using Netreo yet, request a demo to have a sales representative, show you the view of an Azure subscription with Azure SQL Data Warehouse. Learn more about the setup process here.
Netreo can adjust the tier based on any metric that represents Azure SQL Data Warehouse’s load, for example, queued requests, DWU utilization, queued load count, DataIO, etc.
In this example, we use Data Warehouse Units (DWU) utilization, which is a metric tracked out of the box in the default configuration template. You can pick any other metric that allows determining the demand in your environment.
You need to define two rules to increase and decrease the available compute power based on the metric value:
3. Define an overall scaling limit
In most cases it is beneficial to define upper and lower scaling boundaries, so the automatic operations don’t spin out of control. You can define the overall limits to ensure the most expensive tiers are not used. These limits are defined as Scale Ranges.
During monitoring, Netreo picks the currently active range by evaluating its condition, which can be defined as either an Expression based on metric values or as a Schedule. This is why the order of Scale Ranges is important and they can be re-arranged easily by dragging them around.
To define an overall scale range that applies at all times:
There may be an additional requirement to allow for usage of higher tiers during typical work hours, e.g. 9 am to 5 pm during work days. To define a rule that applies only during those times:
A similar approach can be used to auto-scale other resources, such as SQL Azure, VM Scale Sets, Cloud Services, etc. Here is an example how to auto-scale Cloud Services or Web Apps based on ASB/ASQ queues lengths.
To further optimize costs, consider also Pausing/Resuming Azure SQL Data Warehouse according to a schedule.