Azure SQL Database - read_scale always set to false during initial creation

[rapster83]

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

Terraform (and AzureRM Provider) Version

Terraform v0.12.7

• provider.azurerm v1.32.0

Affected Resource(s)

• azurerm_sql_database

Terraform Configuration Files

resource "azurerm_sql_database" "sql_default_db" {
        collation                        = "SQL_LATIN1_GENERAL_CP1_CI_AS"
        create_mode                      = "Default"
        creation_date                    = "2019-08-30T08:38:56.303Z"
        default_secondary_location       = "North Europe"
        edition                          = "Hyperscale"
        id                               = "/subscriptions/ba8cc2f1-98a2-5816-9eb4-7a5335b7ea8b/resourceGroups/rg-toporg-xdf-dwh/providers/Microsoft.Sql/servers/sqlsrv-toporg-xdf-dwh/databases/sqldb-toporg-xdf-single-db"
        location                         = "westeurope"
        max_size_bytes                   = "-1"
        name                             = "sqldb-toporg-xdf-single-db"
      ~ read_scale                       = true -> false
        requested_service_objective_id   = "9380cd0e-b025-4e9d-829c-c1918c1614bb"
        requested_service_objective_name = "HS_Gen4_1"
        resource_group_name              = "rg-toporg-xdf-dwh"
        server_name                      = "sqlsrv-toporg-xdf-dwh"
        tags                             = {
            "comment"     = "empty"
            "companyName" = "TOPORG"
            "costCenter"  = "empty"
            "environment" = "PROD"
        }

        threat_detection_policy {
            disabled_alerts      = []
            email_account_admins = "Disabled"
            email_addresses      = []
            retention_days       = 0
            state                = "Disabled"
            use_server_default   = "Disabled"
        }
    }

Debug Output

~ read_scale = true -> false

Expected Behavior

Azure SQL Database should be created the the 'read scale' of 0, like the following screenshot illustrates. As the read_scale parameter is set to false explicitly.

Actual Behavior

Even if I set the parameter "read_scale" to 'false' explicitly, the Azure SQL Database will be created with the value 'true' as the following screenshot illustrates.

If I set the read_scale back to 0 in the Azure Portal manually, no changes occur and the code works fine. Seems like there is a problem with the read_scale parameter on initial creation of a SQL Database.

Steps to Reproduce

1. terraform apply

[mybayern1974]

Hi @stefan-rapp ,

I tried things locally and wanna share my findings. Hope it could help you and clarify the next follow ups to unblock you.

1. There are two physical properties: read_scale and secondary_replica_count. Distinguishing them is the basic for we to go on the following. For example, you mentioned “…If I set the read_scale back to 0 in the Azure Portal manually…”, while please allow me to say the description is inaccurate. What you set is “secondary_replica_count” rather than “read_scale”;
2. There is an enum of DB tier. Different tiers have different property settings. To make things easy, let’s only talk on edition = Hyperscale as what your config was.
3. You ran into the symptom that setting “read_scale” to “false” and then getting “read_scale” being “true” and “secondary_replica_count” being 1, while this is not only for terraform. Indeed I repro this symptom on both Azure portal and Azure rest api. So this should be the service side behavior rather than Terraform specific. Though I agree it looks a little bit weird, there is Azure document describing this parameter is not settable for the Hyperscale DB tier: "This property is only settable for Premium and Business Critical databases". Terraform needs to point this out in the TF config doc.
4. There is a work around: if you wish your “read_scale” to false, what you need to do is to explicitly set the “secondary_replica_count” to 0 in your terraform input. I’ve verified it worked in both Azure portal and Azure rest api. However, this workaround does not work right now because terraform has not enabled the secondary_replica_count property yet.

As a summary, there are two follow ups as described from the above. I can regard them in my feature backlog.

Hope the above help clarify.

[rapster83]

Hi @mybayern1974,

First, thank you for your reply and the helpful information. :-)
I tried to solve the problem with the parameter “read_scale”, as there is no “secondary_replica_count” yet. Also not in the Terraform Doc

“An argument named "secondary_replica_count" is not expected here.”

So that does not solve my problem. The module I implemented for Azure SQL DBs, creates several DBs with different Azure SQL Editions. As you mentioned in the Azure Documentation for the parameter “properties.readScale” it is not available for Hyperscale editions:

“If enabled, connections that have application intent set to readonly in their connection string may be routed to a readonly secondary replica. This property is only settable for **Premium** and **Business Critical** databases.``” That was the reason why I want to have the read_scale` disabled for Hyperscale edition DBs.

To explicitly set the “secondary_replica_count” to 0 in the terraform input would be a great idea. Is there a feature request already, so a parameter will be supported with the next release of the Azure Provider for Terraform?

THX

[mybayern1974]

@stefan-rapp , I would work on this "secondary_replica_count" feature aiming at the next release. Please stay tuned.

[mybayern1974]

@stefan-rapp , I'd like to share with you some staged updates:

1. We noticed the feature that has secondary_replica_count configurable was newly enabled by Azure, in terms of programing, at the beginning of Sep, when the Go SDK did not cover that yet, so we will use the newly released Go SDK;
2. After we introduced the new SDK we found that the new SDK has a lot of incompatibilities with the currently being used one, which means enabling this feature requires we fix a bunch of regression code conflicts impacting all sql related terraform azure resources. The required effort exceeds simply adding one property;
3. Because of # 2, I'm seeing risk to deliver this feature to meet the next release which is expected to be 1~2 weeks away. While I wanna have you have positive expectation to some extent that we are still actively working on this feature right now and will keep updating you for any progress.

[hbuckle]

Looking at the provider code, read_scale is an optional attribute, but it defaults to false. I would think that if this is optional terraform should not be setting it unless the user specifies a value.

[mybayern1974]

@stefan-rapp , assume the issue has been fixed. Please expect it comes into being in v1.36.0

[ghost]

This has been released in version 1.36.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 = "~> 1.36.0"
}
# ... other configuration ...

[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 👉 [email protected]. Thanks!