There are a few ways to scale a SQL elastic pool. For this blog post I show you how to scale up. It can be done via the Azure portal and Azure PowerShell but not T-SQL.
I would say the PowerShell route is the easiest. Connect to your account and issue the below code. Here I am going from a 100 edtu pool to a massive 2000 edtu pool whilst tweaking the min/max setting.
Connect-AzAccount
Below shows the current spec of my elastic pool.
Get-AzSqlElasticPool -ResourceGroupName "AKS_PROD" -ServerName "smarties"
Use Set-AzSqlElasticPool to make the changes to whatever you desire.
Set-AzSqlElasticPool -ResourceGroupName "AKS_PROD" -ServerName "smarties" -ElasticPoolName "jumbo" -Dtu 2000 -DatabaseDtuMax 100 -DatabaseDtuMin 20
While the update is happening connect to the server via SSMS and you can check the progress.
SELECT * FROM sys.dm_operation_status WHERE state_desc = 'IN_PROGRESS'
Get-AzSqlElasticPool -ResourceGroupName "AKS_PROD" -ServerName "smarties"
Then a final confirmation of the update.
Can’t we do autoscaling of elastic pool service tier in a scheduled manner ?
Thanks,
LikeLike
Not out the box. you need to roll your own. Logic apps, Azure function etc.
LikeLike
This means we do not have anything yet to auto scale the elastic pool service tiers, I need to write the powershell script ?
LikeLike
When I say out the box I mean there is nothing behind the scenes that will dynamically grow or shrink the pool based on usage. So if you know you need to increase eDTUs run PowerShell, Azure CLI whatever you like. thanks
LikeLike
Hi, thanks for the immediate reply.
When I say auto scaling of service tier, it means changing levels like (P0,P1,S0,S1 etc) and not scaling the EDTU’s. Am able to scale the edtu’s but not able to change the service tier automatically.
Thanks,
LikeLike
I personally haven’t tried that yet. looking at https://docs.microsoft.com/en-us/powershell/module/az.sql/set-azsqlelasticpool?view=azps-4.2.0
-edition switch has the different service tiers.
thanks.
LikeLike