Azure Runbook Configuration

Hello everybody! Greetings from the very warm state of Texas!  One of the most popular questions I get when talking about Azure SQL Database is how to configure Index and Statistics maintenance without a SQL Agent to execute jobs.  I normally recommend using Azure Automation and the creation of a runbook to do so.

 

I had a lot of issues when I created my first one, and after discussing with some folks, they had the same issues.  I searched for the best blog posts that I could find on the subject, and the one I LOVED the most was here: Arctic DBA.  He broke it down so simply, that I finally created my own pseudo installer and I wanted to share it with all of you.  Please, bear in mind, these code snippets may fail at anytime due to changes in Azure.

 

**IMPORTANT**

These next steps assume the following:

You have created/configured your Azure Automation Account and credential to use to execute this runbook.

 

 

Here is the Azure Runbook PowerShell workflow configuration:

 

Next is the T-SQL to execute against each of your Azure SQL Databases, now mind you, with this script it WILL create a new schema called “DBA” but this will ONLY be for the maintenance objects.  If you prefer not to have the new schema, delete the Schema creation section, and you will have to edit the rest of the script and remove all calls to the “[DBA].” schema, along with editing the Runbook above and removing all calls to the “[DBA].” schema.

 

T-SQL Install of OLA Hallengren Index/Statistics Maintenance w/ Schema Creation: