ArcSDE / SQL Server compatibility level

[PaulLohr]

I did not find anything in the ArcSDE documentation referring to SQL Server's compatibility level. Not too much mentioned on the forums either.

Can anyone come up with a scenario where the compatibility level of SQL Server should be set to a version earlier than the instance that is installed? Perhaps an old application that will not be updated?

Thanks for any help,

Paul Lohr

[JayantaPoddar]

Hi Paul,

Check this technical article if it is helpful.

Thanks,

Jay

[AsrujitSengupta]

ALTER DATABASE Compatibility Level (Transact-SQL)

The above link should provide you with information related to the importance and difference between the different SQL Server Compatibility level.

From ArcSDE point of view, as per my understanding, as long as the version of the database is supported, it should not be a problem. However maybe someone else may have some more info on this......

[JoshuaBixby]

Given that ArcSDE documentation doesn't address SQL Server's compatibility level, I would argue there is an implicit requirement that ArcSDE be installed in SQL Server databases with the default setting. 

[BillFox]

Hi Paul,

Did you receive any further information about ESRI/SQL Server compatibility levels?

With 10.4.x not supporting SQL Server 2008 R2, has anyone seen ESRI documentation on SQL Server compatibility levels?

2016     130

2014     120

2012     110

2008 R2     105

2008     100

2005     90

2000     80

-Bill

Asrujit SenGupta‌ Sanket‌

[BillFox]

Reference ESRI documentation v10.4:

Upgrade an enterprise geodatabase in SQL Server

Upgrade an enterprise geodatabase in SQL Server—Help | ArcGIS for Desktop 

"Tip:

If you are unsure of the database compatibility level of your SQL Server database, check the database properties from Microsoft SQL Server Management Studio. If your database compatibility level is 80, you must update your database compatibility level. See SQL Server documentation for instructions."

But it does not indicate which level we must set it to for 10.4.1, 10.3.1. etc.

Asrujit SenGupta‌ SButala-esristaff

[BillFox]

See the "Supported Compatibility Level Values" column below.

Example: The SQL Server 2014 geodatabase can be set to 100 or 110 or 120 and ESRI is good to go.

Reference Microsoft SQL Server documentation:

ALTER DATABASE Compatibility Level (Transact-SQL) 

Arguments

https://msdn.microsoft.com/en-gb/library/bb510680.aspx#Anchor_1

database_name
Is the name of the database to be modified.

COMPATIBILITY_LEVEL { 130 | 120 | 110 | 100 | 90 | 80 }
Is the version of SQL Server with which the database is to be made compatible. The following compatibility level values can be configured:

Product	Database Engine Version	Compatibility Level Designation	Supported Compatibility Level Values
SQL Server 2016	13	130	130, 120, 110, 100
SQL Database	12	120	130, 120, 110, 100
SQL Server 2014	12	120	120, 110, 100
SQL Server 2012	11	110	110, 100, 90
SQL Server 2008 R2	10.5	105	100, 90, 80
SQL Server 2008	10	100	100, 90, 80
SQL Server 2005	9	90	90, 80
SQL Server 2000	8	80	80

-Bill

Thank you: asengupta-esristaff‌ SButala-esristaff‌