Azure SQL DB Auto-Tuning with Terraform AzAPI Provider

Following our detailed exploration into monitoring Azure SQL databases using Terraform, which we embarked on in our initial post and further delved into with advanced monitoring techniques, we now turn our attention to the critical aspect of performance optimization.

Auto-tuning in Azure SQL Database is a pivotal feature that automatically manages and applies performance enhancements to our database. Although the current Terraform AzureRM provider does not directly support Azure SQL database auto-tuning configurations, we bridge this gap with the Terraform AzAPI provider. The AzAPI provider acts as a versatile tool that interfaces directly with the Azure ARM REST APIs, covering the terrain beyond AzureRM's capabilities, such as managing private or public preview services and features that may not be part of the AzureRM provider

Why AzAPI?

The use of the AzAPI provider in Terraform scripts signifies a step forward in the realm of infrastructure as code (IaC), allowing us to manage advanced configurations that are not supported in the mainline AzureRM provider. By leveraging this provider, we can automate complex tasks, reduce the potential for human error, and ensure our infrastructure adapts dynamically to performance data.

Adding Azure SQL Auto-tuning settings using Terraform AzAPI provider

In the continuation of enhancing our Azure SQL Database infrastructure using Terraform, as detailed in our previous articles, we are now appending three crucial resource blocks to our existing configuration. These new blocks make use of the azapi_update_resource from the AzAPI provider to enable Auto-Tuning features on a per-database level. Specifically, we're focusing on three aspects of Auto-Tuning: creating indexes, forcing the last known good plan, and dropping indexes. By integrating these updates into our Terraform scripts, we're not only following infrastructure as code practices but also ensuring that each SQL database has its individual Auto-Tuning configurations, rather than inheriting settings from the server level.

Auto-Tuning with AzAPI Provider

With the Terraform AzAPI provider, we can fine-tune our databases to ensure optimized performance without manual intervention. Here's a comprehensive look at the code snippets we are incorporating and their purposes:

Provider Configuration

terraform {
  required_providers {
    azapi = {
      source  = "Azure/azapi"
    }
  }
}

This snippet defines our required Terraform version and the AzAPI provider version, ensuring we have the necessary tools to interact with Azure's API.

Enabling Create Index Advisor

resource "azapi_update_resource" "mssql_database_autotuning_create_index" {
  for_each    = { for db in var.databases : db => db }
  type        = "Microsoft.Sql/servers/databases/advisors@2014-04-01"
  resource_id = "${azurerm_mssql_database.example[each.key].id}/advisors/CreateIndex"

  body = jsonencode({
    properties : {
      autoExecuteValue : var.create_index_auto_execute_value
    }
  })
}

This resource automates the enabling of the Create Index Advisor, an integral part of SQL Database's performance tuning.

Force Last Good Plan Advisor

resource "azapi_update_resource" "mssql_database_autotuning_force_last_good_plan" {
  for_each    = { for db in var.databases : db => db }
  type        = "Microsoft.Sql/servers/databases/advisors@2014-04-01"
  resource_id = "${azurerm_mssql_database.example[each.key].id}/advisors/ForceLastGoodPlan"

  body = jsonencode({
    properties : {
      autoExecuteValue : var.force_last_good_plan_auto_execute_value
    }
  })
}

This Terraform resource enables the Force Last Good Plan Advisor, ensuring that the database reverts to the last known good execution plan if a query's performance regresses.

Drop Index Advisor

resource "azapi_update_resource" "mssql_database_autotuning_drop_index" {
  for_each    = { for db in var.databases : db => db }
  type        = "Microsoft.Sql/servers/databases/advisors@2014-04-01"
  resource_id = "${azurerm_mssql_database.example[each.key].id}/advisors/DropIndex"

  body = jsonencode({
    properties : {
      autoExecuteValue : var.drop_index_auto_execute_value
    }
  })
}

Here, the DropIndex Advisor is set up to automatically remove indexes that are no longer beneficial, streamlining the database's performance.

Configuration Variables

In Terraform, variables are a way to define parameters that can change between different deployments without altering the main configuration code. They make our configurations more dynamic and reusable. In our Azure SQL Database Auto-Tuning configuration, we've defined three variables, each corresponding to a specific Auto-Tuning feature. By default, these variables are set to "Disabled". This is an intentional choice that provides us with the flexibility to enable these settings explicitly rather than inheriting them by default from the Azure SQL server-level configurations.

Here's why this approach is significant:

1. Control and Safety: Setting the default to "Disabled" gives us explicit control over whether we want these features enabled on a per-database basis. It prevents automatic application of these sometimes impactful operations, ensuring that they are only turned on after careful consideration and according to specific needs.

2. Inheritance from Parent Resource: If these settings are not explicitly enabled at the database level, the database inherits Auto-Tuning settings from the Azure SQL Server parent resource. In some cases, this may not be desirable as it could apply broad performance optimization strategies that aren't tailored to the individual database's workload patterns.

3. Customization: Each database might have different performance characteristics and requirements. By disabling these features by default, we retain the ability to customize Auto-Tuning on a case-by-case basis.

The variables we've introduced are as follows:

• create_index_auto_execute_value: This controls whether the advisor will automatically create indexes that it determines could improve performance.

• force_last_good_plan_auto_execute_value: This determines whether the advisor should automatically force the last known good execution plan if a query suddenly starts performing poorly.

• drop_index_auto_execute_value: This setting controls whether the advisor will automatically drop indexes that it deems unnecessary or harmful to database performance.

variable "create_index_auto_execute_value" {
  description = "The auto execute value for the CreateIndex advisor. Possible values: 'Enabled', 'Disabled'."
  type        = string
  default     = "Disabled" } variable "force_last_good_plan_auto_execute_value" { description = "The auto execute value for the ForceLastGoodPlan advisor. Possible values: 'Enabled', 'Disabled'." type = string default = "Disabled" } variable "drop_index_auto_execute_value" { description = "The auto execute value for the DropIndex advisor. Possible values: 'Enabled', 'Disabled'." type = string default = "Disabled" } 

In practice, by having these variables, an Azure SQL Database administrator can run Terraform with different variable values for different databases. This provides a highly customized and optimized environment where databases do not simply use a "one size fits all" Auto-Tuning setting but rather have configurations that are aligned with their unique workloads and performance profiles.

Terraform Plan and Apply

When we run terraform plan, Terraform will perform a refresh to determine if the state of the resources it manages has changed compared to the last time it checked. It will output a plan showing what Terraform will do to match the desired state defined in our configurations. This plan includes the creation of new resources for Auto-Tuning settings for each of our Azure SQL Databases. Running terraform apply will then prompt us to confirm whether we want to proceed with these changes. Once confirmed, Terraform will make calls to Azure APIs via the AzAPI provider to apply these Auto-Tuning settings at the database level. As a result, each database will have its specific Auto-Tuning settings applied, enabling it to autonomously optimize its performance by creating or dropping indexes and forcing the last good plan when necessary, independent of server-level settings. This granular control ensures tailored performance optimization that can adapt to the unique workload patterns and requirements of each database.

Enabling Autotuning

When we adjust the variable values from "Disabled" to "Enabled" for any of the Auto-Tuning features and apply these changes using Terraform, the corresponding effects will be observable in the Azure Portal. Specifically, for our database 'db1' under the server 'sql-myazuresqlserver-01', the Azure Portal will reflect these changes in the "Automatic tuning" settings of the database.

Upon navigating to the 'Automatic tuning' view for 'db1', we would see the status of each feature—'Create Index', 'Force Last Good Plan', and 'Drop Index'—changed to "On". Alongside this status, there will be an indication "Forced by user", signifying that these settings were explicitly set by an administrator rather than inherited from the server defaults or Azure's general recommendations.

This visual cue in the Azure Portal serves as a clear confirmation that the Auto-Tuning settings have been successfully applied and are now actively being enforced. It ensures that the database performance optimizations are tailored as per the Terraform configuration, providing an extra layer of assurance to database administrators that the database is configured according to the prescribed specifications.

Conclusion

In conclusion, adding Azure SQL DB auto-tuning capabilities through the AzAPI provider complements our existing infrastructure configuration and enhances the performance and efficiency of our Azure SQL databases. This addition illustrates the power of Terraform in automating not just deployment but also ongoing optimization of our cloud resources.