Inconsistent SQL Always On cluster configuration in terraform state

[Gdcorbijn]

Is there an existing issue for this?

• I have searched the existing issues

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave comments along the lines of "+1", "me too" or "any updates", they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment and review the contribution guide to help.

Terraform Version

1.7.0

AzureRM Provider Version

3.107

Affected Resource(s)/Data Source(s)

azurerm_network_interface, azurerm_mssql_virtual_machine_group, azurerm_mssql_virtual_machine_availability_group_listener

Terraform Configuration Files

terraform {
  required_providers {
    azurerm = {
      source  = "hashicorp/azurerm"
      version = ">= 3.107.0"
    }
  }
  
  backend "local" {}

  required_version = "~> 1.7"
}

# Declare a standard provider block using your preferred configuration.
# This will target the SampleWorkload Subscription.
provider "azurerm" {
  subscription_id = "cdd1919a-e9ff-498d-b817-8f4b823da814"
  features {}
}

resource "azurerm_virtual_network" "vnet" {
    name                = "myvnet"
    location            = "canadacentral"
    resource_group_name = "myrg"
    address_space       = ["10.0.0.0/16"]
    dns_servers         = ["addsdomainip1","addsdomainip2"]
}

resource "azurerm_subnet" "snet" {
  name                 = "mysubnet"
  resource_group_name  = "myrg"
  virtual_network_name = azurerm_virtual_network.vnet.name
  address_prefixes     = ["10.0.1.0/24"]
}


resource "azurerm_windows_virtual_machine" "wvm1" {
  name                  = "myvm1"
  resource_group_name   = "myrg"
  location              = "canadacentral"
  zone                  = "1"
  size                  = "Standard_B2s_V2"
  admin_username        = "usradmin"
  admin_password        = "1somethingsecure!"
  network_interface_ids = [azurerm_network_interface.wvm_nic1.id]

  os_disk {
    caching              = "ReadWrite"
    storage_account_type = "Standard_LRS"
    disk_size_gb         = 200
  }

  source_image_reference {
    publisher = "MicrosoftSQLServer"
    offer     = "SQL2022-WS2022"
    sku       = "enterprise-gen2"
    version   = "latest"
  }
}

resource "azurerm_windows_virtual_machine" "wvm2" {
  name                  = "myvm2"
  resource_group_name   = "myrg"
  location              = "canadacentral"
  zone                  = "2"
  size                  = "Standard_B2s_V2"
  admin_username        = "usradmin"
  admin_password        = "1somethingsecure!"
  network_interface_ids = [azurerm_network_interface.wvm_nic2.id]

  os_disk {
    caching              = "ReadWrite"
    storage_account_type = "Standard_LRS"
    disk_size_gb         = 200
  }

  source_image_reference {
    publisher = "MicrosoftSQLServer"
    offer     = "SQL2022-WS2022"
    sku       = "enterprise-gen2"
    version   = "latest"
  }
}

resource "azurerm_network_interface" "wvm_nic1" {
  name                = "nic1"
  location            = "canadacentral"
  resource_group_name = "myrg"

  ip_configuration {    
    name                          = "internal"
    subnet_id                     = azurerm_subnet.snet.id
    private_ip_address_allocation = "Dynamic"
  }
}

resource "azurerm_network_interface" "wvm_nic2" {
  name                = "nic2"
  location            = "canadacentral"
  resource_group_name = "myrg"

  ip_configuration {
    name                          = "internal"
    subnet_id                     = azurerm_subnet.snet.id
    private_ip_address_allocation = "Dynamic"
  }
}

resource "azurerm_virtual_machine_extension" "wvm1_ad_domain_join" {
  name                 = "joindomain1"
  virtual_machine_id   = azurerm_windows_virtual_machine.wvm1.id
  publisher            = "Microsoft.Compute"
  type                 = "JsonADDomainExtension"
  type_handler_version = "1.3"

  settings = jsonencode({
    "Name" : "addsdomain",
    "OUPath" : "yourOU",
    "User" : "[email protected]",
    "Restart" : "true",
    "Options" : "3"
  })

  protected_settings = jsonencode({
    "Password" : "1somethingsecure!"
  })
}

resource "azurerm_virtual_machine_extension" "wvm2_ad_domain_join" {
  name                 = "joindomain2"
  virtual_machine_id   = azurerm_windows_virtual_machine.wvm2.id
  publisher            = "Microsoft.Compute"
  type                 = "JsonADDomainExtension"
  type_handler_version = "1.3"

  settings = jsonencode({
    "Name" : "addsdomain",
    "OUPath" : "yourOU",
    "User" : "[email protected]",
    "Restart" : "true",
    "Options" : "3"
  })

  protected_settings = jsonencode({
    "Password" : "1somethingsecure!"
  })
}

resource "azurerm_mssql_virtual_machine_group" "sqlserver_ag" {
  name                 = "myag"
  resource_group_name  = "myrg"
  location             = "canadacentral"

  sql_image_offer = "SQL2022-WS2022"
  sql_image_sku   = "Enterprise"

  wsfc_domain_profile {
    fqdn                = "addsdomain"
    cluster_subnet_type = "MultiSubnet"
    cluster_bootstrap_account_name = "domainusr@addsdomain"
    cluster_operator_account_name  = "domainusr@addsdomain"
    sql_service_account_name       = "domainusr@addsdomain"
    organizational_unit_path       = "yourOU"
    storage_account_primary_key    = azurerm_storage_account.stga.primary_access_key
    storage_account_url            = "${azurerm_storage_account.stga.primary_blob_endpoint}${azurerm_storage_container.quorum.name}"
  }
}

resource "azurerm_mssql_virtual_machine" "sqlvmha1" {
    virtual_machine_id               = azurerm_windows_virtual_machine.wvm1.id
    sql_license_type                 = "PAYG"
    sql_virtual_machine_group_id     = azurerm_mssql_virtual_machine_group.sqlserver_ag.id
    sql_connectivity_port            = "1433"
    sql_connectivity_type            = "PRIVATE"
    sql_connectivity_update_password = "1somethingsecure!"
    sql_connectivity_update_username = "localusr"

    wsfc_domain_credential {
      cluster_bootstrap_account_password = "1somethingsecure!" # Domain Admin account password
      cluster_operator_account_password  = "1somethingsecure!" # Domain Admin account password
      sql_service_account_password       = "1somethingsecure!" # A normal domain user account password
    }
}

resource "azurerm_mssql_virtual_machine" "sqlvmha2" {
    virtual_machine_id               = azurerm_windows_virtual_machine.wvm2.id
    sql_license_type                 = "PAYG"
    sql_virtual_machine_group_id     = azurerm_mssql_virtual_machine_group.sqlserver_ag.id
    sql_connectivity_port            = "1433"
    sql_connectivity_type            = "PRIVATE"
    sql_connectivity_update_password = "1somethingsecure!"
    sql_connectivity_update_username = "localusr"

    wsfc_domain_credential {
      cluster_bootstrap_account_password = "1somethingsecure!" # Domain Admin account password
      cluster_operator_account_password  = "1somethingsecure!" # Domain Admin account password
      sql_service_account_password       = "1somethingsecure!" # A normal domain user account password
    }
}

resource "azurerm_mssql_virtual_machine_availability_group_listener" "sqlserver_ag_listener" {
  name                          = "myaglistener"
  availability_group_name       = azurerm_mssql_virtual_machine_group.sqlserver_ag.name
  port                          = "1433"
  sql_virtual_machine_group_id  = azurerm_mssql_virtual_machine_group.sqlserver_ag.id

  multi_subnet_ip_configuration {
    private_ip_address = cidrhost("10.0.1.0/24", 99)
    subnet_id          = "10.0.1.0/24"
    sql_virtual_machine_id = azurerm_mssql_virtual_machine.sqlvmha1.id
  }

  multi_subnet_ip_configuration {
    private_ip_address = cidrhost("10.0.2.0/24", 99)
    subnet_id          = "10.0.2.0/24"
    sql_virtual_machine_id = azurerm_mssql_virtual_machine.sqlvmha2.id
  }

  replica {
    sql_virtual_machine_id = azurerm_mssql_virtual_machine.sqlvmha1.id
    role                   = "Primary"
    commit                 = "Synchronous_Commit"
    failover_mode          = "Automatic"
    readable_secondary     = "No"
  }

  replica {
    sql_virtual_machine_id = azurerm_mssql_virtual_machine.sqlvmha2.id
    role                   = "Secondary"
    commit                 = "Synchronous_Commit"
    failover_mode          = "Automatic"
    readable_secondary     = "No"
  }
}

# Create storage account to configure a cloud witness.
resource "azurerm_storage_account" "stga" {
  name                     = "vvdbclusterstawitness"
  resource_group_name      = "myrg"
  location                 = "canadacentral"
  account_tier             = "Standard"
  account_replication_type = "LRS"
}

# Create blob container under the storage account
resource "azurerm_storage_container" "quorum" {
  name                  = "quorum"
  storage_account_name  = azurerm_storage_account.stga.name
  container_access_type = "private"
}

Debug Output/Panic Output

Hard to share the entire log, too much info to obfuscate, but this is the main problem... the second execution of a plan shows that the ips added by the listener to the VM NICs will be removed:

# azurerm_network_interface.wvm_nic1 will be updated in-place
  ~ resource "azurerm_network_interface" "wvm_nic1" {
        id                             = "xxxxx"
        name                           = "nic1"
        # (13 unchanged attributes hidden)

      - ip_configuration {
          - name                          = "myaglistener-79cac856" -> null
          - primary                       = false -> null
          - private_ip_address            = "10.0.1.99" -> null
          - private_ip_address_allocation = "Static" -> null
          - private_ip_address_version    = "IPv4" -> null
          - subnet_id                     = "xxxxx" -> null
        }

        # (1 unchanged block hidden)
    }

# azurerm_network_interface.wvm_nic2 will be updated in-place
  ~ resource "azurerm_network_interface" "wvm_nic2" {
        id                             = "xxxxx"
        name                           = "nic2"
        # (13 unchanged attributes hidden)

      - ip_configuration {
          - name                          = "myaglistener-a2a725ab" -> null
          - primary                       = false -> null
          - private_ip_address            = "10.1.3.99" -> null
          - private_ip_address_allocation = "Static" -> null
          - private_ip_address_version    = "IPv4" -> null
          - subnet_id                     = "xxxxxxx" -> null
        }

        # (1 unchanged block hidden)
    }

Expected Behaviour

If no configs are done to the code, the second planning (and any subsequent planning) of the cluster configuration should say that there are no changes and the applies therefore should say that there were no infrastructure changes done.

Actual Behaviour

The listener IP configuration introduced in the VM (DB node) NIC by the azurerm_mssql_virtual_machine_availability_group_listener resource are removed because they are not part of the VM state, these only appear in the state of the listener resource.

Steps to Reproduce

terraform plan
terraform apply
terraform plan

Important Factoids

Nothing atypical but a few comments: Assumption: You have a managed adds domain (or a traditional AD) already created. Important Note: Both users VM local and AD, are admin and domain admin respectively, to simplify the always on creation process. Another thing I tied: Added explicitly a new ip config to each VM expecting that the listener resource uses them since they already exist, but no, the listener literally creates the ip config and adds it to the VM, so if I do this, there is an error indicating that the listener tried to allocate an IP that is not available.

References

No response