Autoscaling Azure SQL Database

Question

We have an application that uses Azure SQL for the database backend. Under normal load/conditions this database can successfully run on a Premium 1 plan. However, during the early morning hours we have jobs that run that increase database load. During these few hours we need to move to a Premium 3 plan. The cost of a Premium 3 is about 8 times more, so obviously we do not want to pay the costs of running on this plan 24/7.

Is it possible to autoscale the database up and down? Cloud services offer an easy way to scale the number of instances in the Azure Portal, however, nothing like this exists for Azure SQL databases. Can this be done programmatically with the Azure SDK? I have been unable to locate any documentation on this subject.

kspearrin · Accepted Answer · 2014-11-06T18:42:25.680

19

After digging through the articles in @ErikEJ's answer (Thanks!) I was able to find the following, which appears to be newly published with the release of the Elastic Scale preview:

Changing Database Service Tiers and Performance Levels

The following REST APIs are now newly available as well, which let you do pretty much whatever you want to your databases:

REST API Operations for Azure SQL Databases

And for my original question of scaling service tiers (ex. P1 -> P3 -> P1):

Update Database REST API

With these new developments I am going to assume it's only a matter of time before autoscaling is also available as a simple configuration in the Azure Portal, much like cloud services.

edited Nov 06 '14 at 18:42

answered Nov 06 '14 at 18:23

kspearrin

10,238
9
53
82

Too bad the service objectives aren't the simple "P1" style identifiers that humans know and understand. ;) – David Peden Nov 06 '14 at 18:42
Yea, I don't really get that, but from my testing you must provide the Name, Edition, and ServiceObjectiveId (GUID) if you are scaling tiers. – kspearrin Nov 06 '14 at 18:44
4

@DavidPeden, do a `select * from slo_service_objectives` - that gives you the mapping between objective id and names like "P1" or "S2". – Johan Levin Jan 30 '15 at 10:53
1

you can use t-sql version of alter database to do this. However note that auto scaling is not in place and it takes a few minutes to couple hours (size of the database) to scale up / scale down. Alter database Modify (edition = 'Premium', service_objective = 'P1') should does the trick too – Sirisha Chamarthi Jun 10 '15 at 01:52
Just a FYI for everyone, here are all the current known service object ids: http://pastebin.com/SnNRFxrs – kspearrin Sep 03 '15 at 18:46
@SirishaChamarthi: Error: Can not alter the database, User must be in the master database. Where and how can u run that command? – Chinh Phan Oct 01 '15 at 07:08
You should connect to the master and execute this (in case of V12, this works from connecting the user db too). – Sirisha Chamarthi Oct 04 '15 at 06:33
It's 4 years after this answer was given and Autoscaling is still not an option. Microsoft is really dropping the ball - I have not been impressed with their Azure products so far :-( – Abe Miessler Oct 29 '18 at 03:12

score 13 · Answer 2 · edited Dec 05 '16 at 17:08

Another way to do it is using Azure automation and using run book below:

param
(
    # Desired Azure SQL Database edition {Basic, Standard, Premium}
    [parameter(Mandatory=$true)] 
    [string] $Edition,

    # Desired performance level {Basic, S0, S1, S2, P1, P2, P3}
    [parameter(Mandatory=$true)] 
    [string] $PerfLevel

)

inlinescript
{
    # I only care about 1 DB so, I put it into variable asset and access from here
    $SqlServerName = Get-AutomationVariable -Name 'SqlServerName'
    $DatabaseName = Get-AutomationVariable -Name 'DatabaseName'


    Write-Output "Begin vertical scaling script..."

    # Establish credentials for Azure SQL Database server 
    $Servercredential = new-object System.Management.Automation.PSCredential("yourDBadmin", ("YourPassword" | ConvertTo-SecureString -asPlainText -Force)) 

    # Create connection context for Azure SQL Database server
    $CTX = New-AzureSqlDatabaseServerContext -ManageUrl “https://$SqlServerName.database.windows.net” -Credential $ServerCredential

    # Get Azure SQL Database context
    $Db = Get-AzureSqlDatabase $CTX –DatabaseName $DatabaseName

    # Specify the specific performance level for the target $DatabaseName
    $ServiceObjective = Get-AzureSqlDatabaseServiceObjective $CTX -ServiceObjectiveName "$Using:PerfLevel"

    # Set the new edition/performance level
    Set-AzureSqlDatabase $CTX –Database $Db –ServiceObjective $ServiceObjective –Edition $Using:Edition -Force

    # Output final status message
    Write-Output "Scaled the performance level of $DatabaseName to $Using:Edition - $Using:PerfLevel"
    Write-Output "Completed vertical scale"
}

Ref:
Azure Vertically Scale Runbook
Setting schedule when u want to scale up/down.
For me, I used 2 schedules with input parameters, 1 for scaling up and another one for scaling down.
Hope that help.

Awesome! This solution involves some more reading (learning how to create an Automation Account, a new Asset (Credential), a new Runbook (this one) and a new Schedule. But it works like a charm. Thanks! — Augusto Barreto, Oct 12 '15 at 21:27
This is a great answer, and can be done through the *Classic Azure Portal*. (1) Create an automation account (2) Assets > Add > Credential > PS credential > {Your DB Admin creds} (3) Import the above runbook (follow link and download first) (4) To test: Author > Draft > Start, supply credential name from above and other params. (5) If all good, publish runbook and create schedule (pretty self explanatory). — Dunc, Mar 30 '16 at 09:40

ErikEJ · Answer 3 · 2017-03-03T15:52:24.390

12

Yes, that feature has is available: Azure SQL Database Elastic Scale

https://learn.microsoft.com/en-gb/azure/sql-database/sql-database-elastic-scale-introduction

edited Mar 03 '17 at 15:52

answered Nov 06 '14 at 14:54

ErikEJ

40,951
5
75
115

8

Elastic scale doesn't seem to do autoscaling, it's for partitioning/sharding data across multiple databases. For big scenario's this is helpful, not for scaling during the day – Erik Oppedijk Nov 06 '14 at 14:57
1

@ErikO - not sure why you think that: http://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-scale-elasticity/ – ErikEJ Nov 06 '14 at 15:04
ErikO's link seems broken, try this: https://azure.microsoft.com/en-gb/documentation/articles/sql-database-elastic-scale-introduction/ – Dunc Mar 16 '16 at 16:56

score 10 · Answer 4 · answered Jul 06 '16 at 10:25

10

In some cases the easiest option might be to just run SQL query as described in msdn.

For example:

ALTER DATABASE [database_name] MODIFY (EDITION = 'standard', SERVICE_OBJECTIVE = 'S3', MAXSIZE = 250 GB)

answered Jul 06 '16 at 10:25

Lanorkin

7,310
2
42
60

Autoscaling Azure SQL Database

4 Answers4

Linked