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]

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]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 |
USE [master]; /* Read-only pre-migration check for Azure SQL Managed Instance. Lists on-premises usage of features that the Managed Instance documentation says differ. Nothing is created or changed. Results go to a table variable and one result set. Needs SQL Server 2017 or later because it uses CONCAT_WS. */ SET NOCOUNT ON; DECLARE @findings TABLE ( [area] nvarchar(40) NOT NULL , [check_name] nvarchar(80) NOT NULL , [database_name] nvarchar(128) NULL , [item] nvarchar(400) NULL , [detail] nvarchar(400) NULL ); DECLARE @trace_flags TABLE ( [trace_flag] int NOT NULL , [status] int NOT NULL , [global] int NOT NULL , [session] int NOT NULL ); /* Server: Windows logins and groups (MI has no CREATE LOGIN ... FROM WINDOWS) */ INSERT @findings SELECT N'Logins' , N'Windows login or group' , NULL , [sp].[name] , [sp].[type_desc] FROM [sys].[server_principals] AS [sp] WHERE [sp].[type] IN ('U', 'G') AND [sp].[name] NOT LIKE N'NT SERVICE\%' AND [sp].[name] NOT LIKE N'NT AUTHORITY\%'; /* Server: sp_configure options that are unsupported or ignored on MI */ INSERT @findings SELECT N'Configuration' , N'sp_configure option in use' , NULL , [c].[name] , CONCAT_WS(N' ', N'value_in_use =', CONVERT(nvarchar(20), [c].[value_in_use])) FROM [sys].[configurations] AS [c] WHERE [c].[name] IN (N'xp_cmdshell', N'Ole Automation Procedures', N'scan for startup procs', N'filestream access level', N'allow polybase export') AND CONVERT(int, [c].[value_in_use]) <> 0; /* Server: startup procedures */ INSERT @findings SELECT N'Configuration' , N'Startup procedure' , N'master' , CONCAT_WS(N'.', SCHEMA_NAME([p].[schema_id]), [p].[name]) , NULL FROM [master].[sys].[procedures] AS [p] WHERE OBJECTPROPERTY([p].[object_id], 'ExecIsStartup') = 1; /* Server: global trace flags outside the documented MI list */ INSERT @trace_flags EXEC (N'DBCC TRACESTATUS(-1) WITH NO_INFOMSGS;'); INSERT @findings SELECT N'Configuration' , N'Trace flag not in MI supported list' , NULL , CONVERT(nvarchar(20), [tf].[trace_flag]) , CONCAT_WS(N' ', N'global =', CONVERT(nvarchar(20), [tf].[global])) FROM @trace_flags AS [tf] WHERE [tf].[trace_flag] NOT IN (460, 2301, 2389, 2390, 2453, 2467, 7471, 8207, 9389, 10316, 11024); /* Server: collation and time zone are fixed when an MI is created */ INSERT @findings SELECT N'Configuration' , N'Server collation differs from MI default' , NULL , CONVERT(nvarchar(128), SERVERPROPERTY('Collation')) , N'MI default is SQL_Latin1_General_CP1_CI_AS' WHERE CONVERT(nvarchar(128), SERVERPROPERTY('Collation')) <> N'SQL_Latin1_General_CP1_CI_AS'; /* CURRENT_TIMEZONE_ID() exists in SQL Server 2022 and later, so it is called through dynamic SQL. Older versions fall back to the current UTC offset, which reads +00:00 for a zone such as London in winter. */ DECLARE @tz_id nvarchar(128); IF CONVERT(int, SERVERPROPERTY('ProductMajorVersion')) >= 16 BEGIN EXEC [sys].[sp_executesql] N'SET @tz = CURRENT_TIMEZONE_ID();' , N'@tz nvarchar(128) OUTPUT' , @tz = @tz_id OUTPUT; END; INSERT @findings SELECT N'Configuration' , N'Server time zone is not UTC' , NULL , @tz_id , N'MI default time zone is UTC' WHERE @tz_id IS NOT NULL AND @tz_id <> N'UTC'; INSERT @findings SELECT N'Configuration' , N'Server UTC offset is not zero' , NULL , DATENAME(TZOFFSET, SYSDATETIMEOFFSET()) , N'Current offset only, so a zone on daylight saving time may not be flagged' WHERE @tz_id IS NULL AND DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) <> 0; /* Server: total user database files (a General Purpose instance allows 280, data and log combined) */ INSERT @findings SELECT N'Database files' , N'More than 280 user database files on the instance (General Purpose limit)' , NULL , CONVERT(nvarchar(20), COUNT_BIG(1)) , N'data and log files in sys.master_files' FROM [sys].[master_files] AS [mf] INNER JOIN [sys].[databases] AS [d] ON [d].[database_id] = [mf].[database_id] WHERE [d].[database_id] > 4 AND [d].[source_database_id] IS NULL HAVING COUNT_BIG(1) > 280; /* Server: linked servers (MI supports SQL Server family targets only) */ INSERT @findings SELECT N'Linked servers' , CASE WHEN [s].[provider] IN (N'SQLNCLI', N'SQLNCLI11', N'SQLOLEDB', N'MSOLEDBSQL', N'MSOLEDBSQL19') THEN N'Linked server (SQL Server provider)' ELSE N'Linked server (non-SQL Server provider)' END , NULL , [s].[name] , CONCAT_WS(N' | ', [s].[product], [s].[provider], [s].[data_source]) FROM [sys].[servers] AS [s] WHERE [s].[is_linked] = 1; /* Server: audits that write to a file or a Windows log */ INSERT @findings SELECT N'Auditing' , N'Server audit target not URL' , NULL , [a].[name] , [a].[type_desc] FROM [sys].[server_audits] AS [a] WHERE [a].[type_desc] IN (N'FILE', N'APPLICATION LOG', N'SECURITY LOG'); /* Server: user Extended Events sessions with an event_file target */ INSERT @findings SELECT N'Extended Events' , N'event_file target' , NULL , [es].[name] , NULL FROM [sys].[server_event_sessions] AS [es] INNER JOIN [sys].[server_event_session_targets] AS [est] ON [est].[event_session_id] = [es].[event_session_id] WHERE [est].[name] = N'event_file' AND [es].[name] NOT IN (N'system_health', N'AlwaysOn_health', N'telemetry_xevents'); /* Server: user-defined messages (sp_addmessage is not supported on MI) */ INSERT @findings SELECT N'Messages' , N'User-defined messages (sp_addmessage)' , NULL , CONVERT(nvarchar(20), COUNT_BIG(1)) , N'rows in sys.messages with message_id > 50000' FROM [sys].[messages] AS [m] WHERE [m].[message_id] > 50000 HAVING COUNT_BIG(1) > 0; /* Server: user extended stored procedures */ INSERT @findings SELECT N'Extended procedures' , N'User extended stored procedure' , N'master' , [ep].[name] , [ep].[dll_name] FROM [master].[sys].[extended_procedures] AS [ep] WHERE [ep].[is_ms_shipped] = 0; /* Server: credentials that are not SAS or managed identity */ INSERT @findings SELECT N'Security' , N'Credential identity type to review' , NULL , [cr].[name] , [cr].[credential_identity] FROM [sys].[credentials] AS [cr] WHERE [cr].[credential_identity] NOT IN (N'SHARED ACCESS SIGNATURE', N'Managed Identity'); /* Server: database mirroring endpoints (AG and mirroring are not user-managed on MI) */ INSERT @findings SELECT N'Availability' , N'Database mirroring endpoint' , NULL , [e].[name] , [e].[role_desc] FROM [sys].[database_mirroring_endpoints] AS [e]; /* Agent: job step types other than T-SQL, SSIS and the supported replication agents */ INSERT @findings SELECT N'Agent' , N'Job step subsystem to review' , NULL , CONCAT_WS(N' / step ', [j].[name], CONVERT(nvarchar(11), [js].[step_id])) , [js].[subsystem] FROM [msdb].[dbo].[sysjobsteps] AS [js] INNER JOIN [msdb].[dbo].[sysjobs] AS [j] ON [j].[job_id] = [js].[job_id] WHERE [js].[subsystem] NOT IN (N'TSQL', N'SSIS', N'LogReader', N'Snapshot', N'Distribution'); /* Agent: T-SQL steps that take backups (MI allows COPY_ONLY to URL only) */ INSERT @findings SELECT N'Agent' , N'Job step runs BACKUP' , [js].[database_name] , CONCAT_WS(N' / step ', [j].[name], CONVERT(nvarchar(11), [js].[step_id])) , LEFT(REPLACE(REPLACE([js].[command], NCHAR(13), N' '), NCHAR(10), N' '), 200) FROM [msdb].[dbo].[sysjobsteps] AS [js] INNER JOIN [msdb].[dbo].[sysjobs] AS [j] ON [j].[job_id] = [js].[job_id] WHERE [js].[subsystem] = N'TSQL' AND [js].[command] LIKE N'%BACKUP%'; /* Agent: proxies, alerts, and pager or net send operators */ INSERT @findings SELECT N'Agent' , N'Agent proxy' , NULL , [px].[name] , NULL FROM [msdb].[dbo].[sysproxies] AS [px]; INSERT @findings SELECT N'Agent' , N'Agent alert' , NULL , [al].[name] , NULL FROM [msdb].[dbo].[sysalerts] AS [al]; INSERT @findings SELECT N'Agent' , N'Operator with pager or net send' , NULL , [o].[name] , NULL FROM [msdb].[dbo].[sysoperators] AS [o] WHERE COALESCE([o].[pager_address], N'') <> N'' OR COALESCE([o].[netsend_address], N'') <> N''; /* tempdb: user objects in model are not copied to tempdb on MI */ INSERT @findings SELECT N'tempdb' , N'User object in model' , N'model' , CONCAT_WS(N'.', SCHEMA_NAME([o].[schema_id]), [o].[name]) , [o].[type_desc] FROM [model].[sys].[objects] AS [o] WHERE [o].[is_ms_shipped] = 0 AND [o].[parent_object_id] = 0; /* Database settings that a restore to MI overrides or that MI rejects */ INSERT @findings SELECT N'Database options' , [x].[check_name] , [d].[name] , NULL , [x].[detail] FROM [sys].[databases] AS [d] CROSS APPLY ( SELECT N'Compatibility level below 100' , CONVERT(nvarchar(400), [d].[compatibility_level]) WHERE [d].[compatibility_level] < 100 UNION ALL SELECT N'Recovery model becomes FULL on restore' , [d].[recovery_model_desc] WHERE [d].[recovery_model_desc] <> N'FULL' UNION ALL SELECT N'AUTO_CLOSE becomes OFF on restore' , N'is_auto_close_on = 1' WHERE [d].[is_auto_close_on] = 1 UNION ALL SELECT N'Database collation differs from server' , [d].[collation_name] WHERE [d].[collation_name] <> CONVERT(nvarchar(128), SERVERPROPERTY('Collation')) UNION ALL SELECT N'Database snapshot' , DB_NAME([d].[source_database_id]) WHERE [d].[source_database_id] IS NOT NULL ) AS [x] ([check_name], [detail]) WHERE [d].[database_id] > 4; /* Per-database checks, run through dynamic SQL with three-part names. The cross-database check keeps only references to user databases that exist on this instance, because sys.sql_expression_dependencies also reports XML method calls such as x.n.value() as three-part names. */ DECLARE @template nvarchar(max); DECLARE @sql nvarchar(max); DECLARE @db_name sysname; DECLARE @rows int; DECLARE @cur CURSOR; SET @template = N' SELECT N''Database files'', N''More than one log file'', @db, NULL, CONVERT(nvarchar(20), COUNT_BIG(1)) COLLATE DATABASE_DEFAULT FROM {db}.[sys].[database_files] AS [f] WHERE [f].[type] = 1 HAVING COUNT_BIG(1) > 1 UNION ALL SELECT N''Database files'', N''FILESTREAM filegroup'', @db, [ds].[name] COLLATE DATABASE_DEFAULT, [ds].[type_desc] COLLATE DATABASE_DEFAULT FROM {db}.[sys].[data_spaces] AS [ds] WHERE [ds].[type] = ''FD'' UNION ALL SELECT N''Database files'', N''Memory-optimized filegroup (not on General Purpose)'', @db, [ds].[name] COLLATE DATABASE_DEFAULT, [ds].[type_desc] COLLATE DATABASE_DEFAULT FROM {db}.[sys].[data_spaces] AS [ds] WHERE [ds].[type] = ''FX'' UNION ALL SELECT N''Database files'', N''FileTable'', @db, CONCAT_WS(N''.'', [s].[name], [t].[name]) COLLATE DATABASE_DEFAULT, NULL FROM {db}.[sys].[filetables] AS [ft] INNER JOIN {db}.[sys].[tables] AS [t] ON [t].[object_id] = [ft].[object_id] INNER JOIN {db}.[sys].[schemas] AS [s] ON [s].[schema_id] = [t].[schema_id] UNION ALL SELECT N''CLR'', N''User assembly'', @db, [a].[name] COLLATE DATABASE_DEFAULT, [a].[permission_set_desc] COLLATE DATABASE_DEFAULT FROM {db}.[sys].[assemblies] AS [a] WHERE [a].[is_user_defined] = 1 UNION ALL SELECT N''Service Broker'', N''Route address other than LOCAL'', @db, [r].[name] COLLATE DATABASE_DEFAULT, [r].[address] COLLATE DATABASE_DEFAULT FROM {db}.[sys].[routes] AS [r] WHERE UPPER([r].[address]) <> N''LOCAL'' UNION ALL SELECT N''Service Broker'', N''Remote service binding'', @db, [rsb].[name] COLLATE DATABASE_DEFAULT, [rsb].[remote_service_name] COLLATE DATABASE_DEFAULT FROM {db}.[sys].[remote_service_bindings] AS [rsb] UNION ALL SELECT N''Cross-database'', N''Modules referencing another database'', @db, [ref].[referenced_database_name] COLLATE DATABASE_DEFAULT, CONVERT(nvarchar(20), COUNT_BIG(1)) COLLATE DATABASE_DEFAULT FROM {db}.[sys].[sql_expression_dependencies] AS [ref] WHERE [ref].[referenced_database_name] IS NOT NULL AND [ref].[referenced_database_name] <> @db AND [ref].[referenced_server_name] IS NULL AND DB_ID([ref].[referenced_database_name]) > 4 GROUP BY [ref].[referenced_database_name] UNION ALL SELECT N''Linked servers'', N''Modules referencing a linked server'', @db, [ref].[referenced_server_name] COLLATE DATABASE_DEFAULT, CONVERT(nvarchar(20), COUNT_BIG(1)) COLLATE DATABASE_DEFAULT FROM {db}.[sys].[sql_expression_dependencies] AS [ref] WHERE [ref].[referenced_server_name] IS NOT NULL GROUP BY [ref].[referenced_server_name];'; SET @cur = CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT [d].[name] FROM [sys].[databases] AS [d] WHERE [d].[database_id] > 4 AND [d].[state] = 0 AND [d].[source_database_id] IS NULL AND HAS_DBACCESS([d].[name]) = 1 ORDER BY [d].[name]; OPEN @cur; SET @rows = @@CURSOR_ROWS; WHILE @rows > 0 BEGIN FETCH NEXT FROM @cur INTO @db_name; SET @sql = REPLACE(@template, N'{db}', QUOTENAME(@db_name)); INSERT @findings EXEC [sys].[sp_executesql] @sql , N'@db sysname' , @db = @db_name; SET @rows = @rows - 1; END; CLOSE @cur; DEALLOCATE @cur; SELECT [f].[area] , [f].[check_name] , [f].[database_name] , [f].[item] , [f].[detail] FROM @findings AS [f] ORDER BY [f].[area] , [f].[check_name] , [f].[database_name] , [f].[item]; |
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
CMDEXECsteps, because they callsqllogship.exe. filestream access levelwas on for both instances even though no database had aFILESTREAMfilegroup. It’s on the unsupported list above, but on these two servers nothing uses it.xp_cmdshellwas 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
- 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. ↩
- 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. ↩
- 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. ↩
- 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. ↩
- 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. ↩
- 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. ↩
- 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. ↩
- 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. ↩
- T-SQL differences: tempdb - Microsoft Learn. The fixed 12-file layout, General Purpose size limits, model objects, and memory-optimized tempdb metadata. ↩
- T-SQL differences: msdb - Microsoft Learn. The msdb schemas that must stay owned by predefined roles, and that changes are reverted. ↩
- T-SQL differences: Linked servers - Microsoft Learn. The supported and unsupported linked server targets. ↩
- T-SQL differences: CLR - Microsoft Learn. Only CREATE ASSEMBLY FROM BINARY is supported, because MI can’t access file shares and Windows folders. ↩
- 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. ↩
- 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. ↩
- T-SQL differences: Distributed transactions - Microsoft Learn. T-SQL and .NET based distributed transactions across managed instances are generally available. ↩
- 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. ↩
- 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. ↩
- 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. ↩
- 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. ↩
- 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. ↩
- CONCAT_WS (Transact-SQL) - Microsoft Learn. CONCAT_WS applies to SQL Server 2017 (14.x) and later versions. ↩
- 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. ↩
- 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. ↩
- 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. ↩