Wchich version is functionality compatibility to migrate from SQL Sever version 2016 vs 2019 or 2022?

Question

Hello we have SQL Server 2016 version and was wondering which higher version of SQL Sever version can fully cover to make smooth transition functionality (like Stored Procedures, syntax codes, deprecated items, SQL Agent jobs, etc...) when trying to choose between migration upgrade to newer SQL Server versions 2019 or 2022 ?

My initial guess is to go with latest SQL Server 2022 version but would it be able to cover whatever functionality that SQL Server version 2016 can do... but just asking if newer versions has some deprecated areas and can't support older like too much gap between these versions and can't support.

Thanks in advance.

Answer 1

Let's assume for a second that it would be smoother to move to SQL 2019 than to SQL 2022 because of some changes. But what would the point be? You would have to move from SQL 2019 at some point, and you would encounter the same issues at that point when moving to SQL 2022 or some later version. So why not just as well take the pain directly? Also, if you move to SQL 2019, the next upgrade will have to be closer in time than if you go to SQL 2022. SQL 2019 will go out of mainstream support in less than 18 months.

As it stands, there are very few changes that are likely to cause any direct issues with SQL 2022. The only one I can think of is if you are using the Machine Learning Extensions. In difference to SQL 2016, SQL 2017 and SQL 2019, SQL 2022 does not ship with R (and Python), but you need to download and install it separately. And, oh, the free-standing Machine Learning Services has been removed completely. But these are not features that all sites use.

Another change is that Stretch Database has been deprecated for future removal in SQL 2022, but it is still there. In any case, no one in his sane mind would use Stretch. (Because it's far too expensive.)

What can bite you, no matter you go to SQL 2019 or SQL 2022 and also change the compatibility level to the latest version is that some optimizer enhancements lead to queries regressing in performance. You should have Query Store enabled on the database for a month before you upgrade. Then if you get a regression, you can force the old plan as a quick measure, and then you can investigate why things went wrong.

Answer 2

Hi @techresearch7777777

As a supplement, you can refer to the version upgrade scenario.

https://learn.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades-2022?view=sql-server-ver15

Best regards,

Percy Tang

Answer 3

Deprecated features are documented at

https://learn.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2019?view=sql-server-ver16

A easier & fatser way is to use DMA = "Data Migration Assistant" to check all your database for may known issues.

https://learn.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver16