Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Enabling Azure SQL Database Automatic Tuning via ARM

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"
  }
},
like image 815
Alex KeySmith Avatar asked Sep 29 '17 21:09

Alex KeySmith


People also ask

How do I enable auto tune on my Azure?

To enable automatic tuning on a server in Azure SQL Database, navigate to the server in the Azure portal and then select Automatic tuning in the menu. Select the automatic tuning options you want to enable and select Apply. Automatic tuning options on a server are applied to all databases on this server.

What is automatic tuning in Azure SQL Database?

Automatic tuning is a fully managed intelligent performance service that uses built-in intelligence to continuously monitor queries executed on a database and automatically improve their performance. This is achieved through dynamically adapting a database to changing workloads and applying tuning recommendations.

Which version of SQL Server has automated database tuning feature available?

Automatic tuning, introduced in SQL Server 2017 (14. x), notifies you whenever a potential performance issue is detected and lets you apply corrective actions, or lets the Database Engine automatically fix performance problems.

Which Azure SQL offering supports automatic database scaling?

Azure SQL Database serverless automatically scales compute for single databases based on workload demand and bills for compute used per second. Serverless also provides an option to automatically pause the database during inactive usage periods when only storage costs are billed.


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'))]"
  ]
}
like image 173
Thomas Avatar answered Sep 19 '22 21:09

Thomas


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.

like image 43
hokkaidi Avatar answered Sep 19 '22 21:09

hokkaidi


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.

like image 34
vvasic-MSFT Avatar answered Sep 20 '22 21:09

vvasic-MSFT