Terraform Module: Azure SQL Managed Instance — Lift-and-Shift SQL Server with Guardrails Baked In

Quick take — A production-ready Terraform module for azurerm_mssql_managed_instance on azurerm ~> 4.0: delegated subnet wiring, BYOK TDE, AAD-only auth, configurable vCores/storage, and the connection outputs your apps need. New here? Jump to the Quickstart below to deploy it in minutes; read on for how it works and when to reach for it.

Quickstart (copy-paste)

Minimal, runnable configuration — drop this in a .tf file and fill in the "..." placeholders (each required input is commented):

provider "azurerm" {
  features {}
}

module "sql_managed_instance" {
  source = "git::https://dev.azure.com/teknohut/kloudvin/_git/terraform-modules//terraform-module-azure-sql-managed-instance?ref=v1.0.0"

  name                         = "..."  # Instance name (1-63 lowercase chars/hyphens). Conventio…
  resource_group_name          = "..."  # Resource group that contains the instance.
  location                     = "..."  # Azure region (e.g. `centralindia`).
  subnet_id                    = "..."  # Delegated subnet ID (`Microsoft.Sql/managedInstances`);…
  administrator_login          = "..."  # SQL admin login (required even with AAD-only).
  administrator_login_password = "..."  # SQL admin password; source from Key Vault.
}

Then terraform init && terraform apply. Every other input has a sensible default — see Inputs below to override behaviour.

What this module is

Azure SQL Managed Instance (MI) is the near-100% SQL Server surface area PaaS: you get SQL Agent, cross-database queries, the CLR, Service Broker, linked servers, and the full sqlcmd/T-SQL feature set that plain Azure SQL Database leaves out — but Microsoft runs the patching, backups, and HA for you. The trade-off is that an MI is not a lightweight resource. It always lands inside a delegated subnet in your VNet (a single instance reserves the subnet), it can take hours to provision or scale, and its surface — vCores, storage, TDE key, AAD admin, maintenance window — is a tangle of fields that each have their own rules.

In azurerm ~> 4.0 this is the azurerm_mssql_managed_instance resource. The footguns are real: the subnet_id must point at a subnet delegated to Microsoft.Sql/managedInstances with the right route table and NSG, or creation fails after a long wait; storage_size_in_gb only moves in 32 GB increments; the sku_name (GP_Gen5, BC_Gen5, …) ties the tier to a vCore range; and switching to customer-managed-key TDE or Azure-AD-only authentication is easy to get subtly wrong. Every one of those is a slow, expensive mistake to discover at apply time.

Wrapping MI in a module gives every team one vetted way to stand up an instance: a governed SKU/vCore allow-list enforced by variable validation, the storage_size_in_gb-divisible-by-32 rule caught at plan time, an optional Azure AD administrator and AAD-only lockdown, optional BYOK TDE via Key Vault, a sane maintenance window, and clean id / fqdn outputs that every app connection string and private DNS record consumes. Nobody hand-rolls a broken subnet delegation into a four-hour failed deploy again.

When to use it

• You’re migrating SQL Server (on-prem or IaaS VMs) and need engine features Azure SQL Database can’t offer — SQL Agent jobs, cross-DB queries, CLR, Service Broker, or linked servers.
• You want the instance to live inside your VNet with private connectivity only, reachable from app subnets and over ExpressRoute/VPN, never the public internet.
• You need a governed tier: a SKU and vCore allow-list so teams can’t accidentally stand up a BC_Gen5 Business Critical instance (4× the price) for a dev workload.
• You require compliance-grade encryption — customer-managed TDE keys in Key Vault — and Azure AD-only authentication with no SQL logins.
• You manage many environments and want the instance defined once, parameterised by vCores/storage/SKU, and reused via a pinned Git tag with consistent naming and tagging.

Reach for azurerm_mssql_server + azurerm_mssql_database (single Azure SQL Database or an Elastic Pool) instead when you only need a managed database, don’t need instance-scoped features, and want per-second-billing serverless or far cheaper, faster-provisioning DTU/vCore single databases.

Module structure

terraform-module-azure-sql-managed-instance/
├── versions.tf      # provider + Terraform version pins
├── main.tf          # managed instance + AAD admin + TDE key + maintenance
├── variables.tf     # var-driven inputs with validations
└── outputs.tf       # id, name, fqdn, identity principal

versions.tf

terraform {
  required_version = ">= 1.5.0"

  required_providers {
    azurerm = {
      source  = "hashicorp/azurerm"
      version = "~> 4.0"
    }
  }
}

main.tf

resource "azurerm_mssql_managed_instance" "this" {
  name                = var.name
  resource_group_name = var.resource_group_name
  location            = var.location

  # The instance lives in a delegated subnet (Microsoft.Sql/managedInstances).
  # A single MI consumes the whole subnet; size it /27 or larger.
  subnet_id = var.subnet_id

  # SKU fixes the tier; vcores + storage size the instance within it.
  sku_name           = var.sku_name
  vcores             = var.vcores
  storage_size_in_gb = var.storage_size_in_gb

  # General Purpose -> "LocallyRedundant"/"ZoneRedundant"; Business Critical
  # adds geo options. ZoneRedundant requires a zone-capable SKU + region.
  storage_account_type = var.storage_account_type

  # SQL admin login. Required even when you lock down to AAD-only auth, but
  # AAD-only then blocks its use at the data plane.
  administrator_login          = var.administrator_login
  administrator_login_password = var.administrator_login_password

  # SQL or BasePrice (BasePrice = bring-your-own SQL Server license / AHB).
  license_type = var.license_type

  # Backup retention for point-in-time restore (1-35 days).
  proxy_override               = var.proxy_override
  public_data_endpoint_enabled = var.public_data_endpoint_enabled
  timezone_id                  = var.timezone_id
  minimum_tls_version          = var.minimum_tls_version
  maintenance_configuration_name = var.maintenance_configuration_name

  # System-assigned identity is needed for customer-managed-key TDE and for
  # the AAD administrator block to read Graph.
  identity {
    type = "SystemAssigned"
  }

  tags = var.tags

  lifecycle {
    precondition {
      condition     = var.storage_size_in_gb % 32 == 0
      error_message = "storage_size_in_gb must be a multiple of 32 (Managed Instance allocates storage in 32 GB increments)."
    }

    precondition {
      condition     = var.aad_admin == null ? true : (var.administrator_login != null && var.administrator_login_password != null)
      error_message = "administrator_login and administrator_login_password are required even when an AAD administrator is configured."
    }
  }
}

# Azure AD administrator + optional AAD-only authentication lockdown.
resource "azurerm_mssql_managed_instance_active_directory_administrator" "this" {
  count = var.aad_admin == null ? 0 : 1

  managed_instance_id         = azurerm_mssql_managed_instance.this.id
  login_username              = var.aad_admin.login_username
  object_id                   = var.aad_admin.object_id
  tenant_id                   = var.aad_admin.tenant_id
  azuread_authentication_only = var.aad_admin.azuread_authentication_only
}

# Customer-managed-key (BYOK) Transparent Data Encryption.
# Grant the instance identity get/wrapKey/unwrapKey on the Key Vault key first.
resource "azurerm_mssql_managed_instance_transparent_data_encryption" "this" {
  count = var.tde_key_vault_key_id == null ? 0 : 1

  managed_instance_id = azurerm_mssql_managed_instance.this.id
  key_vault_key_id    = var.tde_key_vault_key_id
  auto_rotation_enabled = var.tde_auto_rotation_enabled
}

variables.tf

variable "name" {
  description = "Name of the Managed Instance (globally unique; forms <name>.<dns_zone>.database.windows.net). Convention: sqlmi-<workload>-<env>-<region>."
  type        = string

  validation {
    condition     = can(regex("^[a-z0-9-]{1,63}$", var.name)) && !can(regex("(^-|-$)", var.name))
    error_message = "name must be 1-63 chars: lowercase letters, numbers, and hyphens, not starting or ending with a hyphen."
  }
}

variable "resource_group_name" {
  description = "Name of the resource group that will contain the instance."
  type        = string
}

variable "location" {
  description = "Azure region for the instance (e.g. centralindia, westeurope)."
  type        = string
}

variable "subnet_id" {
  description = "Resource ID of a subnet delegated to Microsoft.Sql/managedInstances. A single MI consumes the entire subnet; use /27 or larger with the required route table and NSG."
  type        = string
}

variable "sku_name" {
  description = "Managed Instance SKU. Governs the service tier and hardware generation. Immutable tier change forces replacement."
  type        = string
  default     = "GP_Gen5"

  validation {
    # Governed allow-list: General Purpose and Business Critical, Gen5/Gen8 hardware.
    condition = contains([
      "GP_Gen5", "GP_Gen8IM", "GP_Gen8IH",
      "BC_Gen5", "BC_Gen8IM", "BC_Gen8IH"
    ], var.sku_name)
    error_message = "sku_name must be a supported GP_/BC_ Gen5 or Gen8 SKU."
  }
}

variable "vcores" {
  description = "Number of vCores. Valid values depend on the SKU (Gen5 GP/BC support 4, 8, 16, 24, 32, 40, 48, 56, 64, 80)."
  type        = number
  default     = 4

  validation {
    condition     = contains([4, 8, 16, 24, 32, 40, 48, 56, 64, 80, 96, 128], var.vcores)
    error_message = "vcores must be one of the supported values: 4, 8, 16, 24, 32, 40, 48, 56, 64, 80, 96, 128."
  }
}

variable "storage_size_in_gb" {
  description = "Maximum storage in GB. Allocated in 32 GB increments; range depends on tier (GP: 32-16384, BC: 32-16384 with vCore-dependent caps)."
  type        = number
  default     = 32

  validation {
    condition     = var.storage_size_in_gb >= 32 && var.storage_size_in_gb <= 16384
    error_message = "storage_size_in_gb must be between 32 and 16384."
  }
}

variable "storage_account_type" {
  description = "Backup storage redundancy: LocallyRedundant, ZoneRedundant, GeoRedundant, or GeoZoneRedundant."
  type        = string
  default     = "ZoneRedundant"

  validation {
    condition     = contains(["LocallyRedundant", "ZoneRedundant", "GeoRedundant", "GeoZoneRedundant"], var.storage_account_type)
    error_message = "storage_account_type must be one of: LocallyRedundant, ZoneRedundant, GeoRedundant, GeoZoneRedundant."
  }
}

variable "administrator_login" {
  description = "SQL administrator login name. Required at creation even when AAD-only auth is enabled."
  type        = string
}

variable "administrator_login_password" {
  description = "SQL administrator password. Source from Key Vault; never commit. Required at creation even with AAD-only auth."
  type        = string
  sensitive   = true
}

variable "license_type" {
  description = "Licensing model: LicenseIncluded (pay for SQL) or BasePrice (Azure Hybrid Benefit / bring your own license)."
  type        = string
  default     = "LicenseIncluded"

  validation {
    condition     = contains(["LicenseIncluded", "BasePrice"], var.license_type)
    error_message = "license_type must be LicenseIncluded or BasePrice."
  }
}

variable "proxy_override" {
  description = "Connection type: Proxy, Redirect, or Default. Redirect lowers latency for in-VNet clients; Proxy is required across some peerings/firewalls."
  type        = string
  default     = "Proxy"

  validation {
    condition     = contains(["Proxy", "Redirect", "Default"], var.proxy_override)
    error_message = "proxy_override must be one of: Proxy, Redirect, Default."
  }
}

variable "public_data_endpoint_enabled" {
  description = "Expose the instance on a public data endpoint (port 3342). Keep false for VNet-only access."
  type        = bool
  default     = false
}

variable "minimum_tls_version" {
  description = "Minimum TLS version accepted by the instance."
  type        = string
  default     = "1.2"

  validation {
    condition     = contains(["1.0", "1.1", "1.2"], var.minimum_tls_version)
    error_message = "minimum_tls_version must be one of: 1.0, 1.1, 1.2."
  }
}

variable "timezone_id" {
  description = "Windows timezone of the instance (immutable). e.g. 'UTC', 'India Standard Time', 'GMT Standard Time'."
  type        = string
  default     = "UTC"
}

variable "maintenance_configuration_name" {
  description = "Maintenance window configuration name (e.g. SQL_Default, SQL_CentralIndia_MI_1 for a weekday-evening window)."
  type        = string
  default     = "SQL_Default"
}

variable "aad_admin" {
  description = "Optional Azure AD administrator. Set azuread_authentication_only = true to disable SQL logins entirely."
  type = object({
    login_username              = string
    object_id                   = string
    tenant_id                   = string
    azuread_authentication_only = optional(bool, false)
  })
  default = null
}

variable "tde_key_vault_key_id" {
  description = "Optional Key Vault key ID for customer-managed-key (BYOK) TDE. Null uses the service-managed key. Grant the instance identity get/wrapKey/unwrapKey first."
  type        = string
  default     = null
}

variable "tde_auto_rotation_enabled" {
  description = "Auto-rotate the customer-managed TDE key when a new key version is created in Key Vault. Ignored when tde_key_vault_key_id is null."
  type        = bool
  default     = true
}

variable "tags" {
  description = "Tags applied to the Managed Instance."
  type        = map(string)
  default     = {}
}

outputs.tf

output "id" {
  description = "Resource ID of the Managed Instance. Pass to databases, failover groups, and DNS records."
  value       = azurerm_mssql_managed_instance.this.id
}

output "name" {
  description = "Name of the Managed Instance."
  value       = azurerm_mssql_managed_instance.this.name
}

output "fqdn" {
  description = "Fully qualified host name of the instance (<name>.<dns_zone>.database.windows.net) for connection strings and DNS records."
  value       = azurerm_mssql_managed_instance.this.fqdn
}

output "dns_zone" {
  description = "DNS zone partition of the instance, shared by instances in the same VNet for cross-instance connectivity."
  value       = azurerm_mssql_managed_instance.this.dns_zone
}

output "identity_principal_id" {
  description = "Object (principal) ID of the instance's system-assigned identity. Use to grant Key Vault and Graph permissions."
  value       = try(azurerm_mssql_managed_instance.this.identity[0].principal_id, null)
}

How to use it

Provision a General Purpose instance with Azure-AD-only authentication and BYOK TDE, then point an app connection string at its FQDN:

module "sql_managed_instance" {
  source = "git::https://dev.azure.com/teknohut/kloudvin/_git/terraform-modules//terraform-module-azure-sql-managed-instance?ref=v1.0.0"

  name                = "sqlmi-kloudvin-prod-cin"
  resource_group_name = azurerm_resource_group.data.name
  location            = azurerm_resource_group.data.location

  # Subnet must be delegated to Microsoft.Sql/managedInstances.
  subnet_id = azurerm_subnet.sqlmi.id

  sku_name             = "GP_Gen5"
  vcores               = 8
  storage_size_in_gb   = 256
  storage_account_type = "ZoneRedundant"
  license_type         = "BasePrice" # Azure Hybrid Benefit

  administrator_login          = "sqladmin"
  administrator_login_password = data.azurerm_key_vault_secret.sqlmi_admin.value

  # Lock down to Azure AD only — no SQL logins at the data plane.
  aad_admin = {
    login_username              = "sql-admins"
    object_id                   = azuread_group.sql_admins.object_id
    tenant_id                   = data.azurerm_client_config.current.tenant_id
    azuread_authentication_only = true
  }

  # Customer-managed-key TDE (identity must already have wrap/unwrap on the key).
  tde_key_vault_key_id      = azurerm_key_vault_key.tde.id
  tde_auto_rotation_enabled = true

  proxy_override                 = "Redirect"
  minimum_tls_version            = "1.2"
  timezone_id                    = "India Standard Time"
  maintenance_configuration_name = "SQL_CentralIndia_MI_1"

  tags = {
    environment = "prod"
    workload    = "kloudvin-orders"
    managed_by  = "terraform"
  }
}

# Downstream consumer: an app setting built from the instance FQDN output.
resource "azurerm_linux_web_app" "orders_api" {
  name                = "app-orders-prod-cin"
  resource_group_name = azurerm_resource_group.data.name
  location            = azurerm_resource_group.data.location
  service_plan_id     = azurerm_service_plan.api.id

  site_config {}

  app_settings = {
    "ConnectionStrings__Orders" = "Server=tcp:${module.sql_managed_instance.fqdn},1433;Database=Orders;Authentication=Active Directory Default;Encrypt=True;"
  }
}

With Terragrunt

Terragrunt keeps this module DRY across environments — define the backend and provider once in a root config, then a thin terragrunt.hcl per environment supplies only the inputs that differ.

1. Root config — live/terragrunt.hcl (inherited by every module):

remote_state {
  backend = "azurerm"
  generate = { path = "backend.tf", if_exists = "overwrite" }
  config = {
    # ...azurerm state bucket/container + key per path...
  }
}

2. Module config — live/prod/sql_managed_instance/terragrunt.hcl:

include "root" {
  path = find_in_parent_folders()
}

terraform {
  source = "git::https://dev.azure.com/teknohut/kloudvin/_git/terraform-modules//terraform-module-azure-sql-managed-instance?ref=v1.0.0"
}

inputs = {
  name = "..."
  resource_group_name = "..."
  location = "..."
  subnet_id = "..."
  administrator_login = "..."
  administrator_login_password = "..."
}

3. Deploy one environment, or roll out all modules together:

cd live/prod/sql_managed_instance && terragrunt apply        # this module
terragrunt run-all apply                      # every module under live/prod

Why Terragrunt here: the backend and provider live in one place instead of being copy-pasted into every module; inputs is overridden per environment (dev / stage / prod) without forking the module; and run-all orchestrates dependencies across modules. Reach for it once you have more than one environment or more than a handful of modules — for a single stack, the plain Quickstart above is enough.

Inputs

Outputs

Enterprise scenario

A bank is retiring a rack of on-prem SQL Server 2016 boxes that run nightly SQL Agent ETL jobs and cross-database stored procedures the application can’t be rewritten around. The platform team uses this module to stand up a GP_Gen5 instance with vcores = 16, storage_account_type = "GeoZoneRedundant", license_type = "BasePrice" (Azure Hybrid Benefit reuses their existing Software Assurance licenses), Azure-AD-only authentication mapped to a sql-admins group, and customer-managed-key TDE rooted in a HSM-backed Key Vault for the regulator. Because the module validates storage_size_in_gb % 32 and the SKU allow-list in CI, an engineer who later requests storage_size_in_gb = 500 for a new environment gets a failure at terraform plan instead of a multi-hour failed deploy and a support ticket.

Best practices

• Size the delegated subnet once, correctly. An MI consumes its entire subnet and the subnet’s address range can’t be resized after the instance lands; provision a dedicated /27 (or larger to allow a second instance later), delegate it to Microsoft.Sql/managedInstances, and attach the required route table and NSG before the module runs.
• Lock down to Azure AD-only auth and keep the public endpoint off. Set aad_admin.azuread_authentication_only = true and leave public_data_endpoint_enabled = false so the instance is reachable only inside the VNet, with no standing SQL logins for an attacker to brute-force.
• Use BYOK TDE with auto-rotation for regulated data. Point tde_key_vault_key_id at an HSM-backed Key Vault key, grant the instance’s identity_principal_id get/wrapKey/unwrapKey, and enable tde_auto_rotation_enabled so key rotation is hands-off and auditable.
• Right-size the tier and ride Azure Hybrid Benefit. Default to GP_Gen5 and reserve BC_Gen5 (which roughly quadruples the price for local SSD + read replicas) for genuine low-latency/HA workloads; set license_type = "BasePrice" to apply existing SQL Server licenses, and stack a 1- or 3-year reservation on steady production vCores for 30-55% off.
• Pick the right connection type and TLS floor. Use proxy_override = "Redirect" for in-VNet clients to cut latency (it needs NSG rules for ports 11000-11999), fall back to Proxy across restrictive peerings, and pin minimum_tls_version = "1.2".
• Standardise naming, tagging, and maintenance windows. Enforce sqlmi-<workload>-<env>-<region>, require environment/managed_by tags for cost and policy slicing, and set an explicit maintenance_configuration_name so Microsoft patches land in a known off-hours window rather than at random.