Search code examples
azurepowershellazure-elasticpool

How to scale Azure Elastic Pools

I'd like to automatically scale our elastic pools so that between 8am-6pm we give them lots of resource, and between 6pm-8am we give them very little resource. I can use powershell to scale the elastic pool up and down...

Set-AzSqlElasticPool 
-ResourceGroupName "ResourceGroupName01"
-ServerName "ServerName01"
-ElasticPoolName "ElasticPoolName01"
-Dtu 100
-DatabaseDtuMax 50 
-DatabaseDtuMin 20

But I'm unsure how I can automate the running of that powershell script within Azure to automate the scaling of my elastic pool.

Solution

  • Create a new Function App in the Azure Portal. To complete this work we will need to add 2 modules to our function app. We need the latest version Az.Accounts and Az.Sql. You can check the latest versions here: https://www.powershellgallery.com/packages/Az.Accounts https://www.powershellgallery.com/packages/Az.Sql Download the nuget package on the Manual download tab on each of the above pages. Download Package

    Back to the function app in Azure and select “Advanced Tools” and click Go, from the top menu choose Debug Console > Powershell Kudu Menu

    Browse to \site\wwwroot and edit the file: requirements.psd1. Add references to the 2 modules we are intending to add (don’t forget to update the version number if required), then save. Requirements.psd1 file

    In the wwwroot directory create a new folder called Modules, within the Modules folder create 2 additional folders “Az.Sql” and “Az.Accounts”. In each of the folders drag and drop the corresponding nuget package you downloaded earlier. You’ll need to wait a minute for Kudu to unpack them. Modules Folder

    Now click on the home button and run the following 2 powershell commands Import-Module C:\home\site\wwwroot\Modules\Az.Accounts\Az.Accounts.psd1 Import-Module C:\home\site\wwwroot\Modules\Az.Sql\Az.Sql.psd1 A great explanation of this is here: https://tech.nicolonsky.ch/azure-functions-powershell-modules/ The modules are now installed so go back to the function app and select Identity in the menu and turn on System Assigned. Now browse to your SQL Server (that is hosting the elastic pool) > Access Control > Add > Add Role Assignment. Choose the appropriate role (Owner will work but is not suitable for production environments). In members choose “Assign Access to” “Managed Identity”, select members and choose your function app, then assign the role.

    Back to the Function App, you can now create a function to scale your elastic pool. For timer functions this website is very helpful for generating a CRON expression https://ncrontab.swimburger.net/ In the code and test tab of your function add the following line to scale the pool to your requirements: Set-AzSqlElasticPool -ResourceGroupName "ResourceGroupName01" -ServerName "ServerName01" -ElasticPoolName "ElasticPoolName01" -Dtu 100 -DatabaseDtuMax 50 -DatabaseDtuMin 20