Enabling Azure SQL Database Automatic Tuning via ARM

user_141022 0
Viewed 2515

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"
  }
},
azure-resource-manager azure-powershell azure-sql-database azure
3 Answers

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'))]"
  ]
}
user_41672000

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.

user_61561190

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.

user_50755130
Related