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.
{
"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
