Possibity of upgrade and migration from SQL 2014 to SQL 2022

Question

HI Team,

I am having one system ( Procesor: Intel Xeon E-2224 3.4GHz, 8M c ache, 4C/4T, turbo (71W)) with installed 2014 SQL I would like to upgrade to 2022 Standard SQL is it possible and what is most easy way.

Kind regards,

Answer 1

I assume that you have SQL 2014 Standard Edition. Can you share the output of SELECT @@version?

It is certainly possible, but as for what is the easiest way, that depends on the situation.

In many cases when you move to a new version of SQL Server, you also migrate to new hardware, not the least when the leap is this big. An important factor here is also the operating system. What OS do you have? If you have a really old OS, it may not be supported by SQL 2022. Which is one more reason to move to new hardware.

Also, is this is a physical machine or virtual machine? Virtual machines permits you to clone or snapshot them and run a test upgrade. With a physical machine, you need to cross Rubicon directly. (Since you specify the hardware specs, I assume that it is physical.)

If you want to keep the hardware, and the OS is Window Server 2016 or later, you can run an in-place upgrade. This is a supported version upgrade.

Else you would install a new instance on the new hardware and then restore backups of your databases on the new instance. You will need to migrate logins, jobs etc manually.

You can use the Data Migration Assistant to find out if there are backwards compatibility issues that you need to address before the upgrade. I've never used this tool myself, but generally, the number of breaking changes are small.

The area where you are most likely to experience setbacks is performance. Microsoft makes changes to the optimizer in every release, and the idea is of course that these changes will result in better performance overall. However, due to the nature of optimization with estimates from sampled statistics, these changes can backfire.

Thankfully, SQL Server 2022 comes with features that makes it easier to deal with these. The first thing should is to enable Query Store for all databases. Then make sure that all databases remain in compatibility level 120, so that you run with the optimizer of SQL 2014. Run with this setup for 1-2 weeks. Then you flip the compat level to 160. If you get performance regressions, you can use Query Store to force the old plan from compat level 120 to fix the urgent problem, while you investigate a better long-term solution.

There is also a tool in recent versions of SSMS that can help you with this process. Right-click a database, select Tasks from the context menu, and at the bottom, you find the alternative Database Upgrade.

Answer 2

Hi @Tarik sendro

You need to ensure the hardware and operating system meet SQL Server 2022’s requirements.

See this doc: SQL Server 2022: Hardware and software requirements.

what is most easy way

Mostly speaking, the Side-by-Side Migration approach is often considered instead of in-place upgrade.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".