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.

Answer 1

Use Azure automation and just run the 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"

}