SQL Server migrations to Azure come down to one early decision: Azure SQL Database or SQL Managed Instance. SQL Database is the right default for new applications. Managed Instance exists for the estate you already have: cross database queries, SQL Agent jobs, CLR, linked servers, Service Broker, all the instance scoped features that twenty years of enterprise applications depend on. If your assessment shows those dependencies, MI gives you near full compatibility with PaaS operations, automated backups, patching, and built in HA.

Deployment Essentials

MI deploys into a delegated subnet in your VNet, private by design. Size the subnet at /27 minimum, though I recommend /26 to leave room for scaling operations. Choose the service tier deliberately: General Purpose uses remote storage and is fine for most workloads, Business Critical runs a local SSD Always On replica set and includes a free readable secondary, which matters for both latency sensitive OLTP and offloading reports.

resource "azurerm_mssql_managed_instance" "this" {
  name                         = "sqlmi-erp-prod"
  resource_group_name          = azurerm_resource_group.data.name
  location                     = "westeurope"
  subnet_id                    = azurerm_subnet.sqlmi.id
  license_type                 = "BasePrice"
  sku_name                     = "BC_Gen8IM"
  vcores                       = 8
  storage_size_in_gb           = 512
  administrator_login          = "sqladmin"
  administrator_login_password = random_password.sqlmi.result
  minimum_tls_version          = "1.2"
  proxy_override               = "Redirect"
  zone_redundant_enabled       = true
}

resource "azurerm_mssql_managed_instance_active_directory_administrator" "this" {
  managed_instance_id         = azurerm_mssql_managed_instance.this.id
  login_username              = "dba-team"
  object_id                   = azuread_group.dba.object_id
  tenant_id                   = data.azurerm_client_config.current.tenant_id
  azuread_authentication_only = true
}

Three settings there earn their place. BasePrice applies Azure Hybrid Benefit if you own SQL licenses with Software Assurance, roughly a 40 percent saving. Redirect mode lets clients talk directly to the node after login instead of through the gateway, cutting latency. And Entra only authentication removes SQL logins as an attack surface.

Migration with the Link Feature

The old migration options were backup and restore (long downtime) or transactional replication (painful setup). The Managed Instance link changed the game: it builds a distributed availability group between your on premises SQL Server and the MI, streaming changes continuously. The database stays fully usable on premises while MI catches up, and cutover is a planned failover measured in seconds. It also works in reverse for supported versions, giving you a rollback path after cutover, which is the thing every change advisory board asks about first.

The practical sequence: validate compatibility with the Data Migration Assistant, establish the link per database, let it synchronize over days if needed, run your application tests against a restored copy on MI, then fail over during a maintenance window and repoint connection strings, ideally already abstracted behind DNS.

High Availability and DR

Inside the region, Business Critical with zone redundancy spreads replicas across availability zones, surviving zone loss with automatic failover. Across regions, failover groups pair a primary MI with a secondary in another region and give you two stable listener endpoints, read write and read only, that never change during failover.

resource "azurerm_mssql_managed_instance_failover_group" "this" {
  name                        = "fog-erp"
  location                    = "westeurope"
  managed_instance_id         = azurerm_mssql_managed_instance.this.id
  partner_managed_instance_id = azurerm_mssql_managed_instance.dr.id

  read_write_endpoint_failover_policy {
    mode          = "Manual"
  }
}

I set failover policy to manual in most designs. Automatic sounds attractive until a transient network blip fails over your primary region and the application tier does not follow. Regional failover should be a human decision executed by a tested runbook. Test that runbook quarterly, verify backup restores monthly, and monitor the log replication lag between partners, because your real RPO is whatever that lag says it is.

Cheers
Osama