Azure SQL Server automatic tuning

[gpthome]

trafficstars

Does Terraform support Azure SQL Server automatic tuning? If so, can you please point me in that direction? Here is a snapshot from the Azure GUI to visualize the request: We use azurerm_mssql_server to manage our resource. Thanks!

[faisaleem-na]

any updates on this and whether it is likely to be looked at. i am also in need of enabling this and currently having to run a script after the terraform apply in order to enable it, thanks

[stormshaun]

Also looking for this.. any references or terraform usage that can help?

[CarlosSardo]

Also looking for this.. any references or terraform usage that can help?

@stormshaun, @faisaleem-na , @gpthome

Take a look at this alternative sample using Terraform null_resource and sqlcmd utility. Hope it helps!

[v4ld3r5]

Looking forward to have this feature implemented

Also looking for this.. any references or terraform usage that can help?

@stormshaun, @faisaleem-na , @gpthome

Take a look at this alternative sample using Terraform null_resource and sqlcmd utility. Hope it helps!

Hi @CarlosSardo, this looks pretty cool, thank you. Only issue is, it's setup at database level. Personally I would prefer to set at server level and let the databases underneath to inherit them. Have you tried it against the SQL server?

[MelleD]

Any update on this, because the Azure defaults not hits our requirements...

[mikemadeja]

Hello,

I was able to accomplish this against an SQL Database with the azapi. I think you can try with the Server as well, I'll give that example a go when I have some time but my business case was getting a specific SQL DB having autotuning enabled.

Link

Hope it helps..

[mikemadeja]

I was able to do it at a server level with azapi as well... The only thing I notice is I can't do a loop for each adviser (CreateIndex, DropIndex, etc...), so I had to declare each resource.

Link

[tpaul1611]

Here is my solution with azapi_resource_action (in cdktf for typescript):

new ResourceAction(this, 'automatic_tuning', {
  type: 'Microsoft.Sql/servers/automaticTuning@2021-11-01',
  resourceId: `${this.server.id}/automaticTuning/current`,
  method: 'PATCH',
  body: Fn.jsonencode({
    properties: {
      desiredState: 'Auto',
      options: {
        createIndex: { desiredState: 'On' },
        dropIndex: { desiredState: 'On' },
        forceLastGoodPlan: { desiredState: 'On' }
      }
    }
  }),
  dependsOn: [this.server]
});

[joakimlemb]

Workaround in HCL format, based on @tpaul1611 example above.

# https://learn.microsoft.com/en-us/rest/api/sql/server-automatic-tuning/update?view=rest-sql-2021-11-01
resource "azapi_resource_action" "example" {
  resource_id = "${azurerm_mssql_server.example.id}/automaticTuning/current"
  type        = "Microsoft.Sql/servers/automaticTuning@2021-11-01"
  method      = "PATCH"
  body = jsonencode({
    properties = {
      desiredState = "Auto"
      options = {
        # Valid desiredState options = "Default","On","Off"
        forceLastGoodPlan = { desiredState = "Default" }
        createIndex       = { desiredState = "On" }
        dropIndex         = { desiredState = "Off" }
      }
    }
  })
  depends_on = [azurerm_mssql_database.example]
}

[abilous-ti]

@mybayern1974 @magodo Do you have any ETA when it is going to be implemented?