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.
The post Setup Ola Index Maintenance with Azure Runbooks and Terraform appeared first on sqlkitty.