Azure SQL DB - Cant move database in or out of ElasticPool

ermeaney commented 6 years ago

Terraform Version

Terraform v0.11.0

provider.azurerm v0.3.2

Affected Resource(s)

Please list the resources as a list, for example:

azurerm_sql_database

Terraform Configuration Files

Module

resource "azurerm_sql_database" "RzDatabase" {
  count = "${var.datacenter_count}"
  name = "${format("%sSqlDatabase%s",var.name, var.tier)}"
  create_mode = "${count.index == 0 ? "Default" : "OnlineSecondary"}"
  resource_group_name = "${var.resourcegroup[count.index]}"
  location = "${var.locations[var.datacenters[count.index]]}"
  server_name = "${var.server_name[count.index]}"
  requested_service_objective_name = "${var.service_objective}" #set to ElasticPool
  elastic_pool_name = "${var.elastic_pool_name}"
  tags = "${var.tags}"
}

Resource

module "ActivitiesDatabase" {
  source = "./SqlDatabases"
  datacenter_count = "${var.datacenter_count}"
  name = "Activities"
  service_objective = "${var.DBServiceLevel["Activities"]}"
  server_name = "${module.SqlServers.name}"
  tier = "${var.tier}"
  resourcegroup = "${module.ResourceGroups.name}"
  datacenters = "${local.datacenters}"
  locations = "${local.locations}"
  tags = "${local.tags}"
}

Expected Behavior

Terraform should be able to move a SQL database into or out of an ElasticPool

Actual Behavior

both ways fail.

Moving a DB out of a pool

requested_service_objective_name: "ElasticPool" => "S0"

Error: Error applying plan:

1 error(s) occurred:

module.ActivitiesDatabase.azurerm_sql_database.RzDatabase: 1 error(s) occurred :
azurerm_sql_database.RzDatabase: sql.DatabasesClient#CreateOrUpdate: Failure r esponding to request: StatusCode=400 -- Original Error: autorest/azure: Service returned an error. Status=400 Code="Unknown" Message="Unknown service error"

Moving a DB into a pool

azurerm_sql_database.RzDatabase: sql.DatabasesClient#CreateOrUpdate: Failure sending request: StatusCode=200 -- Original Error: Long running operation terminated with status 'Failed': Code="40860" Message="Elastic pool 'rz-pool-dev' and service level objective 'S0' combination is invalid."

Steps to Reproduce

terraform apply

Important Factoids

I also tried using the parameter requested_service_objective_id on the database with the correct GUID and that also resulted in the same behavior.

Totally a guess, but it appears that that the two setting that need to change are happening independently.

I also tested with the Azure Powershell commands to verify that Azure REST API's themselves were not broken, and that worked.

ermeaney commented 6 years ago

After a bit of digging, I am wondering if this is actually a bug in the Azure Rest SDK. I am saying that because the docs say that if elasticPoolName is set:

The name of the elastic pool the database is in. If elasticPoolName and requestedServiceObjectiveName are both updated, the value of requestedServiceObjectiveName is ignored. Not supported for DataWarehouse edition.

My go is really weak, but tracing through the source of this provider and the azure go library, it appears that the provider is doing the correct thing.

steve-hawkins commented 6 years ago

I was considering raising another issue, but I believe my issue is related

I'm seeing the following error when simply trying to move from one Service Level Objective to another:-

requested_service_objective_name: "S0" => "S2"

Error: Error applying plan:

1 error(s) occurred:

* azurerm_sql_database.test: 1 error(s) occurred:

* azurerm_sql_database.test: Long running operation terminated with status 'Failed': Code="0" Message="The requested Service Objective Name does not match the requested Service Objective Id."

I agree with your guess and it looks like the error message above confirms that both Requested Service Objective ID and Name need to be changed at the same time

looking at azurerm/resource_arm_sql_database.go there is a comment about this:-

            "requested_service_objective_name": {
                Type:             schema.TypeString,
                Optional:         true,
                Computed:         true,
                DiffSuppressFunc: ignoreCaseDiffSuppressFunc,
                // TODO: add validation once the Enum's complete
                // https://github.com/Azure/azure-rest-api-specs/issues/1609
            },

That issue 1609 appears to have been closed now

I'm new to GO too, so not sure how much help I will be

WodansSon commented 6 years ago

Closing this issue as #1503 was merged on Jul 5, 2018.

ermeaney commented 6 years ago

Hey, thanks for getting to this, and I wish I could test for you, but the world has moved on for me, and I am not using terraform or Azure anymore.

ghost commented 4 years ago

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.

If you feel this issue should be reopened, we encourage creating a new issue linking back to this one for added context. If you feel I made an error 🤖 🙉 , please reach out to my human friends 👉 hashibot-feedback@hashicorp.com. Thanks!

hashicorp / terraform-provider-azurerm