0

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.

RyanBreakspear
  • 29
  • 7

2 Answers2

0

Is it possible to make azure elastic pool scale automatically?

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

I'm not sure what type of application you have but you could use an Azure Function or WebJob to scale down and up at certain times daily. That would be more reliable than pure PowerShell.

jasonc
  • 213
  • 1
  • 7
  • Thanks for replying Jason, I had looked at that question but the answer is pretty much the same script as I already have. If I try the script that I have within a function app I get the following error: "ERROR: The term 'Set-AzSqlElasticPool' is not recognized as the name of a cmdlet, function, script file, or operable program." – RyanBreakspear Jan 25 '22 at 19:17
  • @RyanBreakspear generally when you are getting that error it means you are missing the PowerShell module. In your case you'll need the Az.Sql module which should be included in your Az module. A lot of computers also are defaulted to the older Azure RM and not Azure Az modules. So check it out with: Get-InstalledModule then look for Az.Sql – jasonc Jan 25 '22 at 19:28
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 26 '22 at 00:13
0

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

RyanBreakspear
  • 29
  • 7