通过ARM启用Azure SQL数据库自动调优

Question

我无法找到任何有关在发布管道中启用自动调优的文档，比如通过ARM模板或powershell，或者在github arm快速启动中。

我可以在资源管理器中看到自动调优，但我看不出这在ARM模板中是如何反映的。

{
  "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"
  }
},

Answer 1

这个特性目前还没有得到官方的支持，尽管有它的基础设施。我们正在努力在不久的将来为其提供支持。

Answer 2

现在可以通过逻辑服务器或数据库级别的ARM模板设置自动调优选项。我在Azure门户上使用了Automation Script刀片来获取以下信息: server级别：

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

数据库级别：

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

Answer 3

正如Estienne所提到的，Alex目前不支持ARM模板，但是有其他方法可以做到这一点。

1)所有新创建的数据库，默认情况下从服务器继承自动调优设置。因此，如果您在服务器级别上配置自动调优，所有新数据库在创建时都将继承这些设置。

2)您可以使用to，可以在数据库上运行它来配置自动调优。下面的level将在数据库级别上配置自动调优：

ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO | INHERIT | CUSTOM

选择AUTO，您将得到一组默认的选项- CREATE_INDEX和FORCE_LAST_GOOD_PLAN已启用。选择“继承”将从服务器继承设置。选择自定义，您将需要显式地声明所有自动调优选项。

如果要显式启用/禁用某些选项，可以使用以下命令：

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

将某些选项设置为默认选项将获取数据库级别的配置。

3)可以使用REST配置自动调优。就像这样：

补丁/subscriptions/{SUBID}/resourceGroups/{RGNAME}/providers/Microsoft.Sql/servers/{SRVNAME}/databases/{DBNAME}/automaticTuning/current?api-version=2017-03-01-preview HTTP/1.1

主机: management.azure.com

授权:持票人

内容-类型:应用程序/json

缓存-控制:无缓存

{“属性”：{ "desiredState"：“自定义”，“选项”：{ "forceLastGoodPlan"："On"，"createIndex“："On"，"dropIndex”："Off“}}

很快，您将能够通过PowerShell和托管库来配置它。