Activate Create Index and Drop Index for Automatic tuning in bicep for sql database

Question

How can I activate the automatic tuning Create and Drop index using bicep?

I've search and there seems to be this resource in Bicep Microsoft.Sql/servers/databases/automaticTuning but I don't have the options to activate only Create and Drop index.

Update 1: There's my Bicep code:

resource sqlServer 'Microsoft.Sql/servers@2022-02-01-preview' = {
  name: '${appName}-${environment}-sql'
  location: location
  properties: {
    administratorLogin: '${environment}-admin'
    administratorLoginPassword: sqlPassword
    minimalTlsVersion: '1.2'
    restrictOutboundNetworkAccess: 'Disabled'
    publicNetworkAccess: publicNetworkAccess ? 'Enabled' : 'Disabled'
  }
  identity: {
    type: 'SystemAssigned'
  }
}

resource sqlServer 'Microsoft.Sql/servers@2022-02-01-preview' existing = {
  name: sqlServerName
}

resource sqlServerDatabase 'Microsoft.Sql/servers/databases@2022-02-01-preview' = {
  parent: sqlServer
  name: '${appName}-${environmentName}-db-${dbName}'
  location: location
  sku: environmentSettings[toLower(environmentName)].sku
}

Answer 1

It is possible to set the automatic tuning create and drop indexes with the resource provider Microsoft.Sql/servers/advisors in bicep.

The functionality of this resource works only to reference the existing resource in the latest version releases. If you want to deploy a new resource, you need to use the older API version: 2014-04-01 as detailed below.

main.bicep:

param servername string = 'myserverj'
@minLength(1)
param servernameAdminLogin string = 'jahnaviadmin'
param servernameAdminLoginPassword string = 'xxxx'

resource server 'Microsoft.Sql/servers@2023-08-01-preview' = {
  name: servername
  location: 'West Europe'
  properties: {
    administratorLogin: servernameAdminLogin
    administratorLoginPassword: servernameAdminLoginPassword
    version: '12.0'
  }
}

resource ForceLast 'Microsoft.Sql/servers/advisors@2014-04-01' = {
  parent: server
  name: 'ForceLastGoodPlan'
  properties: {
    autoExecuteValue: 'Enabled'
  }
}

resource Create 'Microsoft.Sql/servers/advisors@2014-04-01' = {
  parent: server
  name: 'CreateIndex'
  properties: {
    autoExecuteValue: 'Enabled'
  }
}

resource Drop 'Microsoft.Sql/servers/advisors@2014-04-01' = {
  parent: server
  name: 'DropIndex'
  properties: {
    autoExecuteValue: 'Enabled'
  }
}

Deployment succeeded:

You can also refer this SO to enable it using ARM template.