In this article, we’ll explain how to upgrade to SQL Server 2022, detailing the different options available and any relevant limitations or requirements.
As we’ve talked about many times previously on our blog, keeping your infrastructure up to date is really important, and with something as critical as your database management system, it’s not something you can afford to ignore.
However, it’s important to be very careful when updating to the latest version of any software. When software is very new, there’s an increased risk of exposing yourself to bugs that haven’t been detected yet or security flaws that haven’t been resolved or patched. That’s why it’s crucial to strike a balance between having an up-to-date system and one that has been duly tested.
Given that a few months have passed since SQL Server 2022 was released, we think we now start looking to upgrade at least some of our databases to the latest version.
How to Upgrade to SQL Server 2022
Before we look at the exact process to follow, we want to take a moment to go over a few things you should know before you start, such as whether command-based upgrades are supported, what the system requirements are, etc.
Which Older Versions Can Upgrade to SQL Server 2022
Unlike some other applications, SQL Server 2022 is not particularly problematic in this regard. You can upgrade from almost any previous version (from SQL Server 2012 SP4) and you can certainly upgrade from all versions that are currently still supported, which are:
- SQL Server 2012 (11.x) SP4 or later
- SQL Server 2014 (12.x) SP3 or later
- SQL Server 2016 (13.x) SP3 or later
- SQL Server 2017 (14.x)
- SQL Server 2019 (15.x)
If you don’t know which version you have installed, you can use the following Transact-SQL command to find out:
SELECT CONVERT(char(20), SERVERPROPERTY(‘productlevel’));
Hardware Requirements
Obviously, before any system upgrade, it’s important to know what the hardware (and software) needs are. However, we’re not going to dwell on this here, as we’ve already covered this topic in our article on requirements and planning for installing SQL Server 2022.
In any case, one thing we will point out is that you can only run SQL Server 2022 on 64-bit platforms.
If you want to upgrade SQL Server from a 32-bit platform to a 64-bit platform, you should use the migration method, where your data is first exported from one platform and then imported into the other.
Backups
As with any upgrade or migration, it’s vital to make sure you have backup copies of your databases before you make any changes. You never know when something might unexpectedly go wrong!
If you need more information about this, check out our article titled How to Back Up an SQL Database.
Recommendations When Upgrading to SQL Server 2022
Firstly, in order to start the upgrade process, you’ll need to check that the Windows Installer service is running. If it is stopped for any reason, you won’t be able to run the installation.
Secondly, you should also ensure that your system is fully up to date and that there are no pending updates or restarts.
Another thing we recommend is setting up a trial server using the new version so that you can check that all the features you need are fully functional.
It’s also not possible to run different versions of SQL Server on the same machine. In other words, all components for a single instance must be for SQL Server 2022 (16.x).
Lastly, please bear in mind that it’s not possible to install additional packages during the upgrade process. Instead, you’ll have to finish the installation and then add these new packages later.
SQL Server Migration Process
Migrating from an older version of SQL Server is a very simple processes and you can use the Microsoft Assessment and Planning Toolkit (MAP) to help you. You can download this tool here.
This general purpose tool allows you to perform various different types of migrations, but for this process, you should click on “Database”.
It’s a very simple tool, but you need to take care to select the correct options. So, make sure you select “SQL Server” and select all the elements available.
Once you’ve specified the source of the data, you should assess your local instances using the Data Migration Assistant (DMA).
This tool will allow you to detect incompatibilities or other issues and will also provide recommendations. For this, you’ll have various options such as options that in disuses, changes of behaviour, or important changes in the tool, etc.
Another great thing about this tool is that it provides information about security, performance and use of storage, which can really help you to optimise your databases and eliminate any issues.
All these points are reviewed for LTP and AE (Always Encrypted), DDM (Dynamic Data Masking) and TDE (Transparent Data Encryption).
You also have the option to use the Database Experimentation Assistant (DEA), which provides a simple way of performing migration tests for databases. Click here to download the DEA.
Once you have checked everything, you should be able to upgrade without any issues.
Recommendations When Performing a SQL Server 2022 Migration
While it’s not the main focus of this article, we’re going to just mention a few things about the migration process. But don’t worry, we’ll cover this in much more detail in a future article on the Jotelulu blog.
Firstly, make sure that everything is compatible before you perform the migration, just as you would before performing an upgrade.
To do this, use the DMA tool shown above to detect any compatibility issues.
The DMA tools will also help identify any performance issues and will make recommendations, and this will be a great help when migrating data, schemes, etc.
You can download this tool by clicking here.
As always, don’t forget to make a backup before you do anything.
It is possible to perform mass data loads from older versions like SQL Server 2008 using traditional methods like scripts or using CLI, but if you choose this method, we recommend performing exhaustive tests once the process is finished.
Conclusion
In this article, we’ve covered a number of important tips and recommendations for upgrading to SQL Server 2022. We’ve also looked at the migration process, though this is a topic that we’ll cover more thoroughly in a future article.
We’ve also provided links to a few tools that should be really useful for assessing your system and getting prepared for your upgrade or migration process.
We hope that you’ve found this article useful.
Thanks for reading!
