Building an Azure SQL Managed Instance Link to an on-premises SQL Server for a true DR design

Here’s a high-level breakdown of the activities and steps involved in building an Azure SQL Managed Instance Link to an on-premises SQL Server for a true DR design.

Phase 1: Planning & Prerequisites

1. Validate version and licensing requirements

  • Confirm on-premises SQL Server is a supported version. One-way replication from SQL Server to Azure SQL Managed Instance is generally available for every supported SQL Server version, but disaster recovery with two-way replication and failback is supported starting with SQL Server 2022. Microsoft Learn
  • For true bidirectional DR with failback, on-premises must be SQL Server 2022 (or 2025), and the SQL Managed Instance must be configured with the matching update policy (e.g., the SQL Server 2022 update policy).
  • If the SQL MI will only act as a passive DR replica, evaluate the Hybrid Failover Benefit to reduce licensing costs.

2. Provision the Azure SQL Managed Instance

  • Deploy SQL MI in the appropriate region, subnet, and service tier.
  • Set the correct update policy (SQL Server 2022 update policy is required if you want to fail back to SQL Server 2022).
  • Size for memory, storage, and any In-Memory OLTP requirements that match the source.

3. Confirm permissions

  • Sysadmin rights on on-premises SQL Server.
  • SQL Managed Instance Contributor role (or equivalent custom role) in Azure.

Phase 2: Environment Preparation

4. Network connectivity (the most common blocker)

  • Establish bidirectional connectivity between on-premises SQL Server and the SQL MI subnet via ExpressRoute, Site-to-Site VPN, or equivalent.
  • Open the required ports (typically TCP 5022 for the database mirroring/AG endpoint, plus 1433 for SQL traffic).
  • Configure the NSG on the SQL MI subnet to allow inbound traffic from on-premises.
  • Allow-list the SQL MI FQDN and Azure Resource Manager endpoints if egress is restricted.
  • Test connectivity in both directions using SSMS or T-SQL before proceeding.

5. Prepare the on-premises SQL Server instance

  • Enable the Always On Availability Groups feature in SQL Server Configuration Manager and restart the SQL Server service.
  • For SQL Server 2016, complete the additional WSFC-related prerequisite steps.
  • Enable recommended trace flags at startup (e.g., T1800 for differing log sector sizes, T9567 for compression during automatic seeding).
  • Ensure databases to be replicated are in FULL recovery model and have at least one full backup taken.

6. Handle encryption (TDE) if applicable

  • If the source database is TDE-encrypted, migrate the TDE certificate to SQL MI before establishing the link.
  • Note that databases encrypted with service-managed TDE keys on SQL MI cannot be linked back to SQL Server — customer-managed keys (Azure Key Vault) are required for that direction.

7. Install tooling

  • Install SQL Server Management Studio (SSMS) v19.2 or later on the admin workstation, since this is the primary tool used to drive the link wizard.

Phase 3: Link Creation

8. Decide the initial primary

  • For migration scenarios: on-premises is the initial primary.
  • For new workloads in Azure with on-premises as DR target: SQL MI can be the initial primary (requires SQL Server 2022 CU10+ on the secondary).

9. Create the link using the SSMS wizard (recommended path)

  • In SSMS, right-click the source database → Azure SQL Managed Instance link → New.
  • Provide a link name, enable connectivity troubleshooting, and tick the two-way disaster recovery option (critical for failback scenarios).
  • Run the wizard’s automated requirements validation and resolve any failures.
  • Select the database(s) to replicate (one distributed AG is created per database).
  • Add the secondary replica (SQL MI) by signing into Azure and selecting the target instance.
  • Optionally choose Script instead of Finish to capture the T-SQL for repeatable, version-controlled deployments.

10. Behind the scenes, the wizard configures

  • A local Always On Availability Group on SQL Server (if one doesn’t exist).
  • Database mirroring endpoints with certificate-based authentication.
  • A distributed availability group spanning SQL Server and SQL MI — this is the underlying technology that carries the replication.
  • Automatic seeding of the database to SQL MI.

Phase 4: Validation & Operations

11. Verify replication health

  • Confirm the database appears as a synchronized read-only copy on SQL MI.
  • Monitor the AG and DAG health using DMVs (sys.dm_hadr_database_replica_states, sys.availability_groups, sys.distributed_availability_groups).
  • Validate latency under realistic transaction load.

12. Document and rehearse failover procedures

  • Planned failover (e.g., for migration or maintenance): performed gracefully with no data loss.
  • Forced failover (true DR event): may involve data loss depending on replication lag.
  • Failback to SQL Server 2022: can be done online (via the link itself) or offline (backup from SQL MI and restore to SQL Server 2022).
  • Run regular DR drills — a DR design that hasn’t been tested isn’t really a DR design.

Phase 5: Ongoing Management

13. Monitor and maintain

  • Set up alerts on replication lag and AG health.
  • Track Azure costs, particularly egress during failback scenarios.
  • Keep cumulative updates aligned — don’t downgrade SQL Server below CU10 while a link with SQL MI as primary is active.
  • Review the link periodically against the latest Microsoft guidance, since the feature continues to evolve (e.g., SQL Server 2025 added support).

Quick architectural summary: SQL Server (Always On AG) ⇄ Distributed Availability Group ⇄ SQL Managed Instance (internal AG). The DAG is the bridge — and getting bidirectional network connectivity, the right SQL Server build, and the matching SQL MI update policy in place is what makes or breaks the implementation.