{"id":106713,"date":"2025-05-13T03:56:26","date_gmt":"2025-05-13T03:56:26","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=106713"},"modified":"2026-05-21T15:25:31","modified_gmt":"2026-05-21T15:25:31","slug":"sql-mi-configuration-gotchas","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/sql-mi-configuration-gotchas\/","title":{"rendered":"A complete guide to Azure SQL Managed Instance server and database configuration options"},"content":{"rendered":"\n

Azure SQL Managed Instance is largely maintained by Microsoft, which means a lot of server- and database-level configuration options you’re used to on-premises either behave differently, or aren’t available at all. Hardware settings, local file access, high-availability options, and certain auditing controls are configured by Azure, or disabled outright – and the official documentation doesn’t always match what actually works.<\/strong> <\/p>\n\n\n\n

This article is a tested reference of everything not<\/em> supported in Azure SQL Managed Instance.<\/strong><\/p>\n\n\n\n

In a previous article<\/a>, I described issues that might stop your migration to a SQL Server Managed Instance (SQL MI). This covers configuration items that differ or are not supported in SQL MI. These likely won\u2019t stop your migration, but they could slow you down if you aren\u2019t ready for these changes.<\/p>\n\n\n\n

As with previous issues discussed, testing your migration is key. Validate all of your settings and be prepared to make some changes during your migration process. Most of the incompatible options make sense when you think about the purpose of SQL MI \u2013 it is controlled by Microsoft. Hardware settings, local file access, high-availability settings, and auditing are configured differently or completely disabled.<\/p>\n\n\n\n

Server Configuration<\/h2>\n\n\n\n

Since SQL MI is largely maintained by Microsoft, some server level configuration items are not available. I ran into a few of these during our migration. I did a quick test to find others that don\u2019t support user changes. As a reminder, don\u2019t change the default settings unless you have a specific reason. If you make any changes from the defaults, they need to be tested. It is very possible to hurt performance if you change the default settings.<\/p>\n\n\n\n

SP_CONFIGURE<\/h3>\n\n\n\n

Many server settings are controlled with sp_configure. As with an on-prem instance, advanced options still need to be enabled in SQL MI. Run the following commands to see and enable configuration of all compatible server options.<\/p>\n\n\n\n

EXEC sp_configure 'SHOW ADVANCED OPTIONS', 1\nGO\nRECONFIGURE\nGO<\/pre><\/div>\n\n\n\n
The following options throw errors when trying to configure them on SQL MI. Some of the error messages differ, but regardless of the exact reason, they are not configurable on SQL MI. Some of the options that failed are listed in the documentation as compatible with SQL MI, which emphasizes the need for testing.<\/p>\n\n\n\n
It\u2019s interesting to note that the failed option list expanded by one during my validation and testing over a one-week period. The option, \u201cmax UCS send boxcars\u201d was not present during my first test. It showed up during final validation before publishing, so expect further changes and differences to compatible options in the future.<\/p>\n\n\n\n
OptionName<\/p><\/td>
ErrorMessage<\/p><\/td><\/tr>
affinity I\/O mask<\/p><\/td>
Changes to server configuration option affinity I\/O mask are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
affinity mask<\/p><\/td>
Changes to server configuration option ‘affinity mask’ are not supported in this edition of SQL Server.<\/p><\/td><\/tr>
affinity64 I\/O mask<\/p><\/td>
Changes to server configuration option affinity64 I\/O mask are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
affinity64 mask<\/p><\/td>
Changes to server configuration option ‘affinity64 mask’ are not supported in this edition of SQL Server.<\/p><\/td><\/tr>
Agent XPs<\/p><\/td>
Changes to server configuration option ‘Agent XPs’ are not supported in this edition of SQL Server.<\/p><\/td><\/tr>
allow polybase export<\/p><\/td>
Changes to server configuration option ‘allow polybase export’ are not supported in this edition of SQL Server.<\/p><\/td><\/tr>
allow updates<\/p><\/td>
Changes to server configuration option ‘allow updates’ are not supported in this edition of SQL Server.<\/p><\/td><\/tr>
automatic soft-NUMA disabled<\/p><\/td>
Changes to server configuration option automatic soft-NUMA disabled are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
c2 audit mode<\/p><\/td>
Changes to server configuration option c2 audit mode are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
column encryption enclave type<\/p><\/td>
Changes to server configuration option column encryption enclave type are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
common criteria compliance enabled<\/p><\/td>
Changes to server configuration option common criteria compliance enabled are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
external xtp dll gen util enabled<\/p><\/td>
Changes to server configuration option ‘external xtp dll gen util enabled’ are not supported in this edition of SQL Server.<\/p><\/td><\/tr>
filestream access level<\/p><\/td>
Changes to server configuration option ‘filestream access level’ are not supported in this edition of SQL Server.<\/p><\/td><\/tr>
fill factor (%)<\/p><\/td>
Changes to server configuration option fill factor (%) are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
hardware offload config<\/p><\/td>
Changes to server configuration option hardware offload config are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
hardware offload enabled<\/p><\/td>
Changes to server configuration option hardware offload enabled are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
hardware offload mode<\/p><\/td>
Changes to server configuration option hardware offload mode are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
lightweight pooling<\/p><\/td>
Changes to server configuration option lightweight pooling are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
locks<\/p><\/td>
Changes to server configuration option locks are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
max server memory (MB)<\/p><\/td>
Changes to server configuration option ‘max server memory (MB)’ are not supported in this edition of SQL Server.<\/p><\/td><\/tr>
max UCS send boxcars<\/p><\/td>
Changes to server configuration option max UCS send boxcars are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
min server memory (MB)<\/p><\/td>
Changes to server configuration option ‘min server memory (MB)’ are not supported in this edition of SQL Server.<\/p><\/td><\/tr>
open objects<\/p><\/td>
Changes to server configuration option open objects are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
priority boost<\/p><\/td>
Changes to server configuration option priority boost are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
remote access<\/p><\/td>
Changes to server configuration option remote access are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
remote data archive<\/p><\/td>
Changes to server configuration option ‘remote data archive’ are not supported in this edition of SQL Server.<\/p><\/td><\/tr>
remote proc trans<\/p><\/td>
Changes to server configuration option ‘remote proc trans’ are not supported in this edition of SQL Server.<\/p><\/td><\/tr>
scan for startup procs<\/p><\/td>
Changes to server configuration option scan for startup procs are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
set working set size<\/p><\/td>
Changes to server configuration option set working set size are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
tempdb metadata memory-optimized<\/p><\/td>
Changes to server configuration option tempdb metadata memory-optimized are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr>
user connections<\/p><\/td>
Changes to server configuration option user connections are not supported in SQL Database Managed Instances.<\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
I used the following script to validate each option. It attempts to set the value of each option to the currently configured value so no actual changes are made \u2013 it just attempts to access each option.<\/p>\n\n\n\n
SET NOCOUNT ON\nGO\n\nDROP TABLE IF EXISTS #InvalidOptions\n\nDECLARE @OPTIONS TABLE (\nRowNumber\t\tint\t\tidentity\tPRIMARY KEY CLUSTERED\n,OptionName\t\tvarchar(255)\n,Minimum\t\tint\n,Maximum\t\tint\n,ConfigValue\tint\n,RunValue\t\tint\n)\n\nINSERT INTO @OPTIONS (\nOptionName\n,Minimum\n,Maximum\n,ConfigValue\n,RunValue\n)\nEXEC sp_configure\n\nCREATE TABLE #InvalidOptions (\nOptionName\t\tvarchar(255)\tprimary key clustered\n,ErrorMessage\tnvarchar(max)\n)\n\n--SELECT *\n--FROM @OPTIONS\n\nDECLARE\n@SQL\t\t\t\tvarchar(max)\n,@OptionName\t\tvarchar(255)\n,@ConfigValue\t\tint\n,@DropTable\t\t\tbit\t\t\t\t= 1\n,@Debug\t\t\t\tbit\t\t\t\t= 0\n\nDECLARE crsOptions CURSOR\nFOR\nSELECT OptionName\n,ConfigValue\nFROM @OPTIONS\nORDER BY RowNumber\n\nOPEN crsOptions\nFETCH NEXT FROM crsOptions INTO @OptionName, @ConfigValue\n\nWHILE @@FETCH_STATUS = 0\nBEGIN\nPRINT @OptionName\nSELECT @SQL = '\nBEGIN TRY\nEXEC sp_configure ' + '''' + @OptionName + '''' + ',' + CONVERT(varchar(255),@ConfigValue) + '\nEND TRY\nBEGIN CATCH\nINSERT INTO #InvalidOptions (\nOptionName\n,ErrorMessage\n)\nVALUES (' + '''' + @OptionName + '''' + ',ERROR_MESSAGE())\n\nPRINT ' + '''' + 'Error configuring ' + @OptionName + '''' + '\nPRINT ERROR_MESSAGE()\nEND CATCH\n'\nIF @Debug = 1\nBEGIN\nPRINT @SQL\nEND\nELSE\nBEGIN\nEXEC(@SQL)\nEND\n\nFETCH NEXT FROM crsOptions INTO @OptionName, @ConfigValue\nEND\n\n\nCLOSE crsOptions\nDEALLOCATE crsOptions\n\nSELECT * FROM #InvalidOptions\n\nIF @DropTable = 1\nBEGIN\nDROP TABLE #InvalidOptions\nEND<\/pre><\/div>\n\n\n\n
Server Configuration<\/h3>\n\n\n\n
Additional options can be set on SQL Server using ALTER SERVER CONFIGURATION.<\/p>\n\n\n\n
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED ON;\nGO<\/pre><\/div>\n\n\n\n
Documentation only lists SQL Server, not SQL MI for this command. I tested a few options to be sure and the results were as expected. Since these options largely deal with physical configurations, such as CPU affinity, they wouldn\u2019t make sense in SQL MI.<\/p>\n\n\n\n
Msg 41906, Level 16, State 12, Line 8\nStatement 'ALTER SERVER CONFIGURATION SET PROCESS AFFINITY' is not supported in SQL Database Managed Instance.<\/pre><\/div>\n\n\n\n
Tempdb<\/h3>\n\n\n\n
Tempdb options are very limited. The drive locations and number of files are controlled by Microsoft. In my, somewhat limited, testing it has not been an issue. Best practices are followed regarding the number of files and I expect any tempdb configuration to be updated as recommendations change over time.<\/p>\n\n\n\n
I mentioned in a previous post that you can\u2019t change the server configuration option, ‘tempdb metadata memory-optimized’, and that was also shown above. After testing and talking to our Microsoft technical contact, this is not an issue. The database engine has been updated to incorporate the option. In short, the latest version of the engine is more efficient. Tempdb metadata operations don\u2019t stress the engine as in previous versions. I verified this using OStress (part of the RML tools) and was unable to cause latch waits by creating and dropping temp tables.<\/p>\n\n\n\n
I used 100 threads for the stress test. Each thread created and dropped 10,000 temp tables. The entire process took about 45 minutes to finish and didn\u2019t create a noticeable load on the server. That includes CPU, latch waits, and any adverse impact to other queries. The same test with an on-prem system, not configured with the tempdb metadata option, created noticeable load and adversely impacted the system. The load in SQL MI was similar to an on-prem system configured to use tempdb data in memory.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Fast, reliable and consistent SQL Server development…<\/h3>\n                
\n                                            …with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                                            Learn more & try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
Database Scoped Configuration<\/h2>\n\n\n\n
Database scoped configurations are set using the ALTER DATABASE SCOPED CONFIGURATION command.<\/p>\n\n\n\n
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0<\/pre><\/div>\n\n\n\n
I used a script similar to the previous server script to verify database scoped configurations. All options were allowed. As with server configurations, leave these at the default unless you have a specific reason to change them and test any changes you do make. Current settings can be viewed with the following DMV. Configurations are specific to each database.<\/p>\n\n\n\n
SELECT *\nFROM sys.database_scoped_configurations<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n

 <\/p>\n\n\n\n
Database Options<\/h2>\n\n\n\n
Database options are set with the ALTER DATABASE command.<\/p>\n\n\n\n
ALTER DATABASE WideWorldImporters\nSET AUTO_CREATE_STATISTICS ON<\/pre><\/div>\n\n\n\n
Like database scoped configurations, database options are set at a database level. Each database can have different settings based on the application need. Refer to the Microsoft documentation on supported options and test any that you use in your environment. Many of the options are not supported in SQL MI. There are too many to list, but the highlights follow. Note that these are listed as <option spec> (categories) and usually cover multiple options. Some option specs that are supported in SQL MI also have individual options not supported (e.g., AUTO_CLOSE). The following option specs are not supported in SQL MI:<\/p>\n\n\n\n
<accelerated_database_recovery>\n<automatic_tuning_option>\n<containment_option>\n<database_mirroring_option>\n<date_correlation_optimization_option>\n<db_state_option>\n<db_update_option>\n<db_user_access_option>\n<external_access_option>\nFILESTREAM ( <FILESTREAM_option> )\n<HADR_options>\n<mixed_page_allocation_option>\n<recovery_option>\n<remote_data_archive_option>\n<persistent_log_buffer_option>\n<service_broker_option>\n<suspend_for_snapshot_backup>\n<data_retention_policy><\/pre><\/div>\n\n\n\n
This is a side-by-side comparison of the documentation for SQL Server and SQL MI. You can see the large number of unsupported option specs.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Summary<\/h2>\n\n\n\n
Check any server and database configurations during your SQL MI testing. There are many server and database level options that aren\u2019t supported. If you have automated processes to restore or setup environments, be sure to account for the new changes. You\u2019ll also to be ready to update options over time, as my testing revealed ongoing changes.<\/p>\n\n\n\n
References<\/h2>\n\n\n
\n