Enabling Azure SQL Database Automatic Tuning via ARM

Question

I'm unable to find any documentation regarding enabling automatic tuning in a release pipeline i.e. through ARM templates or powershell, nor in the github arm quickstarts.

I can see in the resource explorer automatic tuning is mentioned, but I don't see how this reflects in the ARM templates.

{
  "name": "Microsoft.Sql/servers/automaticTuning/read",
  "display": {
    "provider": "Microsoft SQL Database",
    "resource": "Server Automatic Tuning",
    "operation": "Get automatic tuning settings for the server",
    "description": "Returns automatic tuning settings for the server"
  }
},
{
  "name": "Microsoft.Sql/servers/automaticTuning/write",
  "display": {
    "provider": "Microsoft SQL Database",
    "resource": "Server Automatic Tuning",
    "operation": "Update automatic tuning settings for the server",
    "description": "Updates automatic tuning settings for the server and returns updated settings"
  }
},

Thomas · Answer 1 · 2018-09-27T11:53:53.627

It is now possible to set automatic tuning option through ARM template at the logical server or the database level. I've used the Automation Script blade on the Azure portal to get these information: Sql server level:

{
  "type": "Microsoft.Sql/servers/advisors",
  "name": "[concat(parameters('sqlserverName'), '/', 'ForceLastGoodPlan')]",
  "apiVersion": "2014-04-01",
  "scale": null,
  "properties": {
    "autoExecuteValue": "Enabled"
  },
  "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', parameters('sqlserverName'))]"
  ]
},
{
  "type": "Microsoft.Sql/servers/advisors",
  "name": "[concat(parameters('sqlserverName'), '/', 'CreateIndex')]",
  "apiVersion": "2014-04-01",
  "scale": null,
  "properties": {
    "autoExecuteValue": "Enabled"
  },
  "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', parameters('sqlserverName'))]"
  ]
},
{
  "type": "Microsoft.Sql/servers/advisors",
  "name": "[concat(parameters('sqlserverName'), '/', 'DropIndex')]",
  "apiVersion": "2014-04-01",
  "scale": null,
  "properties": {
    "autoExecuteValue": "Enabled"
  },
  "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', parameters('sqlserverName'))]"
  ]
},
{
  "type": "Microsoft.Sql/servers/advisors",
  "name": "[concat(parameters('sqlserverName'), '/', 'DbParameterization')]",
  "apiVersion": "2014-04-01",
  "scale": null,
  "properties": {
    "autoExecuteValue": "Disabled"
  },
  "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', parameters('sqlserverName'))]"
  ]
},
{
  "type": "Microsoft.Sql/servers/advisors",
  "name": "[concat(parameters('sqlserverName'), '/', 'DefragmentIndex')]",
  "apiVersion": "2014-04-01",
  "scale": null,
  "properties": {
    "autoExecuteValue": "Disabled"
  },
  "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', parameters('sqlserverName'))]"
  ]
}

Database level:

{
    "type": "Microsoft.Sql/servers/databases/advisors",
    "name": "[concat(parameters('sqlserverName'), '/', parameters('databaseName'), '/', 'ForceLastGoodPlan')]",
    "apiVersion": "2014-04-01",
    "scale": null,
    "properties": {
    "autoExecuteValue": "Enabled"
    },
    "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', parameters('sqlserverName'))]",
    "[resourceId('Microsoft.Sql/servers/databases', parameters('sqlserverName'), parameters('databaseName'))]"
    ]
},
{
    "type": "Microsoft.Sql/servers/databases/advisors",
    "name": "[concat(parameters('sqlserverName'), '/', parameters('databaseName'), '/', 'CreateIndex')]",
    "apiVersion": "2014-04-01",
    "scale": null,
    "properties": {
    "autoExecuteValue": "Enabled"
    },
    "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', parameters('sqlserverName'))]",
    "[resourceId('Microsoft.Sql/servers/databases', parameters('sqlserverName'), parameters('databaseName'))]"
    ]
},
{
    "type": "Microsoft.Sql/servers/databases/advisors",
    "name": "[concat(parameters('sqlserverName'), '/', parameters('databaseName'), '/', 'DropIndex')]",
    "apiVersion": "2014-04-01",
    "scale": null,
    "properties": {
    "autoExecuteValue": "Enabled"
    },
    "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', parameters('sqlserverName'))]",
    "[resourceId('Microsoft.Sql/servers/databases', parameters('sqlserverName'), parameters('databaseName'))]"
    ]
},
{
    "type": "Microsoft.Sql/servers/databases/advisors",
    "name": "[concat(parameters('sqlserverName'), '/', parameters('databaseName'), '/', 'DbParameterization')]",
    "apiVersion": "2014-04-01",
    "scale": null,
    "properties": {
    "autoExecuteValue": "Disabled"
    },
    "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', parameters('sqlserverName'))]",
    "[resourceId('Microsoft.Sql/servers/databases', parameters('sqlserverName'), parameters('databaseName'))]"
    ]
},
{
  "type": "Microsoft.Sql/servers/databases/advisors",
  "name": "[concat(parameters('sqlserverName'), '/', parameters('databaseName'), '/DefragmentIndex')]",
  "apiVersion": "2014-04-01",
  "scale": null,
  "properties": {
    "autoExecuteValue": "Disabled"
  },
  "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', parameters('sqlserverName'))]",
    "[resourceId('Microsoft.Sql/servers/databases', parameters('sqlserverName'), parameters('databaseName'))]"
  ]
}

Good spot Thomas, I haven't had a chance to try it out yet, but looks very promising thanks +1 — Alex KeySmith, Feb 08 '18 at 11:18
Unfortunately, the `advisors` resource type seems to be read-only at present, so I don't believe it's possible to create tuning options via Bicep/ARM as yet. [MS Documentation](https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/servers/advisors?pivots=deployment-language-bicep) — Mike, Dec 15 '22 at 15:56
if you check the version reference in the arm template, it was possible. newest api version dont allow it anymore. good spot. — Thomas, Dec 15 '22 at 18:04

score 5 · Answer 2 · answered Oct 12 '17 at 13:46

Alex, as Estienne mentioned, this is currently not supported through ARM template, but there are alternative ways to do this.

1) All newly created databases, by default inherit the automatic tuning settings from the server. So if you configure automatic tuning on the server level, all new databases will inherit these settings upon creation.

2) You can use T-SQL, that you can run on the database to configure automatic tuning. Following T-SQL will configure automatic tuning on a database level:

ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO | INHERIT | CUSTOM

Choosing AUTO you will get a default set of options - CREATE_INDEX and FORCE_LAST_GOOD_PLAN enabled. Choosing INHERIT you will inherit the settings from the server. Choosing CUSTOM, you will need to explicitly state all automatic tuning options.

In case you want to explicitly enable/disable some of the options, you can use this:

ALTER DATABASE current SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = DEFAULT, CREATE_INDEX = ON, DROP_INDEX = OFF )

Setting some of the options to DEFAULT will pick up the database level configuration.

3) You can use REST API to configure automatic tuning. Something like this:
PATCH /subscriptions/{SUBID}/resourceGroups/{RGNAME}/providers/Microsoft.Sql/servers/{SRVNAME}/databases/{DBNAME}/automaticTuning/current?api-version=2017-03-01-preview HTTP/1.1
Host: management.azure.com
Authorization: Bearer
Content-Type: application/json
Cache-Control: no-cache
{ "properties": { "desiredState": "Custom", "options": { "forceLastGoodPlan": "On", "createIndex" : "On", "dropIndex" : "Off" }}}

Soon you will be able to configure this through PowerShell and managed library as well.

Thanks, I've marked Estienne's as the answer as it did deservedly answer my initial question, but I've given you the bounty from the depth of the answer thanks. — Alex KeySmith, Oct 12 '17 at 16:16

score 3 · Accepted Answer · answered Oct 06 '17 at 16:54

3

This feature is not officially supported at this time, although there exists infrastructure for it. We are working on providing support for it in the near future.

answered Oct 06 '17 at 16:54

hokkaidi

858
6
19

Thanks Estienne, is there an alternative to ARM using the existing infrastructure, perhaps powershell or calling the API's directly? – Alex KeySmith Oct 11 '17 at 11:04

Enabling Azure SQL Database Automatic Tuning via ARM

3 Answers3