SQL SERVER 2022 – Oldest Compatibility Level Supported

SQL Server 2022, the latest version of Microsoft’s relational database management system, supports compatibility levels dating back to SQL Server 2005. This wide range of compatibility allows organizations to upgrade to the latest SQL Server version while supporting databases created under much older versions. This post will look at the oldest compatibility supported by SQL Server 2022 (compatibility level 160) and discuss why maintaining backward compatibility is essential.

What is the Compatibility Level?

In SQL Server, every database has a compatibility level that determines which features can be used within that database. When a database is created in SQL Server, it gets the compatibility level of that instance’s version. For example, a database built on a SQL Server 2022 instance will default to the SQL Server 2022 compatibility level.

The compatibility level acts as a bridge between old and new versions of SQL Server. Keeping a database at a lower compatibility level allows you to continue using it even after upgrading the SQL Server version. The trade-off is that newer features won’t be available within that database until you boost its compatibility level.

Oldest Compatibility Supported by SQL Server 2022

SQL Server 2022 supports compatibility levels back to SQL Server 2005 (90). So technically, you can restore or attach a database created under SQL Server 2005 or later and still use it on a modern SQL Server 2022 instance.

Here are the oldest compatibility levels supported by SQL Server 2022:

• SQL Server 2005 – 90
• SQL Server 2008 and 2008 R2 – 100
• SQL Server 2012 – 110
• SQL Server 2014 – 120
• SQL Server 2016 – 130
• SQL Server 2017 – 140
• SQL Server 2019 – 150

Along with the named versions, SQL Server 2022 supports manually setting any compatibility level between 90 and 150.

Why Maintaining Compatibility Matters

There are a few key reasons why retaining backward compatibility matters so much for a product like SQL Server:

1. Smoother upgrades

The main benefit is that it allows for smoother upgrades to newer SQL Server versions. Organizations can upgrade the database engine without necessarily having to change all of their databases simultaneously. Any databases requiring the older compatibility level can be kept in that legacy mode for as long as needed.

2. Avoid rewrites of legacy applications

Keeping backward compatibility also means legacy applications that rely on older SQL Server features will continue functioning after an upgrade. Without legacy compatibility, those applications would fail or require expensive rewrites after a SQL Server upgrade.

3. Reduce migration costs

Upgrading the compatibility level on a large database can take testing, code changes, and downtime. So, maintaining legacy compatibility drastically reduces the migration cost and risks when upgrading SQL Server versions.

4. Allow gradual modernization

Organizations can upgrade to SQL Server 2022 and gradually migrate individual databases to modern compatibility levels, as needed. The broad SQL Server backward compatibility makes this more gradual, lower-risk approach possible.

5. Future-proofing databases

Choosing a lower compatibility level also future-proofs databases against any deprecated features removed in later versions. Setting the compatibility level for a database created in SQL Server 2022 to 140, for example, would prevent that database from using any features deprecated in SQL Server 2017 and later.

Striking a Balance

While SQL Server’s backward compatibility capabilities are indispensable, there are also good reasons to upgrade database compatibility levels:

• Gain access to new performance enhancements and features
• Remove the use of deprecated features
• Improve database portability between versions
• Simplify support and testing processes

Organizations have to strike the right balance for their needs. But SQL Server 2022’s support for compatibility back to 2005 gives ample flexibility. You can maintain support for legacy databases as long as required, while also taking advantage of modern features for new development.

Changing the Compatibility Level

The compatibility level for a database can be changed very easily using the ALTER DATABASE statement. Here is the basic syntax:

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = version;

For example, to change a database named ‘mydb’ to the SQL Server 2022 compatibility level:

ALTER DATABASE mydb
SET COMPATIBILITY_LEVEL = 160;

The version can be specified as the year, like 140 above, or the SQL Server version number, like 2017. Setting it to 140/2017 enables all features supported in SQL Server 2017 and earlier.

This simple ALTER DATABASE statement allows the compatibility mode to be updated anytime. Remember that changing to a higher level could result in compatibility issues until any deprecated features are removed from objects like stored procedures. Checking for compatibility problems is advised.

Conclusion

SQL Server 2022’s backward compatibility back to 2005 allows organizations to upgrade their infrastructure while keeping legacy databases intact. This protects past investments in database development and avoids expensive rewrites. It also facilitates gradual, lower-risk database migrations. Striking the right balance between new and old is critical, but SQL Server’s broad backward compatibility range provides flexibility to upgrade on your own terms.

Reference: Pinal Dave (https://blog.sqlauthority.com)