I often get asked about Automating Tasks and Monitoring Tools in Azure SQL, here are some helpful resources and scripts that can be used to test these features. All the scripts are in this Automated_AzureSQLDB.zip file.

Database Watcher:

Database watcher is a managed monitoring solution for database services in the Azure SQL family. It supports Azure SQL Database and Azure SQL Managed Instance. Database watcher collects in-depth workload monitoring data to give you a detailed view of database performance, configuration, and health. This article contains detailed steps to create, configure, and start a watcher in the Azure portal. You can enable in-depth database monitoring of your Azure SQL resources in minutes. Here are The 3 most asked Database Watcher Questions.

Elastic Database Jobs:

Elastic Database Jobs which is now Generally Available. Start with the file Elastic_Database_Jobs.sql that will walk through how to create the jobs and job steps using SQL Authentication. There is also an updated script to demonstrate using Managed Identities Elastic_Jobs_Managed_Identities.sql . The second script AzureSQLMaintenance.sql is used to create a custom stored procedure for AzureSQLMaintenance from Yochanan Rachamim (SQL Ninja Engineering team) that performs indexes and statistics updates. But you could customize for any Ola Hallengren scripts. 

If you are currently using scripts in your on-premises SQL Server environment, you can Download Convert SQL Server Agent Jobs into Elastic Database Jobs from Official Microsoft Download Center to migrate them over to your Azure SQL environment.

Azure Automate for Index and Statistic maintenance:

Azure Automation is useful to automate tasks across all the Azure resources. Instead of re-inventing the wheel, here are two links from SQL Shack  that walk you through Setting up an Azure Automation Account and Automating Azure SQL Database indexes and statistics maintenance. The second link uses the same AzureSQLMaintenance.sql script as above. Also included is an AzureAutomateCredentials.sql file and the Automate_Maintenance.ps1 PowerShell script that is used in that demonstration.

Autoscaling with Runbooks and Alerts:

This Word document comes from Julio Calderón when he was on the SQL Ninja Engineering team. He recently updated this to use Managed Identities. This whitepaper walks through using Automation runbooks and Azure Monitor Alerts to auto-scale the database as soon as it goes over 85% CPU usage for a sustained period of 5 minutes. The AutoScale_AzureDB.ps1 is used for the runbook code and can be found in the Automated_AzureSQLDB.zip file.

Using Azure Metrics, Alerts, and Automation Accounts to start an Extended Event session:

This GitHub repo on building xEvents Sessions in Azure SQL was created by Jes Chapman who is a Senior Cloud Solution Architect at Microsoft. With On-Premises SQL Server, you can start a SQL Server Agent Job when a Windows PerfMon counter is at/over/under a certain value (“Performance Condition Alert”).

Azure SQL Managed Instance (SQL MI) and Azure SQL Database (SQL DB) DOES NOT have that capability. However, you can use Azure Monitor Alerts, an Azure Automation Runbook, and PowerShell to accomplish the same thing.

If you want to continue your learning journey, here are some resources that can help you.

• Microsoft Learn: Azure Data [docs.microsoft.com]: (Includes a sandbox to practice the Microsoft Learn Modules).
• Try the Azure SQL Workshop [github.com]:  (A series of hands-on labs that work with the Azure SQL for Beginners [docs.microsoft.com] videos).
• Azure SQL Database free offer [youtube.com]: (Step-by-step instructions on setting up a free Azure SQL Database environment).