[Azure] I tried autoscaling the DTU of SQL Database using Azure Automation.

Hello, this is Hase from the System Development Department.

I am currently developing a web system using App Service and SQL Database provided by Azure, but when I start operation, I am thinking of implementing autoscaling to reduce costs as much as possible. Masu.

However, App Service has an autoscaling function, but SQL Database does not have such a function.

I was wondering if there was a way to do this, so I did some research and found an article that says you can autoscale SQL Database by using a feature called Azure Automation.

When I actually tested it, I was able to successfully autoscale, so I would like to introduce how to do it this time.

premise

There are multiple purchase models for SQL Database (DTU, vCore-based, and serverless), but this time we will use DTU.

What is Azure Automation?

• A feature of Azure Portal that allows you to automate tasks (called runbooks in Azure Automation)
• Can be executed periodically by specifying a time (0:00 every day, etc.)
• Runbooks can be written in PowerShell, PowerShell Workflow, Python 2, and DSC languages

Implementation image

• Create a SQL Database scaling runbook in powershell format in Azure Automation
• Add the created runbook to the schedule so that the runbook is automatically executed at a specified time

procedure

Add an Azure Automation account

① Log in to Azure Portal

② Select “Automation account” from Azure services

③ Select “+Add” to add an Automation account

④ Create by entering Automation account information

*1) Be sure to select "Yes" to create an Azure execution account ⇒
In order to operate SQL Database with a runbook, approval of the execution account is required, so
if you select "No", SQL Database operations can be executed from the runbook. become unable to

Once created, your Automation account will appear in the list.

Add the module to the added Automation account

Add the following module to operate SQL Database from a runbook (task)

⑤ Modules are configured for each Automation account, so select the account you created.

⑥ Select “Module Gallery”

Shared Resources > Module Gallery

⑦ Install the module to be registered from the module gallery screen

Register the Az.Account module here (the order of installation does not matter)

⑧ The Az.Accounts module details screen will be displayed, so click “Import”

⑨ An import confirmation screen will be displayed, so click “OK”

Az.Accounts module import begins

If the import is successful, the message "Import succeeded" will be displayed at the top right of the screen.

⑩ In the same way, import the remaining modules.

Create a runbook (task)

⑪ Select “Runbook” from the created Automation account

Process Automation > Runbooks

⑫ Select “+Create Runbook” to create a runbook

⑬ Enter runbook information and create

The created runbook will be displayed in the list.

*AzureAutomationTutorial, AzureAutomationTutorialPython2, and AzureAutomationTutorialScript are provided by default when creating an Automation account, so we will not use them this time.

Write code in a runbook

Write the process to execute autoscaling in the created runbook
* Use PowerShell as the language

■ Rough implementation flow
① Obtain Azure Automation connection information
② Use the obtained Azure Automation connection information to connect to your Azure account
③ Change SQL Database specifications
* Change SQL Database specifications with PowerShell Requires connection to Azure account to make changes

⑭ Select the created runbook

⑮ Select “Edit” to open the code editor

*Write the code here

⑯ Describe the process to obtain Azure Automation connection information

■ Code

# Write a command to get Azure Automation connection information and assign it to a variable $connection = Get-AutomationConnection -Name "{Automation account connection name}"

■ Command details

⑰ You can check the Automation account connection name in step 21 from "Connection"

Shared Resources > Connections

⑱ Connect to your Azure account using the acquired Azure Automation connection information

■ Code

# Authorize your Azure account Connect-AzAccount -Tenant $connection.TenantID ` -ApplicationId $connection.ApplicationID -CertificateThumbprint ` $connection.CertificateThumbprint

*If you want to break a line in the middle of a formula, add "` (grave accent)" at the end.

■ Command details

⑲ Change SQL Database specifications

■ Code

# Change the SQL Database plan to S0 (Standard DTU 10) Set-AzSqlDatabase -ResourceGroupName "{Resource Group Name}" ` -DatabaseName "{SQL Database Name}" -ServerName "{SQL Server Name}" ` -Edition " Standard" -RequestedServiceObjectiveName "S0"

■ Command details

completed code

Combining the above code will result in this

# Write a command to get Azure Automation connection information and assign it to a variable $connection = Get-AutomationConnection -Name "{Automation account connection name}" # Authorize Azure account Connect-AzAccount -Tenant $connection. TenantID ` -ApplicationId $connection.ApplicationID -CertificateThumbprint ` $connection.CertificateThumbprint # Change the SQL Database plan to S0 (Standard DTU 10) Set-AzSqlDatabase -ResourceGroupName "{Resource Group Name}" ` -DatabaseName "{SQL Database Name }" -ServerName "{SQL Server name}" ` -Edition "Standard" -RequestedServiceObjectiveName "S0"

Check the operation of the created runbook

⑳ Open the test window to check the operation of the created runbook

Select “Test Window” on the code editor screen

㉑ Select “Start” to run the test

㉒ Once the test is successfully completed, the stream will be displayed.

*If the test fails, an error message will be displayed in red.

You can confirm that the target SQL Database plan has been changed.

Register the created runbook to the schedule

Register the created runbook in a schedule so that it can be executed regularly.
*In order to register a runbook in a schedule, you need to publish the runbook so that it can be used in the production environment.

㉓ Click “Publish” and select “Yes” to publish the runbook.

If the runbook is successfully published, a message will be displayed at the top right of the screen.

㉔ Next, click "Link to schedule" to register to the schedule.

㉕ The schedule setting screen will be displayed, so create and register a new schedule.

Schedule > + Create a new schedule
*No need to create a schedule if you already have one

㉖ Enter the time setting information for the schedule and click "Create"

㉗ The schedule you created will be selected, so click "OK"

When the schedule is successfully registered, a message will be displayed with a blue background.

㉘ When the schedule is executed, it will be displayed in the job list.

Process Automation > Jobs

summary

With the above steps, you will be able to autoscale SQL Database.

By the way, to achieve scale-out/in, you can simply create two similar runbooks and rewrite the plan (-RequestedServiceObjectiveName) part.

However, the problem is that it can only be executed at a specified time, and it is not possible to execute autoscaling using the important load condition as a threshold...
If anyone knows how to do this, please let me know. I would be happy if you could let me know!

Also, this time I used a DTU, but I think it can be achieved in a similar way with other purchased models, so please give it a try.

lastly

I have opened the system development service site "SEKARAKU Lab" to which I belong.
Beyond is a one-stop service for everything from server design and construction to operation, so if you have any trouble with server-side development, please feel free to contact us.
SEKARAKU Lab: [https://sekarakulab.beyondjapan.com/](https://sekarakulab.beyondjapan.com/)

That's all.
Thank you very much for this time.