Azure SQL Managed Instance Differences That Catch DBAs During a Migration

If you lift and shift to Azure SQL Managed Instance (MI), your log backup job fails on its first run, because MI takes its own backups. It takes a full backup every week, a differential every 12 or 24 hours, and a log backup about every 10 minutes.[1] You can take your own backups, but only COPY_ONLY full backups to URL.[2]

A DBA checks crates moving from a server room to a cloud building, setting aside a few oddly shaped crates that do not fit through the doorway.

That’s one of many differences between MI and the SQL Server you run yourself. Most of them are listed on one Microsoft Learn page, “T-SQL differences between SQL Server and Azure SQL Managed Instance.” This post goes through the ones I’d expect to cause trouble in a migration, then gives a read-only query that finds where an on-premises instance uses them.

I don’t have a Managed Instance to test against. Every statement below about how MI behaves comes from Microsoft Learn, and I read each page on October 30, 2026. The only thing I tested is the check query, which I ran against two local instances. The docs change often, so check the current pages before you plan around anything here.

My earlier post Azure SQL Feasibility Check with AI compared Azure SQL Database with Managed Instance to help pick a target. This post assumes you’ve already picked MI.

Backups and Restores

A user backup on MI must be a full COPY_ONLY backup to URL in Azure Blob Storage. Differential and log backups aren’t supported, and neither are FILE, TAPE or backup devices. The log options NORECOVERY, STANDBY and NO_TRUNCATE aren’t supported either.[2] Any Agent job, maintenance procedure or third-party tool that writes .bak or .trn files to a disk path won’t run as written.

RESTORE reads only FROM URL, and the page says restores that use WITH options such as STATS, REPLACE or DIFFERENTIAL will fail. The restore also overrides some settings in the backup. A database in SIMPLE or BULK_LOGGED recovery becomes FULL, AUTO_CLOSE becomes OFF, and Service Broker is turned on if it was off. A backup that contains more than one log file, or any FILESTREAM or FILETABLE objects, can’t be restored at all.[3]

Files and Database Options

Multiple log files aren’t supported, and a database can’t contain a FILESTREAM filegroup. A General Purpose instance allows 280 files in total, counting the data and log files of every database on it.[4]

Scripts that set file locations need changes, because MI places every database file itself. ALTER DATABASE ... ADD FILE can’t take a FILENAME path, and a file name can’t be changed. Several database options can’t be set at all, including SINGLE_USER, RESTRICTED_USER, OFFLINE, READ_ONLY, EMERGENCY and PAGE_VERIFY.[5] If a maintenance or deployment script puts a database in single-user mode first, that script needs a different approach. Because single-user mode isn’t allowed, DBCC CHECKDB can’t run with any REPAIR option, and the page says to contact Azure support about suspected corruption.[6]

SQL Server Agent

Agent is always running, and its settings are read-only. T-SQL and SSIS job steps are supported, and the T-SQL differences page says command shell steps aren’t yet supported. Alerts, proxies, pager and net send notifications aren’t supported. Email notification works only through a Database Mail profile named AzureManagedInstance_dbmail_profile.[7]

The Learn page about Agent jobs on MI doesn’t agree about operating system steps. It lists “OS command/PowerShell steps” as a supported step type. Its limits section says running a script file from disk, importing modules such as dbatools, and PowerShell Core aren’t supported.[8] I can’t test which page is current. In the April post I wrote that CmdExec steps work on MI without file system access. I’d now treat CmdExec and PowerShell steps as unconfirmed until you’ve run one on your own instance.

tempdb and msdb

tempdb always has 12 data files, and you can’t add files or change the layout. On General Purpose, tempdb can’t grow past 24 GB per vCore, and its log is capped at 120 GB. Objects you create in model aren’t copied into tempdb after a restart or failover. Memory-optimized tempdb metadata isn’t supported.[9]

In msdb, the Agent, Database Mail and SSIS schemas must stay owned by their predefined roles. The page says changes to those owners are reverted.[10]

Linked Servers, CLR and Service Broker

Linked servers on MI can point at another Managed Instance, Azure SQL Database, Azure Synapse SQL or SQL Server. Files, Analysis Services and other database products aren’t supported targets.[11] A linked server to Oracle or to an Excel file needs a new design.

CLR assemblies must be created FROM BINARY. CREATE ASSEMBLY FROM FILE isn’t supported, and ALTER ASSEMBLY can’t reference files.[12] The page doesn’t mention permission sets. MI can’t reach file shares or Windows folders, though, so I’d review any EXTERNAL_ACCESS or UNSAFE assembly to see what it reaches outside the database.

Service Broker can exchange messages across instances only between Managed Instances. A route address must be LOCAL or the DNS name of another MI, on port 4022. Transport security is supported, but dialog security and CREATE REMOTE SERVICE BINDING aren’t.[13]

Three-part names reach only databases on the same instance. Queries to another instance go through linked servers.[14] T-SQL and .NET distributed transactions across Managed Instances are generally available.[15] I’d move databases that reference each other to the same MI together, or rewrite those references to use linked servers.

Logins, Collation, Time Zone and Trace Flags

CREATE LOGIN ... FROM WINDOWS isn’t supported. Microsoft Entra logins and users replace Windows logins.[16] SQL logins are still supported, including FROM SID, so the approach in Migrating Logins Without Losing Them still applies to them.

The server collation on MI defaults to SQL_Latin1_General_CP1_CI_AS. You can set it when you create the instance, and you can’t change it later.[17] The time zone works the same way. It defaults to UTC, and you can set it only at creation. GETDATE() and Agent schedules follow it, but external processes started by Agent jobs don’t.[18] If your server runs on local time and you accept the default, code that calls GETDATE() gets UTC, and every job schedule moves by the UTC offset.

Only 11 global trace flags are supported on MI, and session trace flags aren’t supported.[19] xp_cmdshell, extended stored procedures and sp_addmessage aren’t supported. Neither are the sp_configure options scan for startup procs and filestream_access_level.[20]

A Read-Only Check Query

The query below looks for on-premises use of the features above. It reads catalog views and msdb tables and changes nothing. It collects findings in a table variable and returns one result set. Per-database checks run through dynamic SQL with three-part names, so it doesn’t need USE for each database. It uses CONCAT_WS, so it needs SQL Server 2017 or later.[21]

I ran it as sysadmin. With less permission, some catalog views, such as sys.server_principals, sys.credentials and sys.servers, return only the rows you’re allowed to see. You’d get a shorter list and no error. The Agent tables in msdb aren’t covered by that rule, so the login also needs read access to them.[22]

Two parts of the query are text matches and can report false positives. The BACKUP check matches any T-SQL job step whose text contains the word, and on my instances that included a step that only reads backup history. The cross-database check needed a filter. sys.sql_expression_dependencies reports XML method calls such as x.n.value() as three-part names. Before I added the filter, it listed “databases” named c, n and x from XML-shredding procedures in three databases. The filter keeps only user databases that exist on the instance, so it misses references to a database that isn’t on this instance.

The time zone check depends on the version. On SQL Server 2022 and later, it compares CURRENT_TIMEZONE_ID() with UTC.[23] On older versions, it reads only the current UTC offset. A server in a zone that observes daylight saving time, such as London, is at +00:00 in winter and may not be flagged.

The query also checks a few items I haven’t covered above. In-memory objects aren’t supported on General Purpose, so it lists memory-optimized filegroups.[4] MI writes audits only to Azure Blob storage, and file and Windows log targets aren’t supported, so it lists audits with those targets.[24] MI ignores the Ole Automation Procedures option, so the query reports it when it’s on.[20]

What It Found on Two Local Instances

I ran it on SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer), both on one workstation. These are test instances, not production servers. Several rows, including all of the linked servers and both cross-database pairs, came from sat_ scratch objects that other tests created.

Check SQL Server 2019 SQL Server 2025
Windows logins and groups 35 2
Agent alerts 26 18
T-SQL job steps containing BACKUP 15 9
Job steps with another subsystem 3 3
sp_configure options in use 2 1
Time zone (2019: UTC offset, 2025: time zone ID) -05:00 Manitoba Standard Time
Databases not in FULL recovery 7 6
Databases with a collation other than the server’s 1 1
Cross-database references (database pairs) 2 2
Linked servers 0 4
Credentials to review 1 1

Both servers use SQL_Latin1_General_CP1_CI_AS, which matches the MI default, so the server collation check returned nothing. Neither had trace flags on, startup procedures, user-defined messages, file-based audits or user Extended Events sessions with a file target. No database had more than one log file, a FILESTREAM filegroup, a FileTable, a CLR assembly or a Service Broker route other than LOCAL. The user databases had 42 files in total on the 2019 instance and 38 on the 2025 instance, under the 280 limit.

Four results surprised me.

  • Both instances flagged syspolicy_purge_history, a policy-based management job I didn’t create, because step 3 is a PowerShell step.
  • Log shipping jobs showed up as CMDEXEC steps, because they call sqllogship.exe.
  • filestream access level was on for both instances even though no database had a FILESTREAM filegroup. It’s on the unsupported list above, but on these two servers nothing uses it.
  • xp_cmdshell was on for the 2019 instance. I’d search the code for callers before estimating how much work that is.

What This Doesn’t Cover

The query reads metadata. It can’t see dynamic SQL built at run time, application code, SSIS packages or scripts that live outside the server. It doesn’t compare sizes against MI resource limits or service tiers. Microsoft’s assessment tools, which I mentioned in the earlier post, look at more than this does. I use this query for a quick first list, and it isn’t a replacement for those tools.

Related: Migration Runbook Rollback Points for SQL Server Upgrades (available 2026-11-19).

If you’ve moved a workload to Managed Instance and something on this list caught you, or something that isn’t on it did, I’d like to hear about it. Find me on Bluesky or LinkedIn.

References

  1. Automated backups in Azure SQL Managed Instance - Microsoft Learn. MI creates full backups every week, differential backups every 12 or 24 hours, and log backups about every 10 minutes. ↩
  2. T-SQL differences between SQL Server and Azure SQL Managed Instance: Backup - Microsoft Learn. User backups must be full COPY_ONLY backups to URL. Differential, log, FILE, TAPE, backup devices and the log-specific options aren’t supported. ↩
  3. T-SQL differences: RESTORE statement - Microsoft Learn. FROM URL is the only source, WITH options fail, recovery model and other options are overridden, and backups with multiple log files or FILESTREAM objects can’t be restored. ↩
  4. T-SQL differences: Database options - Microsoft Learn. Multiple log files and FILESTREAM filegroups aren’t supported, in-memory objects aren’t supported in the General Purpose tier, and a General Purpose instance allows 280 files, data and log combined. ↩
  5. T-SQL differences: ALTER DATABASE statement - Microsoft Learn. FILENAME can’t be specified in ADD FILE, file names can’t be changed, and the list of options that can’t be modified. ↩
  6. T-SQL differences: DBCC - Microsoft Learn. DBCC CHECKDB repair options can’t be used because a database can’t be set to SINGLE_USER, and corruption is handled by Azure support. ↩
  7. T-SQL differences: SQL Server Agent - Microsoft Learn. Supported and unsupported job step types, including “Command shell isn’t yet supported,” and the unsupported alerts, proxies and notifications. ↩
  8. Automate management tasks using SQL Agent jobs in Azure SQL Managed Instance - Microsoft Learn, updated July 7, 2026. Lists OS command/PowerShell steps as a job step type, and lists the PowerShell limits. ↩
  9. T-SQL differences: tempdb - Microsoft Learn. The fixed 12-file layout, General Purpose size limits, model objects, and memory-optimized tempdb metadata. ↩
  10. T-SQL differences: msdb - Microsoft Learn. The msdb schemas that must stay owned by predefined roles, and that changes are reverted. ↩
  11. T-SQL differences: Linked servers - Microsoft Learn. The supported and unsupported linked server targets. ↩
  12. T-SQL differences: CLR - Microsoft Learn. Only CREATE ASSEMBLY FROM BINARY is supported, because MI can’t access file shares and Windows folders. ↩
  13. T-SQL differences: Service Broker - Microsoft Learn. Cross-instance messaging only between Managed Instances, route address limits, transport security supported, and dialog security and remote service bindings not supported. ↩
  14. Compare SQL Database engine features: Azure SQL Database and Azure SQL Managed Instance - Microsoft Learn. Three-part name queries are supported on MI, cross-database transactions work within the instance, and cross-instance queries use linked servers. ↩
  15. T-SQL differences: Distributed transactions - Microsoft Learn. T-SQL and .NET based distributed transactions across managed instances are generally available. ↩
  16. T-SQL differences: Logins and users - Microsoft Learn. Windows logins aren’t supported, Microsoft Entra logins are, and SQL logins can be created FROM SID. ↩
  17. Set or change the server collation - Microsoft Learn. MI server collation is set at creation, can’t be changed later, and defaults to SQL_Latin1_General_CP1_CI_AS. ↩
  18. Time zones in Azure SQL Managed Instance - Microsoft Learn. The time zone defaults to UTC, can be set only at creation, GETDATE() and Agent schedules follow it, and external processes launched from Agent jobs don’t. ↩
  19. Set trace flags with DBCC TRACEON - Microsoft Learn. Lists the 11 global trace flags MI supports, and says session trace flags aren’t yet supported. ↩
  20. T-SQL differences: Stored procedures, functions, and triggers - Microsoft Learn. The unsupported sp_configure options, the ignored Ole Automation Procedures option, xp_cmdshell, extended stored procedures and sp_addmessage. ↩
  21. CONCAT_WS (Transact-SQL) - Microsoft Learn. CONCAT_WS applies to SQL Server 2017 (14.x) and later versions. ↩
  22. Metadata visibility configuration - Microsoft Learn. Catalog views show only securables the user owns or has a permission on, and may return a subset of rows. The rule doesn’t apply to SQL Server Agent system tables. ↩
  23. CURRENT_TIMEZONE_ID (Transact-SQL) - Microsoft Learn. Returns the ID of the time zone the server or instance observes, and applies to SQL Server 2022 (16.x) and later versions. ↩
  24. T-SQL differences: Auditing - Microsoft Learn. MI audit files are stored in Azure Blob storage, file and Windows log targets aren’t supported, and CREATE SERVER AUDIT TO FILE isn’t supported. ↩