CODEWITHSUNDEEP
azure-sql-databaseazure-resource-managerazure-rm-templateelastic-database-pool
PavanKumar GVVS
PavanKumar GVVS

Reputation: 1045

Sql Database Elastic pool and sku combination is invalid

I am able to create Sql Server, Sql database, sql elastic Pool Successfully using ARM templates. But when I trying to create new database with existing elastic pool name. I am getting below error.

Without elastic pool id, database is creating successfully.

Both Sql database Elastic Pool and database are using same location, tier, edition etc.Also When tried in azure portal it created successfully.

 "error": {
"code": "ResourceDeploymentFailure",
"message": "The resource operation completed with terminal provisioning state 'Failed'.",
"details": [
  {
    "code": "ElasticPoolSkuCombinationInvalid",
    "message": "Elastic pool 'sqlsamplepool' and sku 'Basic' combination is invalid."
  }
]

ARM Template:

 {
 "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
 "parameters": {
  "collation": {
  "type": "string",
  "metadata": {
    "description": "The collation of the database."
  },
  "defaultValue": "SQL_Latin1_General_CP1_CI_AS"
},
"skutier": {
  "type": "string",
  "metadata": {
    "description": "The edition of the database. The DatabaseEditions enumeration contains all the 
    valid editions. e.g. Basic, Premium."
  },
  "allowedValues": [ "Basic", "Standard", "Premium" ],
  "defaultValue": "Basic"
},
"resourcelocation": {
  "type": "string",
  "defaultValue": "[resourceGroup().location]",
  "metadata": {
    "description": "Location for all resources."
  }
},
"sqlservername": {
  "type": "string",
  "metadata": {
    "description": "The name of the sql server."
  }
},
"zoneRedundant": {
  "type": "bool",
  "metadata": {
    "description": "Whether or not this database is zone redundant, which means the replicas of this database will be spread across multiple availability zones."
  },
  "defaultValue": false
},
"sqlElasticPoolName": {
  "type": "string",
  "metadata": {
    "description": "The Elastic Pool name."
  }
},
"databaseName": {
  "type": "string"
}
 },
 "functions": [],
"variables": {  },
  "resources": [
{
  "type": "Microsoft.Sql/servers/databases",
  "apiVersion": "2020-08-01-preview",
  "name": "[concat(parameters('sqlservername'),'/',parameter('databaseName'))]",
  "location": "[parameters('resourcelocation')]",
  "sku": {
    "name": "[parameters('skutier')]",
    "tier": "[parameters('skutier')]"
  },
  "properties": {
    "collation": "[parameters('collation')]",
    "zoneRedundant": "[parameters('zoneRedundant')]",
  "elasticPoolId":"[concat('/subscriptions/',subscription().subscriptionId,'/resourceGroups/',resourceGroup().name,'/providers/Microsoft.Sql/servers/',parameters('sqlservername'),'/elasticPools/',parameters('sqlElasticPoolName'))]"
  }
 }
]
}

Upvotes: 0

Views: 2274

Answers (1)

PavanKumar GVVS
PavanKumar GVVS

Reputation: 1045

I am not sure what wrong with "2020-08-01-preview" version but its working fine with stable version. below is my partial arm template code that working.

I changed to 2014-04-01 api version.

  "comments": "If Elastic Pool Name is defined, then curent database will be added to elastic pool.",
  "type": "Microsoft.Sql/servers/databases",
  "apiVersion": "2014-04-01",
  "name": "[concat(parameters('sqlservername'),'/',variables('dbname'))]",
  "location": "[parameters('resourcelocation')]",
  "properties": {
    "collation": "[parameters('collation')]",
    "zoneRedundant": "[parameters('zoneRedundant')]",
    "elasticPoolName":"[if(not(empty(parameters('sqlElasticPoolName'))),parameters('sqlElasticPoolName'),'')]",
    "edition": "[parameters('skutier')]"
  }

Upvotes: 0

Related Questions