First published on MSDN on Jan 22, 2019
One of the many great features of Azure SQL Database is the ability to scale up or down depending on the amount of workload an instance is processing, which means there is greater control of the instance that can translate into greater cost savings. But what about those moments where the workload increases and we don't notice it until our instance is suffering from resource exhaustion?
By leveraging the power of Azure run books, we can set up a simple script that auto-scales the instance and is fired by an alert configured to execute the run book when a certain metric happens; i.e. CPU usage goes above 90%.
This quick tutorial explains how to set up the script that scales the instance in question. The following steps use PostgreSQL as example, but the same method can be used for Azure Database for MySQL.
1. Create an Azure automation account. You can create one quickly by searching for automation on the Portal's search bar. 
2. We need to import several Python 2 packages in order to have the correct libraries to set up the management of MySQL/PostgreSQL resources in Azure.
- Please make sure that Python 2.x is installed on your computer and pip is updated. Azure Automation works only with Python 2.x so these are the packages we need to gather beforehand.
- If not using the newest version of pip, update it following this documentation.
- Open a new cmd window and run:
pip download -d newfiles azure-mgmt-rdbms==2.2.0
- This will download (but not install) the required SDK and its dependencies in .WHL format.
- Return to the Azure Portal and look for Python 2 packages which will be one of the blades for the automation account:
- Next, we will import every file we downloaded with pip to this account by clicking Add a Python 2 package , browsing and uploading every .WHL file. The list will look something like this:
3. Now we are ready to start the coding part:
- Navigate to the Runbooks blade and click Create a runbook
- Enter the requested details and after you create your runbook, click it and then click Edit.
- On another tab, open the Azure Portal and click the Cloud Shell button to start a new bash session, we need to create a service principal that will be allowed to manage the server.
- When it loads (CLI, not PowerShell), execute the following command to create a principal:
az ad sp create-for-rbac --name "MY-PRINCIPAL-NAME" --password "STRONG-SECRET-PASSWORD"
- It will take a while but, when it finishes, please make sure to note the Tenant ID and App ID. We will need those in the next steps.
4. Now back to Step 3, when you click Edit, a new editor interface will open and the following code will go there. Please pay attention to the important parts you need to modify:
- Tenant ID
- Service Principal's Application ID
- Service Principal's password
- Subscription ID
- Resource Group
- Name of the PostgreSQL instance to scale
- SKU: the SKU is the most important part to define, as this will tell the instance to which tier scale up or down. Note that in the example below I used B_Gen5_2 , which means a Basic tier, Generation 5 CPU with 2 cores. To learn more about how you can mix and match these parameters, please refer to this documentation . You can use only the name (as in the example), or use a mix of name, tier, capacity, size or family depending on your needs.
[sourcecode language="python" collapse="false"]
"""
Azure Automation documentation : https://aka.ms/azure-automation-python-documentation
Azure Python SDK documentation : https://aka.ms/azure-python-sdk
"""
import os
import sys
import adal
from azure.common.credentials import ServicePrincipalCredentials
from azure.mgmt.rdbms.postgresql import PostgreSQLManagementClient
from azure.mgmt.rdbms.postgresql.models import ServerUpdateParameters, Sku
import azure.mgmt.resource
import automationassets
import msrest
# Tenant ID for your Azure Subscription
TENANT_ID = 'xxxxxxxx-xxxxxx-xxxxx'
# Your Service Principal App ID
CLIENT = 'xxxxxx-xxxxx-xxxxxx'
# Your Service Principal Password
KEY = 'xxxxxxxxxx'
credentials = ServicePrincipalCredentials(
client_id = CLIENT,
secret = KEY,
tenant = TENANT_ID
)
#Subscription, Resource Group and name of the PostgreSQL instance we will auto-scale
SUBSCRIPTION_ID = "xxxxxxxx-xxxxxxx-xxxxxx"
RESOURCE_GROUP = "xxxxxxxxxxxx"
POSTGRES_SERVER = "xxxxxxxxxxxx"
client = PostgreSQLManagementClient(credentials, SUBSCRIPTION_ID)
server_creation_poller = client.servers.update(
resource_group_name = RESOURCE_GROUP,
server_name = POSTGRES_SERVER,
parameters = ServerUpdateParameters(
sku = Sku(
name = "B_Gen5_2"
)
)
)
srv = server_creation_poller.result()
[/sourcecode]
5. When the required changes to the code are done, we are ready to test!
6. Click on Test Pane and then Run.
7. The run book will start to execute and it will show complete when done. By this time the instance will start to scale up or down and you will soon see the changes reflected in the Overview blade of the PostgreSQL instance.
After confirming that the run book correctly scaled the Azure Database for PostgreSQL instance, you can then follow the next tutorial to fire this run book from an Azure alert. This way if the instance suddenly encounters an increase in workload, the run book will automatically run and scale accordingly.