Setup Ola Index Maintenance with Azure Runbooks and Terraform

Yes, you still need to do some work to maintain indexes in Azure SQL Database. This post will walk you through setting up statistic updates and index maintenance using Terraform.

Thanks to Tracy Boggiano for her directions for setting up the runbooks. If you want to do this manually instead of with Terraform, Tracy’s post walks you through it step by step. I only modified the role assignment so it had read to the entire subscription level to loop through every DB in the subscription.

Thanks to Kendra for blogging about index maintenance in Azure SQL. Her post helped me decide on index maintenance thresholds.

TL;DR, you can get all my code out in Github.

Getting Started

If you aren’t comfortable with Terraform, I have some blog posts to help you get started.

• This post gives you the basics on how to use Terraform.
• This post shows you how to set up the Ola scripts in your SQL db with Flyway.

Creating an Automation Account

You must set up the automation account to hold your runbooks. You will also need to configure some items in the automation account.

To create the automation account, you will use this code:

resource "azurerm_automation_account" "example" {
  name                = "autoacct-${azurerm_resource_group.rg.name}"
  location            = azurerm_resource_group.rg.location
  resource_group_name = azurerm_resource_group.rg.name
  sku_name            = "Basic"
  identity {
    type = "SystemAssigned"
  }
}

The code to create the automation account will also set up a SystemAssigned identity. This will facilitate the automation account having access to the databases. The screenshot below shows what the above Terraform code will do in the portal.

The following code block will allow the automation account to read all the other resources in the subscription, including the SQL DBs.

data "azurerm_subscription" "primary" {}

resource "azurerm_role_assignment" "example" {
  scope                = data.azurerm_subscription.primary.id
  role_definition_name = "Reader"
  principal_id         = azurerm_automation_account.example.identity[0].principal_id
  depends_on = [azurerm_automation_account.example]
}

The following screenshot shows what the above Terraform code will do in the portal.

You will also need to add a SqlServer module so the automation account can execute SQL scripts using the following code:

resource "azurerm_automation_module" "example" {
  name                    = "SqlServer"
  resource_group_name     = azurerm_resource_group.rg.name
  automation_account_name = azurerm_automation_account.example.name

  module_link 
  {        
   uri = "https://www.powershellgallery.com/api/v2/package/SqlServer/22.1.1"
  }

  depends_on = [azurerm_automation_account.example]
}

The previous Terraform will add the module to the automation account as shown in the following screenshot.

Granting Automation Account Perms to Azure SQL DB

You must grant your automation account perms to your Azure SQL DB to execute the Ola scripts. If you need help setting up Ola scripts, see my blog post on that. You need to swap out whatever you named your automation account in the script below. You must execute this on every Azure SQL DB you need the automation account to run stats updates or index maintenance on.

CREATE USER [autoacct-rg-sterling-rabbit] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [autoacct-rg-sterling-rabbit]; 

Creating an Automation Account Schedule

Next, you need to create a schedule to link to a runbook.

resource "azurerm_automation_schedule" "examplestatsschedule" {
  name             = "statsschedule-${azurerm_resource_group.rg.name}"
  resource_group_name     = azurerm_resource_group.rg.name
  automation_account_name = azurerm_automation_account.example.name
  frequency               = "Week"
  interval                = 1
  timezone                = "America/New_York"
  start_time              = local.start_time
  description             = "Schedule to run the Runbook every week"
  week_days               = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Sunday"]
}

You will notice the start time references local.start_time. This is because every time you apply the Terraform code, it will update the start time and needs to be a future start_time. I have a separate file for locals named locals.tf, and it includes this code:

locals {
  current_time           = timestamp()
  start_wallclock_time   = "3.55"
  current_wallclock_time = formatdate("h.mm", local.current_time)
  schedule_tomorrow      = (local.current_wallclock_time >=
                            local.start_wallclock_time)
  today                  = formatdate("YYYY-MM-DD", local.current_time)
  tomorrow               = formatdate("YYYY-MM-DD", timeadd(local.current_time, "24h"))
  start_time             = "${local.schedule_tomorrow ? local.tomorrow : local.today}T03:55:00Z"
}

The Terraform code to create a schedule will look like the following screenshot, but yours will only show the stats schedule since that’s all that’s been created so far. The code to create the index schedule is later in the post.

Creating a Runbook for Stats Updates

You will need to create a runbook to execute the SQL code. The following runbook will perform a stats update using Ola scripts.

resource "azurerm_automation_runbook" "exampleindexstats" {
    automation_account_name  = "autoacct-${azurerm_resource_group.rg.name}"
    content                  = <<-EOT
        $errorActionPreference = "Stop"
        Import-Module SqlServer
        
        $Query = @"
        EXECUTE [dbo].[IndexOptimize]
        @Databases = 'USER_DATABASES' ,
        @FragmentationLow = NULL ,
        @FragmentationMedium = NULL ,
        @FragmentationHigh = NULL ,
        @UpdateStatistics = 'ALL' ,
        @LogToTable = 'Y';
        "@
        
        $context = (Connect-AzAccount -Identity).Context
        
        $Tenant = Get-AzTenant
        $Subscription  = Get-AzSubscription -TenantID $Tenant.TenantId
        
        ForEach ($sub in $Subscription) {
            $AzSqlServer = Get-AzSqlServer 
        
            if($AzSqlServer) {
                Foreach ($SQLServer in $AzSqlServer) {
                    $SQLDatabase = Get-AzSqlDatabase -ServerName $SQLServer.ServerName -ResourceGroupName $SQLServer.ResourceGroupName | Where-Object { $_.DatabaseName -notin "master" }
        
                    Foreach ($Database in $SQLDatabase) {
                        $Token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
        
                        Invoke-Sqlcmd -ServerInstance $SQLServer.FullyQualifiedDomainName -AccessToken $Token -Database $Database.DatabaseName -Query $Query -ConnectionTimeout 60 -Verbose
                    }
                }
            }
        }
    EOT
    description              = "Run Ola Hallengren's IndexOptimize - Stats Only"
    location                 = azurerm_resource_group.rg.location
    log_activity_trace_level = 0
    log_progress             = false
    log_verbose              = false
    name                     = "Sub_StatsUpdate"
    resource_group_name      = azurerm_resource_group.rg.name
    runbook_type             = "PowerShell"
    tags                     = {}
    depends_on = [azurerm_automation_account.example]
}

The previous code block will create a runbook in the portal like the following screenshot, but only the stats runbook since you haven’t created the index runbook yet.

Creating a Runbook Schedule/Job

Finally, you will need a job that’s tied to the schedule for this to execute at the designated day/time:

resource "azurerm_automation_job_schedule" "examplestatsjob" {
  resource_group_name     = azurerm_resource_group.rg.name
  automation_account_name = azurerm_automation_account.example.name
  runbook_name            = azurerm_automation_runbook.exampleindexstats.name
  schedule_name           = azurerm_automation_schedule.examplestatsschedule.name
}

The previous code will create a job associated with this runbook’s schedule and look like this in the portal.

Once the job has been executed either on the schedule or manually, jobs will appear in the Jobs pane.

Creating a Runbook with a Schedule and Job for Index Maintenance

You will see above that the stats updates run on specific days of the week, except for one day reserved for index maintenance. The following code will get the Runbook, its schedule, and job in place:

esource "azurerm_automation_schedule" "exampleindexschedule" {
  name                    = "indexschedule-${azurerm_resource_group.rg.name}"
  resource_group_name     = azurerm_resource_group.rg.name
  automation_account_name = azurerm_automation_account.example.name
  frequency               = "Week"
  interval                = 1
  timezone                = "America/New_York"
  start_time              = local.start_time
  description             = "Schedule to run the Runbook every week"
  week_days               = ["Saturday"]
}

resource "azurerm_automation_runbook" "exampleindexmaint" {
    automation_account_name  = "autoacct-${azurerm_resource_group.rg.name}"
    content                  = <<-EOT
        $errorActionPreference = "Stop"
        Import-Module SqlServer
        
        $Query = @"
        EXECUTE dbo.IndexOptimize
        @Databases = 'USER_DATABASES',
        @FragmentationLow = NULL,
        @FragmentationMedium = 'INDEX_REORGANIZE, INDEX_REBUILD_ONLINE',
        @FragmentationHigh = 'INDEX_REBUILD_ONLINE, INDEX_REORGANIZE',
        @FragmentationLevel1 = 50,
        @FragmentationLevel2 = 80,
        @UpdateStatistics = 'ALL',
        @Indexes = 'ALL_INDEXES',
        @LogToTable = 'Y';
        "@
        
        $context = (Connect-AzAccount -Identity).Context
        
        $Tenant = Get-AzTenant
        $Subscription  = Get-AzSubscription -TenantID $Tenant.TenantId
        
        ForEach ($sub in $Subscription) {
            $AzSqlServer = Get-AzSqlServer 
        
            if($AzSqlServer) {
                Foreach ($SQLServer in $AzSqlServer) {
                    $SQLDatabase = Get-AzSqlDatabase -ServerName $SQLServer.ServerName -ResourceGroupName $SQLServer.ResourceGroupName | Where-Object { $_.DatabaseName -notin "master" }
        
                    Foreach ($Database in $SQLDatabase) {
                        $Token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
        
                        Invoke-Sqlcmd -ServerInstance $SQLServer.FullyQualifiedDomainName -AccessToken $Token -Database $Database.DatabaseName -Query $Query -ConnectionTimeout 60 -Verbose
                    }
                }
            }
        }
    EOT
    description              = "Run Ola Hallengren's IndexOptimize Job"
    location                 = azurerm_resource_group.rg.location
    log_activity_trace_level = 0
    log_progress             = false
    log_verbose              = false
    name                     = "Sub_IndexOptimize"
    resource_group_name      = azurerm_resource_group.rg.name
    runbook_type             = "PowerShell"
    tags                     = {}
    depends_on = [azurerm_automation_account.example]
}

resource "azurerm_automation_job_schedule" "exampleindexjob" {
  resource_group_name     = azurerm_resource_group.rg.name
  automation_account_name = azurerm_automation_account.example.name
  runbook_name            = azurerm_automation_runbook.exampleindexmaint.name
  schedule_name           = azurerm_automation_schedule.exampleindexschedule.name
}

Wrapping Up

Check out my GitHub repo if you want the code in the TF files. Setting up Azure resources with Terraform ultimately makes your life easier. It can be daunting at first, but I’ve found it makes everything so much easier to maintain and keep track of.