Is it possible to make azure elastic pool scale automatically?

Question

As titled, I am trying to automatically scale my Azure databases. I have found a powershell workflow script for a single database, which works perfectly. I was wondering if it is possible to automatically scale my elastic pool. I have tried to rewrite the script, but failed many times.

Here is the script below:

workflow Set-AzureSqlDatabaseEdition 
{ 
    param 
    ( 
        # Name of the Azure SQL Database server (Ex: bzb98er9bp) 
        [parameter(Mandatory=$true)]  
        [string] $SqlServerName, 

        # Target Azure SQL Elastic Pool
        [parameter(Mandatory=$true)]  
        [string] $ElasticPoolName, 

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

        # Desired DTU 
        [parameter(Mandatory=$true)]  
        [string] $DTU, 

        # DatabaseDtuMin  
        [parameter(Mandatory=$true)]
        [string] $DatabaseDtuMin,

        # DatabaseDtuMax
        [parameter(Mandatory=$true)]
        [string] $DatabaseDtuMax,

        # Credentials for $SqlServerName stored as an Azure Automation credential asset 
        # When using in the Azure Automation UI, please enter the name of the credential asset for the "Credential" parameter 
        [parameter(Mandatory=$true)]  
        [PSCredential] $Credential 
    ) 

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

        # Establish credentials for Azure SQL Database server  
        $Servercredential = new-object System.Management.Automation.PSCredential($Using:Credential.UserName, (($Using:Credential).GetNetworkCredential().Password | ConvertTo-SecureString -asPlainText -Force))  

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

        # Get Azure Elastic Pool context 
        $EP = Get-AzureRmSqlElasticPool $CTX ElasticPoolName $Using:ElasticPoolName 

        # Specify the specific performance level for the target $DatabaseName 
        $DTU = Get-AzureRmSqlElasticPool $CTX ElasticPoolName $Using:DTU 

        # Set the new edition/performance level 
        Set-AzureRmSqlElasticPool $CTX ElasticPoolName $DTU –ServiceObjective $DTU –Edition $Using:Edition -Force 

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

I hope someone can point me a right direction to do that if it is possible.

Many Thanks!!!

Please can you include the script in your question? – Dragonthoughts Jun 12 '18 at 08:58 — Dragonthoughts, Jun 12 '18 at 08:58
yes, I have added the script:) – Lancer Xue Jun 12 '18 at 09:23 — Lancer Xue, Jun 12 '18 at 09:23

score 2 · Answer 1 · edited Feb 24 '21 at 09:23

see https://learn.microsoft.com/en-us/azure/sql-database/scripts/sql-database-monitor-and-scale-pool-powershell

This PowerShell script example monitors the performance metrics of an elastic pool, scales it to a higher performance level, and creates an alert rule on one of the performance metrics.

PS C:\>Set-AzSqlElasticPool 
    -ResourceGroupName "ResourceGroup01" 
    -ServerName "Server01" 
    -ElasticPoolName "ElasticPool01" 
    -Dtu 1000 
    -DatabaseDtuMax 100 
    -DatabaseDtuMin 20

ResourceId        : /subscriptions/00000000-0000-0000-0000-000000000001/resourceGroups/resourcegroup01/providers/Microsoft.Sql/servers/Server01/elasticPools/ElasticPool01
ResourceGroupName : ResourceGroup01
ServerName        : Server01
ElasticPoolName   : ElasticPool01
Location          : Central US
CreationDate      : 8/26/2015 10:00:17 PM
State             : Ready
Edition           : Standard
Dtu               : 200
DatabaseDtuMax    : 100
DatabaseDtuMin    : 20
StorageMB         : 204800
Tags              :

score 0 · Answer 2 · answered Jun 12 '18 at 15:38

If you are open to 3rd party products, look into CloudMonix - it has a feature where it can autoscale Elastic Pools, SQL Warehouses, and SQL Azure databases by whatever metrics and/or date-time.

You can define Scale Ranges and Scale Adjustments in it. Scale Ranges are usually time-based criteria between which the scaling happens (ie: for 9am thru 5pm the minimum tier is P3 and maximum is P5, etc) and Scale Adjustments are incremental moves in tiers based on some sort of performance indicator (DTU > 80 or Requests/sec on the website > 50, etc)

No PowerShell required

Is it possible to make azure elastic pool scale automatically?

2 Answers2

Linked