Using Terraform to enable automatic tuning mode in an individual Azure SQL database

💡 Introduction

Azure SQL Database automatically manages data services that constantly monitor your queries and identifies the action that you can perform to improve performance of your workload. You can review recommendations and manually apply them, or let Azure SQL Database automatically apply corrective actions - this is known as automatic tuning mode.

Automatic tuning can be enabled at the server or the database level through:

• 1️⃣ The Azure portal
• 2️⃣ REST API calls
• 3️⃣ T-SQL commands

Source official Microsoft docs

🔍 The challenge

Given the limited options to enable automatic tuning, currently it is not possible to enable it through Azure CLI nor ARM (Azure Resource Manager) template, the sample in this repository provides an alternative deployment option. It expands on the T-SQL command option (:three:) to enable automatic tuning mode in an individual Azure SQL database, using Terraform.

As of today, it also not possible to enable this feature using Terraform azurerm_mssql_database resource.

Therefore, this solution uses the null_resource Terraform resource together with the sqlcmd utility instead.

🔧 How does it work?

As desribed in the Terraform docs:

The null_resource resource implements the standard resource lifecycle but takes no further action.

The triggers argument allows specifying an arbitrary set of values that, when changed, will cause the resource to be replaced.

The key attributes are the depends_on = [azurerm_mssql_database.test] and the triggers = { always_run = timestamp() }.

resource "null_resource" "db_setup" {
  depends_on = [azurerm_mssql_database.test]

  triggers = {
    always_run = timestamp()
  }

  provisioner "local-exec" {
    command = "sqlcmd -S ${azurerm_mssql_server.example.name}.database.windows.net -d ${azurerm_mssql_database.test.name} -U ${var.administrator_login} -P ${var.administrator_login_password} -i ./auto-tuning.sql"
  }
}

This ensures that the sqlcmd utility executes auto-tuning.sql command always, in the terraform apply.

-- Enable automatic tuning on an individual database
ALTER DATABASE current SET AUTOMATIC_TUNING (CREATE_INDEX = ON);

❗ IMPORTANT: Please be aware that in this sample the MS SQL Server administrator credentials (generated) are used to execute the auto-tuning.sql command.

To use automatic tuning, the minimum required permission to grant to the user is Azure's built-in SQL Database contributor role. You can also consider using higher privilege roles such as SQL Server Contributor, SQL Managed Instance Contributor, Contributor, and Owner.

Source official Microsoft docs

📝 Requirements

• ✅ Azure Subscription
• ✅ Azure CLI
• ✅ Terraform
• ✅ sqlcmd Utility

🔧 Usage

After cloning this repository and installing the basic requirements, run the following commands in the ./src/terraform directory:

# Login to an Azure subscription
az login

# Tnitializes Terraform
terraform init

# Plans and Applies this sample's Azure infrastructure using Terraform
# It will prompt you for your current IP Address
terraform plan
terraform apply

🕐 Afer a few minutes, the expected output of terraform apply should be:

null_resource.db_setup: Destroying... [id=630773934331976362]
null_resource.db_setup: Destruction complete after 0s
null_resource.db_setup: Creating...
null_resource.db_setup: Provisioning with 'local-exec'...
null_resource.db_setup (local-exec): (output suppressed due to sensitive value in config)
null_resource.db_setup: Creation complete after 0s [id=7937627834098869972]

✔️ You can confirm that automatic tuning is enabled using the Azure Portal and open the SQL Database Automatic tuning settings:

🚿 Clean-up

You can destroy this sample's Azure infrastructure using:

# Destroy this sample's Azure infrastructure using Terraform
terraform destroy

💙 Contributing

Pull requests are welcome. For major changes, please open an issue first to discuss what you would like to change.

📗 License

MIT