Managed SQL database instance

[srusru]

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

---

It would be nice if we have a terraform feature for Azure SQL Managed database instance.

[rbankole]

i second this! :)

[Chris-Gray94]

I think this needs to be done now that it is in GA.
https://azure.microsoft.com/en-us/blog/azure-sql-database-managed-instance-general-purpose-tier-general-availability/

[TechyMatt]

Is there anyone working on this or can I start writing a PR for the functionality?

[tombuildsstuff]

@jeffreyCline have you started looking into this yet? If not I don't think anybody's working on this from HashiCorp/Microsoft's side, so you should be good @mb290 👍 (thanks in advance 😄)

[TechyMatt]

@tombuildsstuff maybe I've bitten off much more than I can chew! The RestAPI documentation is available here and relatively straight forward, however i'm really struggling to find a concise list of the options for SKUs: https://docs.microsoft.com/en-us/rest/api/sql/managedinstances/createorupdate

There are also lots of dependencies on the VNet that the subnet is in, so i'm trying to figure out the best way to either a) document or b) template this. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-vnet-configuration#requirements

The final challenge is for the first SQL MI in a Subnet MS take up to 6 hours to provision so TF is going to require a very large timeout.

[theharleyquin]

Bump - just checking in to see if there is any movement on this.

[tombuildsstuff]

@theharleyquin not at this time - as @mb290 has mentioned the first Managed Instance in a Subnet can take around 6 hours to provision (up to a maximum of 30h) - which makes developing support for this fairly challenging, unfortunately.

[juanjojulian]

@theharleyquin not at this time - as @mb290 has mentioned the first Managed Instance in a Subnet can take around 6 hours to provision (up to a maximum of 30h) - which makes developing support for this fairly challenging, unfortunately.

I had a chat yesterday with an Azure representative and he promise me that first deployment takes around 35 minutes now. They are selling Managed SQL instances as the way to go for SQL data bases, this Terraform resource is highly needed.

[ghost]

Currently handling MI db creation with azurerm inline script task within a Azure DevOps release pipeline. All other tasks for IaC uses terraform tasks. Using both task types mean that I have to store release variables in the DevOps variable group and .tfvars. Would be nice to do away with the DevOps variable group and only use the .tfvars but I can't do that until terraform supports MI db creation.

YAML for the DevOps task.

steps:

• task: AzurePowerShell@3
  displayName: 'Azure PowerShell script to create new Azure SQL MI database on existing MI'
  inputs:
  azureSubscription: 'name
  ScriptType: InlineScript
  Inline: |
  New-AzureRmResource -Location $(location) -ResourceId "/subscriptions/$(subscriptionId)/resourceGroups/$(sqlMIrg)/providers/Microsoft.Sql/managedInstances/$(sqlMIname)/databases/$(sqlMIdatabasename)"
  -ApiVersion "2017-03-01-preview" `
  -AsJob -Force

[tombuildsstuff]

@a138076 as mentioned in this comment (in the PR #2727) unfortunately this requires a change coming in 2.0 (custom timeouts) - as such support for this is currently blocked on v2.0 (which we're working on the dependencies for at the moment)

[tombuildsstuff]

@TheKangaroo reading the reply from support I believe that's implying "the API is currently working" (e.g. green on the status page) which is unrelated and unfortunately we're still blocked for the reasons outlined above.

There's an internal thread with the product/engineering teams tracking this (which support won't have access too) - but afaik at this point we're waiting on the service team to make some changes to the API to support this. Once that's done we should be able to take another look here 👍

[TheKangaroo]

Ah thank you for the clarification @tombuildsstuff :)

[PriyankaRanganath]

hi @katbyte / @tombuildsstuff ,

Just a quick question. Currently as per my understanding, Managed instance in TF is not supported yet because of below items:

1. create or update takes 3+ hours - can be fixed with custom timeouts
2. If implemented, acceptance tests fails because we can not delete virtual cluster programmatically

for option2, if there is any possibility to get Virtual cluster name from the managed instance GET endpoint, use Virtual cluster endpoints to check if it has any other managed instances and delete the virtual cluster if the current managed instance was the last one in it and we handle all this in Managed instance delete method, would that be enough to accept managed instance PR until a more permanent solution for virtual cluster is available ?

Asking this because SQL prod team is ready to add extra field for virtual cluster name in get mi rest endpoint and we handle the VC delete logic ourselves in azurerm provider (if the VC doesnt has any other managed instance or pools in it) if and only if Hashicorp team accepts the temp solution I mentioned above.

Let me know if that is enough to get my PR for MI get accepted.

[mbfrahry]

Hey @PriyankaRanganath, thanks for taking the time to right out a detailed response. I will say that the first item on long creation time isn't so much of an issue but the second item is definitely the hangup for us.

Unfortunately, while your proposed solution looks straightforward on paper, it causes quite a few issues in implementation. The first that comes to mind is what happens if the virtual cluster fails to delete? The managed instance is gone and now we don't have a way to get to that virtual cluster. We've left the user stranded because of a resource that they didn't even know was created. Since the virtual cluster is the concern of the service, when the last database is gone, it should be safe to remove the subnet; so perhaps the subnet should be de-associated from the virtual cluster during deletion of the db.

Terraform users expect Terraform to manage their infrastructure end to end but the virtual cluster that's created outside Terraform breaks that model. I wish we could work around that in Terraform but there aren't any good solutions that we'd be comfortable supporting other than having the service team handle the virtual cluster end to end in a timely manner or to let Terraform handle the virtual cluster end to end.

[PriyankaRanganath]

Hi @mbfrahry ,

Thanks for the quick response.
I have a question regarding failure in VC deletion issue.
IMO, if we implement proposed solution above, VC deletion should fail only if during deletion of last MI(before VC deletion starts) someone sends a create MI request for that same VC. In all other cases, the VC drop should be successful(if it is empty). Else we can implement retry logic until it is successful.
Correct me if I a wrong.

Would that temp solution work?

[tombuildsstuff]

@PriyankaRanganath the API either needs to expose the VC so that it's manageable by third parties, or delete ths whilst the last MI is being deleted, so I don't believe this approach changes anything unfortunately?

[PriyankaRanganath]

Hi @tombuildsstuff ,

If the product team is ready to expose the VC in API endpoint(as well as in Go SDK) so that it could be managed by 3rd parties,
would you consider the temp solution I mentioned above ?

As I mentioned previously, SQL product team is ready to expose VC details in the API if and only if Hashicorp team accepts my temp proposed solution.

[mbfrahry]

@PriyankaRanganath unfortunately whilst the proposed temporary solution may work for the tests in that example, in practice that's not sufficient. To go into a few of these scenarios:

1. A user creates a SQL managed instance from scratch and tears it down (8h)
2. A user creates/has an existing a SQL managed instance, being the sole Managed Instance on the Virtual Cluster - attempting to make a change to this which requires recreating (deleting and immediately recreating) this - for example if the collation needs to be changed - which will take an entire workday (8h) to replace due to deleting the MI which deletes the VC (~4h) and the subsequently recreating them both (another ~4h).
3. Creating a MI within an existing VC - takes considerably less time and the tainting issue isn't so much of a problem.
4. Organizations want different teams/users to deploy multiple Managed Instances (which end up on the same Virtual Cluster) - from a code perspective these would be configured as different Terraform Modules (e.g. Deployments) referencing the same cluster. Today whilst the API exposes the Virtual Cluster via the Delete, Get and Update methods - there's no way to create one - so how would users achieve this? Whilst we could determine that no other MI's are deployed to the VC and clean this up automatically - there's no guarantee that we have permission to do so in ARM, so what happens when this fails?
5. There's a larger issue when we think about other resources that need a MI - if each test takes 8h to run, and say there's 50 tests - even provisioning 20 in parallel is going to take 20 hours for those tests to run; ultimately whilst the Virtual Cluster management is one issue - this would be the slowest test suite across the entire Azure Provider. Whilst this isn't an immediate issue, it's a blocker for us if it prevents us adding further Managed Instance functionality long-term.

Whilst the solution you've proposed above may fix the first use-case I've mentioned, unfortunately it doesn't work for the rest.

We've spent a little time investigating how this API works, and it appears the following Payload is available for Get-ing and Updating Virtual Cluster:

{
	"properties": {
		"subnetId": "subnetID"
	},
	"location": "eastus",
	"name": "randomName"
}

Since the Delete, Get and Update API's are available - meaning Virtual clusters can already be managed by users; and the payload appears to be quite simple - we believe it should be possible to expose the Create API without exposing any further implementation details?

This allows for the longer initial provisioning time of the Virtual Cluster to be provisioned one time in these scenarios, and referenced as necessary - enabling users to share this as necessary. The Azure API here could then provision one if a user doesn't specify the ID of an existing Managed Cluster (as is the case in AKS and some other Azure API's). This approach would both allow users to provision/reference existing clusters (which would allow this to work in Terraform) - and have the existing approach working of provisioning a Virtual Cluster behind the scenes.

Alternatively Azure could automatically manage the provisioning/deletion of the Virtual Cluster /during/ the deletion of the last Managed Instance within that API - however that would again cause extended deletions when "tainting" (deleting and immediately recreating) the last SQL Managed Instance within a Virtual Cluster.

Based on the use-cases above - I don't see how it'd be possible to use this resource without Terraform being able to manage the lifecycle of the Virtual Cluster. It's worth noting from a implementation perspective, since the Delete, Get and Update API calls for a Virtual Cluster are already exposed - this doesn't expose any more implementation details of the Virtual Cluster. It's also possible to do this in a backwards compatible manner - where a user either specifies the ID of an existing Virtual Cluster to provision the Managed Instance within it - or (if they omit this field) then the Azure API can spin up the Virtual Cluster behind the scenes as is done today (and the automatically clean this up as needed).

As such is it possible to expose the Create API call here too?

---

cc @JeffreyRichter - since this seems extremely curious from an an ARM perspective that a Resource can be retrieved, updated and deleted - but not provisioned; which feels like an anti-pattern?

[czmirek]

Would it be possible to add at least a data source for the "Azure SQL Managed Instance" and resource for the "Managed database"?

Something like this

data "azurerm_managed_instance" "example" 
{
   name = "mymi"
   resource_group = "myrg"
   username = data.some_username_source
   password = data.some_password_source
}

resource "azurerm_managed_instance_database" "exampledb"
{
    name = "mydb"
    resource_group = "myrg"
    managed_instance = data.azurerm_managed_instance.example.name
}

Also it would be totally wonderful if I could read the existing connection string of the managed instance from supported attributes and move it into a key vault.

Also - I cannot imagine anyone using terraform for MI with the instance starting for so many hours, but creation of single databases don't take that long.

az sql midb create -g myresourcegroup --mi existingmi --name mydb --- this took less than minute, I think that is definitely acceptable for most people.

edit: grammar

[TheKangaroo]

For now, our workaround is using an ARM template. Since it took me a lot of time figuring out how to do it best, I want to spare you the pain and copy past it here for reference.

template

mi.tf

resource "azurerm_resource_group_template_deployment" "managed-instance" {
  name                = local.name_lower
  resource_group_name = azurerm_resource_group.rg.name
  deployment_mode     = "Incremental"

  template_content    = file("${path.module}/mi.json")
  parameters_content  = <<TEMPLATE
{
    "managedInstanceName": {
        "value": "${local.name_lower}"
    },
    "location": {
        "value": "germanywestcentral"
    },
    "managedInstanceTags": {
        "value": {
            "mytag": "${var.tag}"
        }
    },
    "skuName": {
        "value": "GP_Gen5"
    },
    "skuEdition": {
        "value": "GeneralPurpose"
    },
    "administratorLogin": {
        "value": "${var.username}"
    },
    "administratorLoginPassword": {
      "reference": {
        "keyVault": {
        "id": "${data.azurerm_key_vault.kv.id}"
        },
        "secretName": "${local.password_name}"
      }
    },
    "subnetId": {
        "value": "${data.azurerm_subnet.sql-mi.id}"
    },
    "storageSizeInGB": {
        "value": 256
    },
    "vCores": {
        "value": 8
    },
    "licenseType": {
        "value": "BasePrice"
    },
    "hardwareFamily": {
        "value": "Gen5"
    },
    "dnsZonePartner": {
        "value": ""
    },
    "collation": {
        "value": "SQL_Latin1_General_CP1_CI_AS"
    },
    "proxyOverride": {
        "value": "Proxy"
    },
    "publicDataEndpointEnabled": {
        "value": false
    },
    "minimalTlsVersion": {
        "value": "1.2"
    },
    "timezoneId": {
        "value": "W. Europe Standard Time"
    },
    "storageAccountType": {
        "value": "GRS"
    }
}
TEMPLATE
  depends_on = [
      # you probably need this for dependency management
      azurerm_key_vault_secret.password,
  ]
  timeouts {
    create = "8h"
  }
}

# example for dependencies
resource "azurerm_monitor_metric_alert" "myalert" {
  name                = "alert"
  resource_group_name = azurerm_resource_group.rg.name
  scopes              = [local.managed_instance_id]
  description         = "alert"
  target_resource_type = "Microsoft.Sql/managedInstances"
  frequency = "PT5M"
  window_size = "PT5M"
  severity = "3"

  criteria {
    metric_namespace = "Microsoft.Sql/managedInstances"
    metric_name      = "avg_cpu_percent"
    aggregation      = "Average"
    operator         = "GreaterThan"
    threshold        = 80
  }
  timeouts {}
  action {
    action_group_id = azurerm_monitor_action_group.ag.id
  }
  depends_on = [
      # you need to manually specify dependency like this
      azurerm_resource_group_template_deployment.managed-instance,
  ]
}

mi.json

{
    "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "managedInstanceName": {
            "type": "String",
            "metadata": {
                "description": "The name of the Managed Instance."
            }
        },
        "location": {
            "type": "String",
            "defaultValue": "germanywestcentral",
            "metadata": {
                "description": "The location of the Managed Instance. Default is the location of the selected resource group."
            }
        },
        "managedInstanceTags": {
            "type": "Object",
            "metadata": {
                "description": "Resource tags to associate with the instance."
            }
        },
        "skuName": {
            "type": "String",
            "defaultValue": "GP_Gen5",
            "allowedValues": [
                "GP_Gen4",
                "GP_Gen5",
                "BC_Gen4",
                "BC_Gen5"
            ],
            "metadata": {
                "description": "Managed instance SKU. If SKU is not set, skuEdition and hardwareFamily values have to be populated."
            }
        },
        "skuEdition": {
            "type": "String",
            "defaultValue": "GeneralPurpose",
            "allowedValues": [
                "GeneralPurpose",
                "BusinessCritical"
            ],
            "metadata": {
                "description": "SKU Edition for the Managed Instance. In case skuName is set this parameter is ignored."
            }
        },
        "administratorLogin": {
            "type": "String",
            "metadata": {
                "description": "The login of the Managed Instance admin."
            }
        },
        "administratorLoginPassword": {
            "type": "SecureString",
            "metadata": {
                "description": "The password of the Managed Instance admin."
            }
        },
        "subnetId": {
            "type": "String",
            "metadata": {
                "description": "Target subnet Id."
            }
        },
        "storageSizeInGB": {
            "type": "Int",
            "defaultValue": 256,
            "minValue": 32,
            "metadata": {
                "description": "Determines how much Storage size in GB to associate with instance. Increments of 32 GB allowed only."
            }
        },
        "vCores": {
            "type": "Int",
            "defaultValue": 8,
            "allowedValues": [
                4,
                8,
                16,
                24,
                32,
                40,
                64,
                80
            ],
            "metadata": {
                "description": "The number of vCores."
            }
        },
        "licenseType": {
            "type": "String",
            "defaultValue": "LicenseIncluded",
            "allowedValues": [
                "LicenseIncluded",
                "BasePrice"
            ],
            "metadata": {
                "description": "Determines license pricing model. Select 'LicenseIncluded' for a regular price inclusive of a new SQL license. Select 'Base Price' for a discounted AHB price for bringing your own SQL licenses."
            }
        },
        "hardwareFamily": {
            "type": "String",
            "defaultValue": "Gen5",
            "allowedValues": [
                "Gen4",
                "Gen5"
            ],
            "metadata": {
                "description": "Compute generation for the instance. In case skuName is set this parameter is ignored."
            }
        },
        "dnsZonePartner": {
            "type": "String",
            "defaultValue": "",
            "metadata": {
                "description": "The resource id of another Managed Instance whose DNS zone this Managed Instance will share after creation."
            }
        },
        "collation": {
            "type": "String",
            "defaultValue": "SQL_Latin1_General_CP1_CI_AS",
            "metadata": {
                "description": "Collation of the Managed Instance."
            }
        },
        "proxyOverride": {
            "type": "String",
            "defaultValue": "Proxy",
            "allowedValues": [
                "Proxy",
                "Redirect"
            ],
            "metadata": {
                "description": "Determines connection type for private endpoint. Proxy connection type enables proxy connectivity to Managed Instance. Redirect mode enables direct connectivity to the instance resulting in improved latency and throughput."
            }
        },
        "publicDataEndpointEnabled": {
            "type": "Bool",
            "defaultValue": false,
            "metadata": {
                "description": "Determines whether public data endpoint will be enabled, required for clients outside of the connected virtual networks. Public endpoint will always default to Proxy connection mode."
            }
        },
        "minimalTlsVersion": {
            "type": "String",
            "defaultValue": "1.2",
            "allowedValues": [
                "1.0",
                "1.1",
                "1.2"
            ],
            "metadata": {
                "description": "The minimum TLS version enforced by the Managed Instance for inbound connections."
            }
        },
        "timezoneId": {
            "type": "String",
            "defaultValue": "UTC",
            "metadata": {
                "description": "Id of the timezone. Allowed values are timezones supported by Windows."
            }
        },
        "storageAccountType": {
            "type": "String",
            "defaultValue": "GRS",
            "allowedValues": [
                "GRS",
                "ZRS",
                "LRS"
            ],
            "metadata": {
                "description": "Option for configuring backup storage redundancy. Selecting 'GRS' will enable 'RA-GRS'."
            }
        }
    },
    "variables": {},
    "resources": [
        {
            "type": "Microsoft.Sql/managedInstances",
            "apiVersion": "2020-02-02-preview",
            "name": "[parameters('managedInstanceName')]",
            "location": "[parameters('location')]",
            "tags": "[parameters('managedInstanceTags')]",
            "sku": {
                "name": "[parameters('skuName')]",
                "tier": "[parameters('skuEdition')]"
            },
            "identity": {
                "type": "SystemAssigned"
            },
            "properties": {
                "administratorLogin": "[parameters('administratorLogin')]",
                "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
                "subnetId": "[parameters('subnetId')]",
                "storageSizeInGB": "[parameters('storageSizeInGB')]",
                "vCores": "[parameters('vCores')]",
                "licenseType": "[parameters('licenseType')]",
                "hardwareFamily": "[parameters('hardwareFamily')]",
                "dnsZonePartner": "[parameters('dnsZonePartner')]",
                "collation": "[parameters('collation')]",
                "proxyOverride": "[parameters('proxyOverride')]",
                "publicDataEndpointEnabled": "[parameters('publicDataEndpointEnabled')]",
                "minimalTlsVersion": "[parameters('minimalTlsVersion')]",
                "timezoneId": "[parameters('timezoneId')]",
                "storageAccountType": "[parameters('storageAccountType')]",
                "backupStorageRedundancy": "[parameters('storageAccountType')]"
            }
        }
    ]
}

You can hardcode any of the paramters to make it way shorter.

[cuntoulishifu]

Last time I deployed a SQL Managed Instance, June 6, it took six hours in westeurope. Once you have the cluster in place and if you deploy another managed instances to the same cluster its true that the process is much faster (minutes). I see another added problem to the managed instance deployment using Terraform, it deploys routes and NSG rules along with the cluster although I instructed it not to do it as per documentation, at the end of the process all those rules and routes will not be under Terraform management.

---

Hi juanjojulian, I encounter the same problem. some routes created by SQL MI, Terraform can't control those so
making plan cause Terraform to delete those routes. I try to hardcode those routes but I feel that is not good.

My quesion is how you to solve this? thanks a lot

[juanjojulian]

Last time I deployed a SQL Managed Instance, June 6, it took six hours in westeurope. Once you have the cluster in place and if you deploy another managed instances to the same cluster its true that the process is much faster (minutes). I see another added problem to the managed instance deployment using Terraform, it deploys routes and NSG rules along with the cluster although I instructed it not to do it as per documentation, at the end of the process all those rules and routes will not be under Terraform management.

Hi juanjojulian, I encounter the same problem. some routes created by SQL MI, Terraform can't control those so
making plan cause Terraform to delete those routes. I try to hardcode those routes but I feel that is not good.

My quesion is how you to solve this? thanks a lot

Hi @cuntoulishifu , we decided not to control any resource related to SQL MI via Terraform, we let SQL MI deployment process create a dedicated route table and NSG and we keep both of them out of Terraform state. Be aware that once deployed, Azure will change both NSG and route table at any time to accommodate any needed change, we found this behaviour in a locked route table associated with a SQL MI and Azure support confirmed it, those resources are under their control and they change them if needed.

[cuntoulishifu]

Last time I deployed a SQL Managed Instance, June 6, it took six hours in westeurope. Once you have the cluster in place and if you deploy another managed instances to the same cluster its true that the process is much faster (minutes). I see another added problem to the managed instance deployment using Terraform, it deploys routes and NSG rules along with the cluster although I instructed it not to do it as per documentation, at the end of the process all those rules and routes will not be under Terraform management.

Hi juanjojulian, I encounter the same problem. some routes created by SQL MI, Terraform can't control those so
making plan cause Terraform to delete those routes. I try to hardcode those routes but I feel that is not good.
My quesion is how you to solve this? thanks a lot

Hi @cuntoulishifu , we decided not to control any resource related to SQL MI via Terraform, we let SQL MI deployment process create a dedicated route table and NSG and we keep both of them out of Terraform state. Be aware that once deployed, Azure will change both NSG and route table at any time to accommodate any needed change, we found this behaviour in a locked route table associated with a SQL MI and Azure support confirmed it, those resources are under their control and they change them if needed.

Thank @juanjojulian for your great reply! then looks like hardcode doesn't work as well ... anyway thanks again!

[TheKangaroo]

@juanjojulian and @cuntoulishifu
We manage the route table and nsg with no routes/rules defined with terraform and the managed instance via arm template.
The managed instance/azure itself creates rules and route entries but terraform does not care. We can add rules and routes via azurerm_network_security_rule and azurerm_route and these coexist side by side with the azure managed routes and rules without any issues so far.

[okandamar0610]

For now, our workaround is using an ARM template. Since it took me a lot of time figuring out how to do it best, I want to spare you the pain and copy past it here for reference.

template
You can hardcode any of the paramters to make it way shorter.

I am also using ARM template to deploy Managed Instance, I also want to set Active Directory Admin. I am using below resource however it is asking me to assign Directory Readers role to Instance. Any idea how to tackle this in ARM template?
{
"name": "string",
"type": "Microsoft.Sql/managedInstances/administrators",
"apiVersion": "2020-11-01-preview",
"properties": {
"administratorType": "ActiveDirectory",
"login": "string",
"sid": "string",
"tenantId": "string"
}
}

[TheKangaroo]

I've never done this in arm, I try do deploy as much as I can in terraform, but
https://docs.microsoft.com/en-us/azure/templates/microsoft.authorization/2018-01-01-preview/roleassignments?tabs=json should be the way to deploy role assignments via arm.

[okandamar0610]

I've never done this in arm, I try do deploy as much as I can in terraform, but
https://docs.microsoft.com/en-us/azure/templates/microsoft.authorization/2018-01-01-preview/roleassignments?tabs=json should be the way to deploy role assignments via arm.

I have tried this. This is actually to deploy RBAC roles. It will not work with Active Directory Roles.

[Marcus-James-Adams]

Also - I cannot imagine anyone using terraform for MI with the instance starting for so many hours, but creation of single databases doesn't take that long.

I would happily wait 6 hours for a managed instance cluster to be created if I could get it under terraform.
the ability to then pass instance names, database names programmatically into key vault's o rother processes would be of tremedess help to anyone who manages their estate via TF

[SecDWizar]

Similarly to others I was forced to do so in ARM,

Attached module for reference, it's short, concise and supports create both MI and databases (and returning resource IDs as needed). supports internal only (though it's trivial to change that) and creates the subnet as needed (paradigm I'm using generally as to logically state what I want from a module without inner-wrokings)

usage example:

locals {
  resource_group_name       = "nztest-sql"
  resource_group_location   = "westeurope" 
  administrator_login       = "vmpadmin"
}

resource "azurerm_resource_group" "testsql" {
  name     = local.resource_group_name
  location = local.resource_group_location
}

resource "azurerm_virtual_network" "testsql" {
  name                = "vnet-testsql"
  resource_group_name = azurerm_resource_group.testsql.name
  location            = azurerm_resource_group.testsql.location
  address_space       = [ "10.10.0.0/24" ]
}

module "sql" {
  source = "../../modules/sqlManagedInstanceInternal"

  sql_managed_instance_name = "baba"
  resource_group_name       = azurerm_resource_group.testsql.name
  resource_group_location   = azurerm_resource_group.testsql.location
  administrator_login       = local.administrator_login
  subnet_address            = [ "10.10.0.0/25" ]
  vnet_name                 = azurerm_virtual_network.testsql.name
  databases                 = [ "vmp" ]
}

# goes to outputs.tf, here for exampe
output "sql_password" {
    value = nonsensitive(module.sql.sql_managed_instance.admin_login_password)
}

output "sql_id" {
    value = module.sql.sql_managed_instance.id
}

output "sql_database_ids" {
    value = module.sql.sql_managed_instance.database_ids
}

module's main.tf:

locals {

    sql_managed_instance_name = "sqlm-${var.sql_managed_instance_name}"
    
    resources = flatten([{
        type        = "Microsoft.Sql/managedInstances"
        apiVersion  = "2021-02-01-preview"
        name        = local.sql_managed_instance_name
        location    = var.resource_group_location
        tags        = var.tags
        sku         = {
            name    = var.sku_name
            tier    = var.sku_tier
        }
        identity    = {
            type    = "SystemAssigned"
        }
        properties  = {
            administratorLogin         = var.administrator_login
            administratorLoginPassword = random_password.password.result
            subnetId                   = azurerm_subnet.snet_sql.id
            licenseType                = var.license_type
            vCores                     = var.number_of_cores
            storageSizeInGB            = var.storage_size_gb
            collation                  = var.collation
            publicDataEndpointEnabled  = false
            proxyOverride              = var.proxy_override
            timezoneId                 = var.time_zone_id
            minimalTlsVersion          = "1.2"
            storageAccountType         = var.storage_account_type
            zoneRedundant              = var.zone_redundant
        }
    }, [ for database_name in var.databases : {
        type          = "Microsoft.Sql/managedInstances/databases"
        apiVersion    = "2021-02-01-preview"
        name          = "${local.sql_managed_instance_name}/${database_name}"
        location      = var.resource_group_location
        dependsOn     = [
            "[resourceId('Microsoft.Sql/managedInstances', '${local.sql_managed_instance_name}')]"
        ]
        "properties"  = {
            collation = var.collation
        }
    }]])

    template_content = jsonencode({
        "$schema"      = "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#"
        contentVersion = "1.0.0.0"
        variables      = { databaseNames = var.databases } # used for output (putting TF in ARM here and using this for output IDs)
        resources      = local.resources
        outputs        = {
            resourceID = {
                type   = "String"
                value  = "[resourceId('Microsoft.Sql/managedInstances', '${local.sql_managed_instance_name}')]"
            }
            databasesResourceIDs = {
                type   = "array"
                copy   = {
                    count = length(var.databases)
                    input = "[resourceId('Microsoft.Sql/managedInstances/databases', '${local.sql_managed_instance_name}', variables('databaseNames')[copyIndex()])]"
                }
            }
        }
    })
}

resource "random_password" "password" {
    length           = 16
    lower            = true
    number           = true
    upper            = true
    special          = true
    min_lower        = 3
    min_numeric      = 3
    min_upper        = 3
    min_special      = 1
    override_special = "_%@!"

#   keepers {
#       sqlmanagedinstance_id = azurerm_resource_group_template_deployment.sqlmanagedinstance.id
#   }
}

resource "azurerm_subnet" "snet_sql" {

  name                 = "snet-sql"
  resource_group_name  = var.resource_group_name
  virtual_network_name = var.vnet_name
  address_prefixes     = var.subnet_address

  delegation {
    
    name = "delegation-sql"
    
    service_delegation {
      name = "Microsoft.Sql/managedInstances"
      actions = [ 
        "Microsoft.Network/virtualNetworks/subnets/join/action",
        "Microsoft.Network/virtualNetworks/subnets/prepareNetworkPolicies/action",
        "Microsoft.Network/virtualNetworks/subnets/unprepareNetworkPolicies/action"
      ]
    }
  }
}

# create an empty rt and connect it to snet
resource "azurerm_route_table" "snet_sql" {
  name                          = "rt-snet-sql"
  location                      = var.resource_group_location
  resource_group_name           = var.resource_group_name
  disable_bgp_route_propagation = false

  # filled automatically by the template sql managed instance
  # lifecycle {
  #   ignore_changes = [
  #     route
  #   ]
  # }
}

resource "azurerm_subnet_route_table_association" "snet_sql" {
  subnet_id      = azurerm_subnet.snet_sql.id
  route_table_id = azurerm_route_table.snet_sql.id
}

#create an empty nsg and connect it to snet
resource "azurerm_network_security_group" "snet_sql" {
  name                = "nsg-snet-sql"
  location            = var.resource_group_location
  resource_group_name = var.resource_group_name

  # filled automatically by the template sql managed instance
  # lifecycle {
  #   ignore_changes = [
  #     security_rule
  #   ]
  # }
}

resource "azurerm_subnet_network_security_group_association" "snet_sql" {
  subnet_id                 = azurerm_subnet.snet_sql.id
  network_security_group_id = azurerm_network_security_group.snet_sql.id
}


resource "azurerm_resource_group_template_deployment" "sqlmanagedinstance" {
    name                = "sqlm-${var.sql_managed_instance_name}"
    resource_group_name = var.resource_group_name
    deployment_mode     = "Incremental"
    template_content    = local.template_content 
    
    timeouts { create = "8h" }
    depends_on = [ # must be so destroy won't fail on ConflictWithNetworkIntentPolicy
        azurerm_subnet_route_table_association.snet_sql,
        azurerm_subnet_network_security_group_association.snet_sql
    ]
}

module's variables.tf

#########################
## mandatory variables ##
#########################
variable "resource_group_name" {
  description = "resource group name"
  type        = string
}

variable "resource_group_location" {
  description = "resource group location"
  type        = string
}

variable "sql_managed_instance_name" {
  description = "sql managed instance name"
  type        = string
}

variable "administrator_login" {
  description = "administrator user name"
  type        = string
}

variable "vnet_name" {
    description = "sql managed instance vnet name on which to generate subnet for integration"
    type        = string
}

variable "subnet_address" {
  description = "sql manage instance subnet addresses list with only one element, ex. [\"10.6.0.0/27\"], min size /28"
  type        = list

  validation {
    # length(var.subnet_address) == 1
    # -------------------------------------------
    # terraform's subnet address_prefixes is a list, however in our subscription at least 
    # there must be exactly one subnet prefix in that list, putting more then one (ex. [ "10.8.1.0/24", "10.8.2.0/24" ]) results in:
    # 
    #   "Subscription xxxxxxxx is not registered for feature Microsoft.Network/AllowMultipleAddressPrefixesOnSubnet"
    # 
    # therefor it is assumed there is axactly one subnet in that list (but kept that list stracture for compatibility with generic terraform docs)
    # in the future if that's no longer the case this can be removed   --------------------------------------------------------------------------
    # 
    # bits <= 27 is because on rotation twice as much instances (addresses) are used, so 27 will alow 13 instances total in whole of the ASP
    # https://docs.microsoft.com/en-us/azure/app-service/web-sites-integrate-with-vnet#subnet-requirements
    condition = length(var.subnet_address) == 1 && alltrue([
      for snet in var.subnet_address : tonumber(split("/",snet)[1]) <= 28
    ])
    error_message = "Subnet address must be a valid (list of) subnet with only one element, for example [\"10.6.0.0/26\"] (/28 onwards)."
  }
}

########################
## Optional variables ##
########################
variable "tags" {
  type        = map
  default     = {}
}

variable "databases" {
  description = "list of databases (names) to create"
  type        = list(string)
  default     = []
}

variable "zone_redundant" {
  description = "Whether or not the multi-az is enabled"
  type        = bool
  default     = false
}
variable "sku_name" {
  description = "sku name"
  type        = string
  default     = "GP_Gen5"
  
  validation {
    condition     = contains(["GP_Gen4", "GP_Gen5"], var.sku_name)
    error_message = "Sku name must be either \"GP_Gen4\" or \"GP_Gen5\"."
  }
}

variable "sku_tier" {
  description = "sku tier"
  type        = string
  default     = "GeneralPurpose"
  
  validation {
    condition     = contains(["GeneralPurpose", "BusinessCritical"], var.sku_tier)
    error_message = "Sku tier must be either \"GeneralPurpose\" or \"BusinessCritical\"."
  }
}

variable "license_type" {
  description = "license type"
  type        = string
  default     = "LicenseIncluded"
  
  validation {
    condition     = contains(["BasePrice", "LicenseIncluded"], var.license_type)
    error_message = "Licese type must be either \"BasePrice\" or \"LicenseIncluded\"."
  }
}

variable "number_of_cores" {
  description = "number of vCores of instance"
  type        = number
  default     = 4
  
  validation {
    condition     = alltrue([
      4 <= var.number_of_cores && var.number_of_cores <= 80,
      var.number_of_cores % 4 == 0
    ])
    error_message = "Number of cores must be between 4 and 80 (inclusive) in increments of 4."
  }
}

variable "storage_size_gb" {
  description = "storage size in GB (max size by tier and number of cores, for example on GeneralPurpose 4: 2048, 8: 8192, ..., no validation for max so careful)"
  type        = number
  default     = 128
  
  validation {
    condition     = alltrue([
      32 <= var.storage_size_gb,
      var.storage_size_gb % 32 == 0
    ])
    error_message = "Storage size GB must be at least 32 in increments of 32."
  }
}

variable "collation" {
  description = "collation"
  type        = string
  default     = "SQL_Latin1_General_CP1_CI_AS"
}

variable "time_zone_id" {
  description = "time zone"
  type        = string
  default     = "UTC"
}

variable "storage_account_type" {
  description = "storage account type"
  type        = string
  default     = "GRS"

  validation {
    condition     = contains(["GRS", "LRS", "ZRS"], var.storage_account_type)
    error_message = "Proxy override must be one of \"GRS\", \"LRS\" or \"ZRS\"."
  }
}

variable "proxy_override" {
  description = "proxyOverride"
  type        = string
  default     = "Redirect"
  
  validation {
    condition     = contains(["Redirect", "Proxy"], var.proxy_override)
    error_message = "Proxy override must be either \"Redirect\" or \"Proxy\"."
  }
}

module's output.tf

output "subnet" {
    value = azurerm_subnet.snet_sql
}

output "sql_managed_instance" {
    value = {
        name                 = azurerm_resource_group_template_deployment.sqlmanagedinstance.name
        admin_login_name     = var.administrator_login
        admin_login_password = random_password.password.result
        deployment_mode      = azurerm_resource_group_template_deployment.sqlmanagedinstance.deployment_mode  
        template_content     = azurerm_resource_group_template_deployment.sqlmanagedinstance.template_content
        id                   = jsondecode(azurerm_resource_group_template_deployment.sqlmanagedinstance.output_content).resourceID.value
        database_ids         = length(var.databases) > 0 ? { for rid in jsondecode(azurerm_resource_group_template_deployment.sqlmanagedinstance.output_content).databasesResourceIDs.value : regex("[^/]+$",rid) => rid }  : null
    }
}

[Marcus-James-Adams]

Is this completed or am i getting confused the changelog for v2.76 mentions azurerm_sql_managed_database but not that azurerm_sql_managed_instance now exists.

[juanjojulian]

Is this completed or am i getting confused the changelog for v2.76 mentions azurerm_sql_managed_database but not that azurerm_sql_managed_instance now exists.

Looks complete: https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/sql_managed_instance

[github-actions]

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 have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.