Azure Auto-Scale Database

7
votes

I am using the Azure Cloud Services with a single Azure Database. I have the pricing tier set to S4 which gives me 200 DTUs and 250GB. The issue is that all of my worker and web roles are set to auto-scale with load except for the Database. When I do a load-test, everything up-scales automatically until the Database starts choking on queries which is causing a bottle-neck.

There isn't any auto-scaling options for Databases that I can find. I found this video from Microsoft: Azure SQL Database dynamically scale-up or scale-down

but it basically says that you need to go to your Azure portal and manually scale-up to a larger pricing tier. It doesn't mention anything about automatically scaling up or down.

I also looked into Elastic Pools but these seem to be more for using many Databases at the same time, rather than just 1 Database.

So my question is:

How do you automatically scale-up and down a single Azure database?

2
azureazure-sql-database
Possible duplicate of Autoscaling Azure SQL Database – Edward Brey

2 Answers

6
votes

You have several options like Alberto mentioned you can use REST API and Of course azure run-books/webhooks to configure the alert rules.

Let me explain my process here.

  1. Deploy the azure run-books for the changing the performance tier in AzureAutomation -Try this
  2. Create some webhooks for triggering your run-books anytime -Webhooks for Azure runbook
  3. From your SQL DB, create an alert rule with the condition when DTU used greater than or equal to some value (say 1600) the call the corresponding webhook to change the tier dynamically.

enter image description here

3
votes

You can test if resource consumption fits the resource limits as shown below:

SELECT
(COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent > 80 THEN 1 ELSE 0 END) * ,(COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent > 80 THEN 1 ELSE 0 END),
(COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent > 80 THEN 1 ELSE 0 END FROM sys.dm_db_resource_stats

service level objective (SLO) of 99.9% <= go to next tier

If SLO of 99.9%, you can then scale up the tier from your application using REST API or T-SQL.

ALTER DATABASE [db1] MODIFY (EDITION = 'Premium', MAXSIZE = 1024 GB, SERVICE_OBJECTIVE = 'P15');