Azure SQL Managed Instance Link: Near-Zero Downtime Migration Runbook

The Managed Instance link migrates a SQL Server database to Azure SQL Managed Instance with near-zero downtime by creating a distributed availability group that seeds the database to Azure and then replicates log changes in near real time while the source stays online. When you're ready, you stop the application, wait for replication lag to reach zero, run a planned failover from SQL Server Management Studio, and repoint connection strings to the managed instance. Microsoft describes it as the only truly online migration option to the Business Critical tier.

Who this is for and what you will have at the end

This runbook is for database administrators moving SQL Server 2016 through 2025 databases to Azure SQL Managed Instance who want the cutover window to be minutes rather than the duration of a backup and restore. It covers the single-database link mode, where each link replicates one database.

At the end you will have:

• SQL Server and the network prepared for the link.
• A database seeded to the managed instance and replicating continuously, usable for read-only testing.
• Server-level objects re-created on the target.
• A rehearsed cutover sequence with lag checks, planned failover and application repointing.

How the link works and what it doesn't do

The link uses distributed availability groups. SQL Server is the primary, the managed instance is a read-only secondary, and the two authenticate with certificates rather than Windows authentication. Initial seeding takes a full backup of the database on SQL Server, transfers it, and restores it on the managed instance; after that, only log changes flow.

Version determines what happens at cutover:

Limits to plan around:

• Only user databases replicate. Logins, Agent jobs and other server-level objects don't.
• A database with multiple log files can't be replicated, because SQL Managed Instance doesn't support multiple log files. FileTable and FILESTREAM aren't supported either.
• In-Memory OLTP databases can only go to the Business Critical tier.
• TDE-protected databases need their encryption key exported to Azure Key Vault and customer-managed TDE configured on the managed instance before you create the link.
• A managed instance in a failover group can't use the link, and vice versa.
• The link uses the VNet-local endpoint only; public and private endpoints can't carry it.
• General Purpose and Business Critical instances support up to 100 databases (Next-gen General Purpose up to 500), shared between linked and existing databases.

If the source is SQL Server 2016 and you're also deciding whether to buy Extended Security Updates, see SQL Server 2016 end of support options.

Prerequisites

• A supported SQL Server version with the servicing update in the table above, on Windows Server (Windows 10 and 11 can't host the link because availability groups can't be enabled there).
• sysadmin on SQL Server.
• On Azure, the SQL Managed Instance Contributor role, or a custom role with the Microsoft.Sql/managedInstances hybrid link, distributed availability group and certificate permissions Microsoft lists.
• A target managed instance sized to match the source in CPU, memory and I/O, so it can keep up with replication and with the workload after cutover.
• Matching collation between SQL Server and the managed instance.
• The latest SQL Server Management Studio.
• Site-to-site VPN or ExpressRoute from on-premises (ExpressRoute is recommended for throughput), or VNet peering when SQL Server runs on an Azure VM.

Step 1: Prepare SQL Server

Run these checks and changes on the source instance. They need one service restart.

Version, master key and availability groups

-- Version and CU
SELECT @@VERSION AS [SQL Server version];
 
-- Database master key in master (create one if missing)
USE master;
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%DatabaseMasterKey%';
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';
 
-- Is the availability groups feature enabled?
SELECT SERVERPROPERTY('IsHadrEnabled') AS [Is Always On enabled? (1 true, 0 false)];

Enable availability groups in SQL Server Configuration Manager: open the SQL Server service Properties, go to the Always On Availability Groups tab and select Enable Always On Availability Groups.

SQL Server 2016 can't enable availability groups without a Windows Server Failover Cluster. A single-node cluster is enough:

Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools
New-Cluster -Name "WSFCluster" -AdministrativeAccessPoint None -Verbose -Force

That PowerShell method is meant for machines not joined to a domain and can't be managed from Failover Cluster Manager; use Failover Cluster Manager to create the cluster if you need full configuration options. Microsoft's SQL Server 2016 preparation article also covers the permissions SQL Server needs on the cluster.

Startup trace flags

Add -T1800 and -T9567 as startup parameters on the Startup Parameters tab of the service properties. Trace flag 1800 helps when the primary and secondary log disks have different sector sizes; 9567 compresses the seeding stream, which costs CPU but can cut transfer time significantly. Restart the service, then confirm:

SELECT SERVERPROPERTY('IsHadrEnabled') AS hadr_enabled;
DBCC TRACESTATUS;

Database settings that can't be fixed later

On SQL Server 2019 and later, enable accelerated database recovery and leave the persistent version store on the PRIMARY filegroup. If ADR is off at migration, you can't turn it on after the database lands on the managed instance, and a non-PRIMARY version store can cause restore failures on the target. Do the same check for Service Broker if you'll use it in Azure.

ALTER DATABASE [SalesDb] SET ACCELERATED_DATABASE_RECOVERY = ON;
 
SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'SalesDb';
-- ALTER DATABASE [SalesDb] SET ENABLE_BROKER;

Step 2: Open the network

Ports on the managed instance side can't be changed. The IP ranges of the SQL Server network and the managed instance subnet must not overlap. On the Windows host:

New-NetFirewallRule -DisplayName "Allow TCP port 5022 inbound" -Direction inbound -Profile Any -Action Allow -LocalPort 5022 -Protocol TCP
New-NetFirewallRule -DisplayName "Allow TCP port 5022 outbound" -Direction outbound -Profile Any -Action Allow -LocalPort 5022 -Protocol TCP

Corporate firewalls along the path need the same openings, and should allow ICMP (to avoid false negatives in testing) and the SQL Server UCS protocol (blocking it can make the connection test pass while link creation fails).

Test connectivity in SSMS: right-click the database, select Tasks > Azure SQL Managed Instance link > Test Connection, and run the Network Checker wizard. It creates and removes a temporary SQL Agent job on each side, so SQL Server Agent must be running.

Step 3: Re-create server-level objects on the managed instance

Because the link doesn't replicate master or msdb, script these from SQL Server and run them on the managed instance before cutover:

• Logins used by the application and by database users.
• SQL Agent jobs, operators and alerts.
• Linked servers, credentials and server-level permissions.
• Anything CDC, log shipping or Service Broker depends on; these need manual reconfiguration after the move.

If the database publishes transactional replication, plan to reconfigure it as a publisher on the managed instance after migration.

Step 4: Create the link and seed

1. Take a full backup of the database on SQL Server with checksum. If the database has no current backup chain (error 1475), take a full backup without COPY_ONLY.
2. On supported builds, enable trace flag 12381 before seeding to prevent premature log truncation. Log backups can continue, but retained log can fill the disk, so monitor log space and disable the flag once seeding finishes. On builds without it, pause log backups during seeding.
3. In SSMS Object Explorer, right-click the database and select Azure SQL Managed Instance link > New....
4. Name the link, pass the Requirements page, and select the database.
5. On Specify Secondary Replica, select Add secondary replica, sign in to Azure, choose the subscription, resource group and managed instance, and connect to it.
6. Review the endpoint settings, pass Validation, optionally select Script on the Summary page to save the configuration, and select Finish.

Seeding time depends on database size and throughput. Microsoft's example: a 100 GB database takes about 1.2 hours at 84 GB per hour, but about 10 hours at 10 GB per hour. Seeding restarts if connectivity drops or either side restarts or fails over, and link creation is canceled automatically after 6 days.

Track seeding on either replica:

SELECT ag.local_database_name, ar.current_state, ag.internal_state_desc,
       ag.database_size_bytes / 1024 / 1024 AS database_size_mb,
       ag.transferred_size_bytes / 1024 / 1024 AS transferred_mb,
       ag.transfer_rate_bytes_per_second / 1024 / 1024 AS transfer_rate_mb_s
FROM sys.dm_hadr_physical_seeding_stats AS ag
JOIN sys.dm_hadr_automatic_seeding AS ar
  ON local_physical_seeding_id = operation_id;

When seeding completes, the database shows as Synchronized in Object Explorer, and you can run read-only tests against it on the managed instance. Take the first transaction log backup on SQL Server (or resume paused backups), disable trace flag 12381, and keep log backups running on schedule; the primary can't truncate log records until they've been replicated.

Step 5: Cut over

Do this in the maintenance window. For several databases on the same instance, Microsoft recommends failing over 8 databases per instance at a time.

1. Stop the workload. The simplest way is to cut application connections to SQL Server.
2. Check replication lag on both replicas until it reaches zero and the link reports healthy:

USE master;
DECLARE @link_name varchar(max) = '<DAGname>';
SELECT ag.name AS [Link name],
       ars1.role_desc AS [Link role],
       ars2.connected_state_desc AS [Link connected state],
       ars2.synchronization_health_desc AS [Link sync health],
       drs.secondary_lag_seconds AS [Link replication latency (seconds)]
FROM sys.availability_groups ag
JOIN sys.dm_hadr_availability_replica_states ars1 ON ag.group_id = ars1.group_id
JOIN sys.dm_hadr_availability_replica_states ars2 ON ag.group_id = ars2.group_id
JOIN sys.dm_hadr_database_replica_states drs ON ars2.replica_id = drs.replica_id
WHERE ag.is_distributed = 1 AND ag.name = @link_name
  AND ars1.is_local = 1 AND ars2.is_local = 0;

3. Fail over. In SSMS, right-click the database, select Azure SQL Managed Instance link > Failover..., choose Planned failover, sign in to Azure and the remote instance, and on Post-Failover Operations choose to remove the link. For SQL Server 2016 to 2019 the link is always removed. On SQL Server 2022 CU13 and later you can instead run ALTER AVAILABILITY GROUP [<DAGname>] FAILOVER on the primary. If you use PowerShell, first switch the distributed availability group to synchronous commit on SQL Server, as the failover article describes.
4. Repoint applications to the managed instance FQDN, for example sqlmi-contoso.a1b2c3d4e5f6.database.windows.net. The failover doesn't change connection strings for you.
5. Keep the old database but stop using it. After a removed link, the source and target are independent read/write databases, so make sure nothing writes to the old copy.

If you kept the link (SQL Server 2022 or 2025 only), don't remove it until Azure finishes the first full backup of the database on the managed instance; removing it earlier can make the database temporarily unavailable after a server restart.

Verification

• Check that the database is online and read/write on the managed instance and that row counts or checksums for key tables match your pre-cutover snapshot.
• Sign in with application logins and confirm users map correctly.
• Run the Agent jobs you re-created and check their history.
• Confirm automated backups are running; the managed instance takes full and log backups automatically (differentials aren't taken while a link exists).
• Monitor performance after cutover and fix regressions before you decommission the source.

Troubleshooting

Error 41962: "Operation aborted because the link wasn't initiated within 5 minutes." Usually network. Recheck ports 5022 and 11000-11999 in both directions and every firewall on the path.

Errors 41973 or 41974 (endpoint certificate not imported correctly). Certificate exchange failed on the managed instance or SQL Server side. Clean up the partial distributed availability group and availability group, then rerun the wizard.

Error 41986: "connection failed or the secondary replica isn't responsive." Check names, configuration parameters and network connectivity. Also confirm the collation matches on both sides; Microsoft notes a mismatch can cause server name casing differences that stop SQL Server connecting to the managed instance.

Errors 1408 or 1412 during seeding. The log was truncated before seeding finished, usually by a log backup. Drop and re-create the link with trace flag 12381 enabled (or log backups paused) during seeding.

Planned failover times out. The secondary is lagging. Stop the workload, wait for lag to clear, add network bandwidth or managed instance capacity if it persists, then retry.

The wizard stops partway. It can't be resumed. Remove any distributed availability group and availability group it created, fix the cause and start again.

For the wider plan of moving application tiers alongside the database, see the enterprise Azure cloud migration playbook.

Runbook checklist

• Source version and servicing level confirmed; Azure Connect pack installed for 2016 and 2017.
• Master key created, availability groups enabled (single-node WSFC on 2016), trace flags 1800 and 9567 set.
• ADR and persistent version store checked on 2019 and later; Service Broker state matches the target's needs.
• Ports 5022 and 11000-11999 open on every firewall; Network Checker passes.
• Managed instance sized and collation matched; TDE keys in Key Vault if needed.
• Logins, jobs and server objects scripted and applied to the target.
• Link created, seeding completed, trace flag 12381 disabled, log backups running.
• Cutover rehearsed: stop workload, lag zero, planned failover, repoint applications, verify.

References

• Overview of the Managed Instance link
• Prepare your environment for a link
• Prepare SQL Server 2016 prerequisites for the link
• Configure link with SSMS
• Migrate with the link
• Fail over link
• Troubleshoot link
• Managed Instance link best practices