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.
3. Now we are ready to start the coding part:
az ad sp create-for-rbac --name "MY-PRINCIPAL-NAME" --password "STRONG-SECRET-PASSWORD"
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:
[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
from azure.common.credentials import ServicePrincipalCredentials
from azure.mgmt.rdbms.postgresql import PostgreSQLManagementClient
from azure.mgmt.rdbms.postgresql.models import ServerUpdateParameters, Sku
# 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()
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.