ARM Template for Databases with Advisors

Contributor

Hi All,

 

I am following the below template and Para file for creating Multiple DB's. The ARM is working fine while creatings DB's without advisors. If i add advisors in resources it is throwing error.

 

I need to create Multiple DB's with Advisors. PLease help to solve the issue.

 

Template:

 

{
    "contentVersion""1.0.0.0",
    "parameters": {
        "serverName": {
            "defaultValue""dbprodserver01",
            "type""String",
            "metadata": {
                "description""Name for the SQL server"
            }
        },
        "administratorLogin": {
            "type""string"
        },
        "administratorLoginPassword": {
            "type""securestring"
        },
        "databaseNames": {
            "defaultValue": [
                "DB_prod_100",
                "DB_test_200"
            ],
            "type""Array",
            "metadata": {
                "description""Array of names for the SQL databases"
            }
        },
        "location": {
            "defaultValue""East US",
            "type""String",
            "metadata": {
                "description""Location for server and DBs"
            }
        },
        "serverNameExternalid": {
            "defaultValue""/subscriptions/eb7f41f7-91d2-40d0-974d-28e42755c3ae/resourceGroups/Test/providers/Microsoft.Sql/servers/dbprodserver01",
            "type""String"
        },
        "elasticPoolName": {
            "defaultValue""dbprodpool",
            "type""String"
        }
    },
    "variables": {},
    "resources": [
        {
            "type""Microsoft.Sql/servers",
            "apiVersion""2020-02-02-preview",
            "name""[parameters('serverName')]",
            "location""East US",
            "properties": {
                "administratorLogin""[parameters('administratorLogin')]",
                "administratorLoginPassword""[parameters('administratorLoginPassword')]",
                "version""12.0"
            }
        },
        {
            "type""Microsoft.Sql/servers/databases",
            "apiVersion""2020-02-02-preview",
            "name""[concat(string(parameters('serverName')), '/', string(parameters('databaseNames')[copyIndex()]))]",
            "location""East US",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
            ],
            "sku": {
                "name""ElasticPool",
                "tier""Standard",
                "capacity"0
            },
            "kind""v12.0,user",
            "properties": {
                "collation""SQL_Latin1_General_CP1_CI_AS",
                "maxSizeBytes"268435456000,
                "elasticPoolId""[concat(parameters('serverNameExternalid'), '/elasticPools/', parameters('elasticPoolName'))]",
                "catalogCollation""SQL_Latin1_General_CP1_CI_AS",
                "zoneRedundant"false,
                "readScale""Disabled",
                "storageAccountType""GRS"
            },
            "resources": [
                {
                    "type""securityAlertPolicies",
                    "apiVersion""2020-02-02-preview",
                    "name""default",
                    "dependsOn": [
                        "[resourceId('Microsoft.Sql/servers/databases', parameters('serverName'), parameters('databaseNames')[copyIndex()])]"
                    ],
                    "properties": {
                        "state""Enabled"
                    }
                },
                {
    "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('serverName'))]",
    "[resourceId('Microsoft.Sql/servers/databases', parameters('sqlserverName'), parameters('databaseName'))]"
    ]
},
{
    "type""Microsoft.Sql/servers/databases/advisors",
    "name""[concat(parameters('serverName'), '/', parameters('databaseNames'), '/', 'CreateIndex')]",
    "apiVersion""2014-04-01",
    "scale"null,
    "properties": {
    "autoExecuteValue""Enabled"
    },
    "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]",
    "[resourceId('Microsoft.Sql/servers/databases', parameters('serverName'), parameters('databaseNames'))]"
    ]
},
{
    "type""Microsoft.Sql/servers/databases/advisors",
    "name""[concat(parameters('serverName'), '/', parameters('databaseNames'), '/', 'DropIndex')]",
    "apiVersion""2014-04-01",
    "scale"null,
    "properties": {
    "autoExecuteValue""Enabled"
    },
    "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]",
    "[resourceId('Microsoft.Sql/servers/databases', parameters('serverName'), parameters('databaseNames'))]"
    ]
},
{
    "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('serverName'), '/', parameters('databaseNames'), '/DefragmentIndex')]",
  "apiVersion""2014-04-01",
  "scale"null,
  "properties": {
    "autoExecuteValue""Disabled"
  },
  "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', parameters('serverName'))]",
    "[resourceId('Microsoft.Sql/servers/databases', parameters('serverName'), parameters('databaseNames'))]"
  ]
}
            ],
            "copy": {
                "name""databaseCopy",
                "count""[length(parameters('databaseNames'))]"
            }
        }
    ]
}
 
Regards,
Vignesh
 
 
1 Reply

@vigneshkrcegmailcom 

 

Hi as far i know this is  not supported through ARM template. In the documentation it's mentionned " For more information, see Database advisor recommendations in the Azure portal, in PowerShell, and in the REST API." You may know that once the automatic tuning is applied at server level all new database will inherit  the config. 

 

https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview

https://docs.microsoft.com/en-us/azure/azure-sql/database/database-advisor-implement-performance-rec...