Merge azurerm_sql_database and azurerm_mssql_database

[frugecn]

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 "+1" or "me too" comments, 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

Description

Can we merge azurerm_sql_database and azurerm_mssql_database so that we get the new features on both resources. The main difference is mssql uses SKU now. We're getting changes to one resource but not the other and getting some drift.

New or Affected Resource(s)

azurerm_sql_database
azurerm_mssql_database

[yupwei68]

hi @frugecn ,thanks for opening this issue. We are in process of eliminating our old sql database azurerm_sql_database and we'll not update new feature on our old sql database.

[katbyte]

Hey @frugecn,

To expand on @yupwei68 - the azurerm_mssql_database resource is the new version of azurerm_sql_database that is using a newer version of the API that supports more features. As such we have been migrating all the supported features of the old resource to the new and it should support everything that azurerm_sql_database does.

May i ask what functionality is missing from the new resource?

[frugecn]

What caught my attention was in v2.6.0, connection policy was added to azurerm_sql_server, but I just realized that is the server resource and not the database resource. I thought it was added to the sql_database resource and not the mssql_resource, but since it is sql_server resource, it was a mistake on my part. Sorry.

Looking at the documentation between the two:

import from like a bacpac
extended_auditing_policy

Is the new resource supposed to be backward compatible with the old service model? Or will we keep both resources depending on which service model you choose? As long as Azure is offering both service models (which they currently are), we'll need both models. I see smaller databases using the old service model for a while and larger databases using the new model.

The extended_auditing_policy is now on the server resource, so that one may not be a big deal, unless you need a different policy than the server level.

[yupwei68]

Hi @frugecn , thanks for your suggestion. As @katbyte said, we are in process of migrating all the supported features into new resource azurerm_mssql_database. We have an open PR for supporting extended_auditing_policy in azurerm_mssql_database #6402 now

[katbyte]

@frugecn - the new resources should be interchangeable except when a feature of the new API is required. I have also opened #6677 to add azurerm_mssql_server. Now that audit policies have been added it looks like only import remains?

[frugecn]

@katbyte -- what about backwards compatibility with the old DTU model? As long as that is still available, we'll need both in the resource.

[yupwei68]

@frugecn It's still available in azurerm_mssql_database, you could assign sku_name as "S0","P2". Please refer azurerm_mssql_database

[jrottenberg]

Would like to point out the doc needs deprecation notice for azurerm_sql_* resources.

Also the h1 for https://www.terraform.io/docs/providers/azurerm/r/sql_server.html makes it a bit misleading : azurerm_mssql_server

[keiransteele]

A couple of things that I've noticed are not possible in azurerm_mssql_database that are possible in azurerm_sql_database:

1. The "Recovery" creation option is not available so it's not possible to create a database from a geo-replicated backup. I note in the code for the newer mssql resource it says that the API doesn't support it but according to the Microsoft API documentation it is supported and in anycase if it works for the older resource then it should be possible on the newer resource? If I knew where to start I might have a go at fixing this one.

2. The "Restore" creation option won't work on the new resource because it is not possible to set "source_database_deletion_date" which is required for this creation option.

[yupwei68]

Hi @keiransteele Thanks, I'm fixing it.

[yupwei68]

Currently I'm blocked by Azure/azure-rest-api-specs#10162

[MaximeMarck]

Hi - Thanks for the work on merging the two resources.

Could you please confirm that the new resources now covers all the features that were available in azurerm_sql_database & azurerm_sql_server ?

We currently have a live environment having a few SQL databases and we would like to move to the new azurerm_mssql_* resources at some point. Do you have any guidance/steps regarding the migration? Would the steps listed in https://www.terraform.io/docs/providers/azurerm/guides/migrating-between-renamed-resources.html apply right?

[nexxai]

Currently in the same boat as @MaximeMarck - have a bunch of production databases that we need to migrate from the old resource type to the new. Any guidance would be appreciated.

[ghost]

This has been released in version 2.29.0 of the provider. Please see the Terraform documentation on provider versioning or reach out if you need any assistance upgrading. As an example:

provider "azurerm" {
    version = "~> 2.29.0"
}
# ... other configuration ...

[TPPWC]

Hi there,

what about azurerm_sql_firewall_rule and azurerm_sql_virtual_network_rule? Do they change at all or will be just renamed?

[ghost]

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!