[AVM Module Issue]: Automation Tuning (Force Plan / Create Index / Drop Index) not available

[Gossef]

Check for previous/existing GitHub issues

• [X] I have checked for previous/existing GitHub issues

Issue Type?

Feature Request

Module Name

avm/res/sql/server

(Optional) Module Version

No response

Description

In Bicep, the resource 'Microsoft.Sql/servers/advisors' version 2014-04-01 lets you set the Automation Tuning settings on the SQL Server for Force Plan, Create Index and/or Drop Index.

For example this code works as of today (September 10th 2024):

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

In later API versions this resource seems to have become read-only, but that also means that you'd have to set it in the portal manually whenever a new server is deployed. For example:

  resource alternativeAutomaticIndexCreation 'Microsoft.Sql/servers/automaticTuning@2021-11-01-preview' = {
    parent: sqlServer
    name: 'current'
    properties: {
      options: {
        createIndex: {
          desiredState: 'On'
        }
      }
    }
  }

As it gives these messages: 1) Resource type "Microsoft.Sql/servers/advisors@2021-11-01" can only be used with the 'existing' keyword.bicep(BCP245) 2) The property "properties" is read-only. Expressions cannot be assigned to read-only properties. If this is an inaccuracy in the documentation, please report it to the Bicep Team.bicepBCP073

The AVM module for SQL Server doesn't seem to contain the Automatic Tuning option either and it's rather inconvenient. Would it be possible to either implement the 2014 API version, or a different way to actually set the Automatic Tuning options so we don't have to rely on old API versions to prevent manual intervention.

(Optional) Correlation Id

No response

[microsoft-github-policy-service[bot]]

[!IMPORTANT] The "Needs: Triage :mag:" label must be removed once the triage process is complete!

[!TIP] For additional guidance on how to triage this issue/PR, see the BRM Issue Triage documentation.

[avm-team-linter[bot]]

@Gossef, thanks for submitting this issue for the avm/res/sql/server module!

[!IMPORTANT] A member of the @Azure/avm-res-sql-server-module-owners-bicep or @Azure/avm-res-sql-server-module-contributors-bicep team will review it soon!

[microsoft-github-policy-service[bot]]

[!WARNING] Tagging the AVM Core Team (@Azure/avm-core-team-technical-bicep) due to a module owner or contributor having not responded to this issue within 3 business days. The AVM Core Team will attempt to contact the module owners/contributors directly.

[!TIP]

• To prevent further actions to take effect, the "Status: Response Overdue 🚩" label must be removed, once this issue has been responded to.
• To avoid this rule being (re)triggered, the ""Needs: Triage :mag:" label must be removed as part of the triage process (when the issue is first responded to)!

[microsoft-github-policy-service[bot]]

[!WARNING] Tagging the AVM Core Team (@Azure/avm-core-team-technical-bicep) due to a module owner or contributor having not responded to this issue within 3 business days. The AVM Core Team will attempt to contact the module owners/contributors directly.

[!TIP]

• To prevent further actions to take effect, the "Status: Response Overdue 🚩" label must be removed, once this issue has been responded to.
• To avoid this rule being (re)triggered, the ""Needs: Triage :mag:" label must be removed as part of the triage process (when the issue is first responded to)!

[microsoft-github-policy-service[bot]]

[!CAUTION] This issue requires the AVM Core Team's (@Azure/avm-core-team-technical-bicep) immediate attention as it hasn't been responded to within 6 business days.

[!TIP]

• To avoid this rule being (re)triggered, the "Needs: Triage :mag:" and "Status: Response Overdue :triangular_flag_on_post:" labels must be removed when the issue is first responded to!
• Remove the "Needs: Immediate Attention :bangbang:" label once the issue has been responded to.

[emichellecarter]

Will take a look at this.