Azure SQL Auto-Scaling

Why auto-scale SQL Azure?

Microsoft Azure SQL Database is a scalable database-as-a-service that provides managed SQL database capabilities to applications running in the Azure cloud.

The tier of SQL Azure database controls how many compute/storage resources are allocated to the database and how much it costs.  Moving between tiers allows SQL Azure administrators to control cost and match the power of a database to required demand. Resources available for a single database are expressed in terms of DTUs (Database Transaction Units).

In this use case, we’ll walk you through the example that shows how to automatically adjust the selected SQL Azure tier based on the actual DTU usage using CloudMonix.

Define automation rules to auto-scale SQL Azure

CloudMonix can automatically move SQL Azure database between the tiers according to user-defined conditions. In the example below, we assume that the database needs to scale between Premium tiers (P1, P2, P4 or P6), adjusting the tier based on the actual DTU usage.

Note: When defining auto-scaling rules, keep in mind that some tiers have a different maximum capacity.  An attempt to scale down to tiers that do not support current database size will result in an error.

After Setup Wizard has been successfully completed, CloudMonix will track the current DTU usage as a metric called DTU. Creating two scale-adjustment rules can move SQL Azure database between tiers:

  • open the configuration dialog for the Azure SQL Database resource;
  • on the Scale Adjustments tab add a new action that will execute the “Scale Up” command when the DTU utilization exceeds 85% for 10 min. sustained. The “Adjust by instance count” parameter specifies how many tiers CloudMonix will scale the database;
  • on the Scale Adjustments tab add a new action that will execute a “Scale Down” action that utilizes “ScaleDown” command when the DTU utilization drops below 50% for 20 min. sustained;
CloudMonix - proactive reboots
CloudMonix - proactive reboots

Define an overall scaling limit

It might be beneficial to ensure that CloudMonix never scales your database higher or lower than certain tiers.  This can be done by defining “Scale Ranges“. The following example shows how to ensure that SQL Database is never scaled below P1 and above P6 tiers:

  • open the configuration dialog for the Azure SQL Data Warehouse;
  • in the “Scale Ranges” tab define a new rule limiting the tier from P1 (Minimum Tier)  to P6 (Maximum Tier). Specify a condition that is always true, e.g. “1 = 1”.
CloudMonix - proactive reboots

A similar approach can be used to automatically scale resources based on other parameters or according to schedule, for example here we show how to automatically pause SQL Data Warehouse every day to save on costs.