How to auto-scale Azure SQL Data Warehouse to optimize costs

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:

1. Run Netreo Setup Wizard to connect to your Azure environment

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.

2. Define auto-scaling Azure SQL Data Warehouse rules

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:

• Open Configuration dialog for the Azure SQL Data Warehouse resource.
• In the Scale Adjustments tab define “ScaleUp” rule that will switch to the next higher tier when the DWU utilization goes over a certain threshold, e.g. 90 % (screenshot below).
• In the Scale Adjustments tab define “ScaleDown” rule that will switch to the lower tier when the DWU utilization drops below a certain threshold, e.g. 50 % (screenshot below).
• Specify a meaningful “Suspended period ” for the actions, e.g. 10 min. for scaling up and 20 min. for scaling down. This will allow the action to not be re-executed again within that period and allow for the metrics to adjust to the latest values.

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:

• Open the Configuration dialog for the Azure SQL Data Warehouse.
• Specify that the condition is evaluated based on Expression and provide an expression that’s always true, e.g. “1 == 1”.
• In the “Scale Ranges” tab define a rule limiting the tier, e.g. from DW100 to DW600.

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:

• Open the Configuration dialog for the Azure SQL Data Warehouse.
• Specify that the condition is evaluated based on Schedule and define a schedule that matches your requirements.
• In the “Scale Ranges” tab define a rule limiting the tier, e.g. from DW500 to DW1200.
• Adjust the order of the rules in the “Scale Ranges” tab  so that the rule evaluated according to a schedule is first.

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.

Find Out how Netreo can help with all Azure monitoring needs. Request a Demo Today