This article is contributed. See the original author and article here.
Flexible Server is a new deployment option for Azure Database for PostgreSQL that gives you the control you need with multiple configuration parameters for fine-grained database tuning along with a simpler developer experience to accelerate end-to-end deployment. With Flexible Server, you will also have a new way to optimize cost with stop/start capabilities. The ability to stop/start the Flexible Server when needed is ideal for development or test scenarios where it’s not necessary to run your database 24×7. When Flexible Server is stopped, you only pay for storage, and you can easily start it back up with just a click in the Azure portal.
Azure Automation delivers a cloud-based automation and configuration service that supports consistent management across your Azure and non-Azure environments. It comprises process automation, configuration management, update management, shared capabilities, and heterogeneous features. Automation gives you complete control during deployment, operations, and decommissioning of workloads and resources. The Azure Automation Process Automation feature supports several types of runbooks such as Graphical, PowerShell, Python. Other options for automation include PowerShell runbook, Azure Functions timer trigger, Azure Logic Apps. Here is a guide to choose the right integration and automation services in Azure.
Runbooks support storing, editing, and testing the scripts in the portal directly. Python is a general-purpose, versatile, and popular programming language. In this blog, we will see how we can leverage Azure Automation Python runbook to auto start/stop a Flexible Server on weekend days (Saturdays and Sundays).
Prerequisites
- Azure subscription. If you don’t have one yet, you can activate your MSDN subscriber benefits or sign up for a free account.
- Azure Database for PostgreSQL – Flexible Server. If you don’t have one yet, you can create one here.
Steps
1. Create a new Azure Automation account with Azure Run As account at:
https://ms.portal.azure.com/#create/Microsoft.AutomationAccount
NOTE: An Azure Run As Account by default has the Contributor role to your entire subscription. You can limit Run As account permissions if required. Also, all users with access to the Automation Account can also use this Azure Run As Account.
2. After you successfully create the Azure Automation account, navigate to Runbooks.
Here you can already see some sample runbooks.
3. Let’s create a new python runbook by selecting+ Create a runbook.
4. Provide the runbook details, and then select Create.
After the python runbook is created successfully, an Edit screen appears, similar to the image below.
5. Copy paste the below python script. Fill in appropriate values for your Flexible Server’s subscription_id, resource_group, and server_name, and then select Save.
import azure.mgmt.resource
import requests
import automationassets
from msrestazure.azure_cloud import AZURE_PUBLIC_CLOUD
from datetime import datetime
def get_token(runas_connection, resource_url, authority_url):
""" Returns credentials to authenticate against Azure resoruce manager """
from OpenSSL import crypto
from msrestazure import azure_active_directory
import adal
# Get the Azure Automation RunAs service principal certificate
cert = automationassets.get_automation_certificate("AzureRunAsCertificate")
pks12_cert = crypto.load_pkcs12(cert)
pem_pkey = crypto.dump_privatekey(crypto.FILETYPE_PEM, pks12_cert.get_privatekey())
# Get run as connection information for the Azure Automation service principal
application_id = runas_connection["ApplicationId"]
thumbprint = runas_connection["CertificateThumbprint"]
tenant_id = runas_connection["TenantId"]
# Authenticate with service principal certificate
authority_full_url = (authority_url + '/' + tenant_id)
context = adal.AuthenticationContext(authority_full_url)
return context.acquire_token_with_client_certificate(
resource_url,
application_id,
pem_pkey,
thumbprint)['accessToken']
action = ''
day_of_week = datetime.today().strftime('%A')
if day_of_week == 'Saturday':
action = 'stop'
elif day_of_week == 'Monday':
action = 'start'
subscription_id = '<SUBSCRIPTION_ID>'
resource_group = '<RESOURCE_GROUP>'
server_name = '<SERVER_NAME>'
if action:
print 'Today is ' + day_of_week + '. Executing ' + action + ' server'
runas_connection = automationassets.get_automation_connection("AzureRunAsConnection")
resource_url = AZURE_PUBLIC_CLOUD.endpoints.active_directory_resource_id
authority_url = AZURE_PUBLIC_CLOUD.endpoints.active_directory
resourceManager_url = AZURE_PUBLIC_CLOUD.endpoints.resource_manager
auth_token=get_token(runas_connection, resource_url, authority_url)
url = 'https://management.azure.com/subscriptions/' + subscription_id + '/resourceGroups/' + resource_group + '/providers/Microsoft.DBforPostgreSQL/flexibleServers/' + server_name + '/' + action + '?api-version=2020-02-14-preview'
response = requests.post(url, json={}, headers={'Authorization': 'Bearer ' + auth_token})
print(response.json())
else:
print 'Today is ' + day_of_week + '. No action taken'
After you save this, you can test the python script using “Test Pane”. When the script works fine, then select Publish.
Next, we need to schedule this runbook to run every day using Schedules.
6. On the runbook Overview blade, select Link to schedule.
7. Select Link a schedule to your runbook.
8. Select Create a new schedule.
9. Create a schedule to run every day at 12:00 AM using the following parameters
10. Select Create and verify that the schedule has been successfully created and verify that the Status is “On“.
After following these steps, Azure Automation will run the Python runbook every day at 12:00 AM. The python script will stop the Flexible Server if it’s a Saturday and start the server if it’s a Monday. This is all based on the UTC time zone, but you can easily modify it to fit the time zone of your choice. You can also use the holidays Python package to auto start/stop Flexible Server during the holidays.
If you want to dive deeper, the new Flexible Server documentation is a great place to find out more. You can also visit our website to learn more about our Azure Database for PostgreSQL managed service. We’re always eager to hear your feedback, so please reach out via email using the Ask Azure DB for PostgreSQL alias.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments