Inconsistent Terraform Plan vs. State for mssql_database Resource

[srinathgvelly]

Current Behavior:
When running terraform plan, the output indicates that the mssql_database.database resource will be updated in-place. Specifically, it shows a change in the name attribute from "another_database_name" to "same_database_name". However, upon inspection of the Terraform state, it shows that the name attribute is already "same_database_name".

Expected Behavior:
The terraform plan output should reflect the actual state in the Terraform state file. If the name attribute is already set to "same_database_name" in the state, it should not be flagged for update.

Steps to Reproduce:

Ensure that the Terraform state is as described in the provided state snippet.
Execute terraform plan in the project directory.
Observe the plan output for the mssql_database.database resource.

Additional Information:

Terraform Version: 1.5.7
Provider: registry.terraform.io/pgssoft/mssql - v0.6.0
There are more than 20 databases in the same state file, and this behavior is happening for 4 of them.

Terraform plan:

# mssql_database.database will be updated in-place
  ~ resource "mssql_database" "database" {
        id        = "21"
      ~ name      = "another_database_name" -> "same_database_name"
        # (1 unchanged attribute hidden)
    }

Terraform state

{
  "mode": "managed",
  "type": "mssql_database",
  "name": "database",
  "provider": "provider[\"registry.terraform.io/pgssoft/mssql\"]",
  "instances": [
    {
      "schema_version": 0,
      "attributes": {
        "collation": "Latin1_General_CI_AS",
        "id": "21",
        "name": "same_database_name"
      },
      "sensitive_attributes": []
    }
  ]
}

[Phylu]

I assume that the issue happens if there is a database with a certain ID (in this case id = 21) deleted and then a new database created. Then instead of "creating a new database with a new id", the old database will be "overridden".

[Krad-dono]

For us this happens with RDS when it failover to another node in MultiAZ setup. Somehow during replication ID of databases is not the same in nodes. So only option atm is to failback to initial node where terraform apply was executed or change id in state.

[Phylu]

Maybe it makes sense to use the database name as the primary identifier for the resource instead of the ID? I am not familiar enough with MSSQL to determine whether this is possible, but it seems to be a (hopefully) easy solution to the issue.