SQL Server 2022 Compatibility Level and ArcGIS Enterprise

[Frank_SA]

We are migrating from our old SQL Server 2016 to a new SQL Server 2022, with plans to upgrade ArcGIS Enterprise from 10.9.1 to either 11.1 or 11.3.

As part of the migration, we noticed that the database on SQL Server 2022 is currently set to the SQL Server 2016 (130) compatibility level.

I am not very familiar with SQL Server, so I have a few questions:

1. How does the database compatibility level setting influence how SQL Server operates, particularly in relation to our ArcGIS Enterprise geodatabase?

2. If we change the database compatibility level to SQL Server 2019, will ArcMap users (who are not ready to switch to ArcGIS Pro yet) still be able to connect to and work with the enterprise geodatabase on SQL Server 2022?

Thanks in advance for any guidance.

[ShannonShields]

Short answer: it won't matter in this case what you set it to. It is very, very rare that something in ArcGIS would hinge on the database compatibility level. However, technically speaking ArcMap is not supported with SQL Server 2022 (regardless of the compatibility level). We won't prevent you from connecting, but it is an unsupported configuration.

Longer, more detailed explanation of compat levels: The SQL Server compatibility level is a property of the database that basically controls which SQL Server features or TSQL syntax is available. It is really there so that older SQL Server applications, that may use deprecated features or syntax, can connect to databases on newer versions of SQL Server. For example, many years ago the where clause join syntax was deprecated in favor of ANSI join syntax. At a specific release that syntax was no longer available and could not be used....unless the database compatibility level was set to an earlier version before that syntax disappeared. 

The compatibility level is not automatically increased on existing databases when you upgrade SQL Server unless the compat level is earlier than SQL Server 2017 (I think 100). It is an explicit choice that the DBA makes, based on what applications are currently interacting with that database, and do they need older things that may not be present in a new database. The opposite can also be true - there may be new features tied to a new compatibility level that you'd like to take advantage of. Often Microsoft will introduce a new optimization behind a trace flag, and in a later release it will no longer require a trace flag, but the compatibility level will need to be increased.

The compatibility level isn't the same as the release of SQL Server where the database is hosted. If you have SQL Server 2022, and new databases you create will automatically have the 2022 compatiblity level (170), but existing databases won't get upgraded. However, everything on that instance will be a 2022 database. Version and compatibility levels are not the same thing.

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-lev...

[HarryPlendl]

In my case, I am upgrading a development Enterprise GIS from 11.2 to 11.3 as a test run for Production.

In order to do this our MS SQL needed to be updated from 2016 to 2022 (because 2022 is the latest compatible with Enterprise GIS 10.9.1 - which is the production environment).

After the upgrade to 2022 on the Development instance of MS SQL, the DBA asked me about the compatibility level still being left at 2016 before I upgraded to 11.3. Since the 11.3 system requirements is stating MS SQL Server 2017 or higher, I believe it would need to be set to MS SQL 2022 for access to increased functionality of >2016 MS SQL. I also would expect the upgrade to go fine until the upgrade of the spatial database is run or even the ArcGIS server as the SQL Server Client is being upgraded to ODBC 18 for the database connections. 

I've previously used PostgreSQL and this is a first-time upgrade dealing with MS SQL. Never would have gave it thought because the upgrades are usually happening because End of Life or support for an older version is on the horizon. Such as MS SQL 2016 in July of 2026.

Thoughts? 

[ShannonShields]

Like I said earlier, there is nothing in ArcGIS that is dependent on the compatibility level of the database. You can change it to 170, or leave it at 130 (2016). It is not the same as the version of SQL Server where the database is actually hosted. I'd recommend updating it if you do not have a reason not to (e.g. another older client application that relies on older, deprecated functionality) but that is more my opinion than any requirement of ArcGIS software.

We do not typically prevent an older client (like ArcMap) from connecting to a newer geodatabase, or newer version of SQL Server, but we do require that the database version (not the compatibility level of the database, but the version of SQL Server) be compatible with all the pieces - the version of ArcGIS Server, the version of Pro, the version of the geodatabase. This KB article gets updated at each release with the versions of SQL Server that are supported by each release. https://support.esri.com/en-us/knowledge-base/is-my-sql-server-database-version-supported-by-arcgis-....

Upgrading your geodatabase will only be possible if the database version is supported by the version you are trying to upgrade to. We will block upgrade on an unsupported version.

For ODBC 18, ArcGIS Desktop will require patching in order to use it - if you are running 10.8.1 the patch is https://support.esri.com/en-us/patches-updates/2024/arcgis-desktop-10-8-1-general-component-updates-... and if you are running 10.8.2 it is https://support.esri.com/en-us/patches-updates/2024/arcgis-desktop-engine-10-8-2-general-component-u...

And lastly, we have always announced deprecation of a SQL Server version shortly after it ages out of Microsoft's Mainstream Support (5 years). This means that you usually have ~6 years with a version of SQL Server before we no longer support it with our current releases. If you choose to upgrade ArcGIS software frequently, then you will also need to plan for more frequent upgrades for SQL Server. PostgreSQL reaches end of life every 5 years so has a significantly shorter lifespan. 

-Shannon