Side by Side Upgrade to SQL Server 2022

Problem

As a SQL Server DBA, the migration of SQL Server from an existing version to the latest version is a usual activity. In today’s cloud-oriented world, many organizations still prefer an on-prem environment; my organization is one of them. There are multiple reasons to keep your data on-prem, like having more privacy and control of the environment. Currently, our major project is to migrate our existing Microsoft SQL Server 2019 to SQL Server 2022. Recently, we completed the POC. Today, let’s discuss the steps of the SQL Server 2022 installation and migration of our databases.

Solution

For this article, we are going to setup a new server and migrate everything from one server to the new server.

Before going further and initiating the installation of SQL Server 2022, let’s find out more about some of the features in SQL Server 2022.

What’s New in SQL Server 2022?

Many new features were introduced with the launch of SQL Server 2022. Some are highlighted in the table below.

Apart from the above, there are numerous improvements in TempDB performance, storage enhancements, simplified cloud backups, and parallel query execution, as well as SSMS and Azure Data Studio enhancements. These are some key features to consider when migrating from SQL Server 2019 to SQL Server 2022.

For more detailed information, please check out:

• SQL Server 2022 New Features
• SQL Server 2022 T-SQL Enhancements

Prerequisites Before Migrating

We plan to perform a side-by-side migration; hence, we will install SQL Server 2022 on a new server, and later, we will migrate and restore databases.

Let’s discuss the prerequisites for SQL Server 2022 installation.

System Requirements

These are the minimum requirements.

Ensure Necessary Infrastructure

Get in touch with your teams (Windows, network, and storage) to ensure that the Windows server is installed with the required policies, it has a fully working network, enough storage space, and the required ISO or installation file + service pack of SQL Server 2022. Also, check the firewall ports so that the source and target servers can easily communicate.

Inventory

Develop a list containing all the databases, SQL jobs, linked servers, and logins, etc. This will help to validate all resources have been moved post-migration.

Compatibility Check

It is always recommended to perform a compatibility check before a migration to a later version of SQL Server. Features may be deprecated in the newer version. You can use the Microsoft tool Data Migration Assistant (DMA) to detect compatibility issues that can impact the functionality of your databases after migration. To avoid bugs, it is recommended to always download the newest version of DMA.

After completion, review the results for:

• Breaking Changes (must be fixed before migration).
• Behavior Changes (recommended fixes).
• Deprecated Features (features no longer supported in SQL Server 2022).
• Feature Recommendations (new SQL Server 2022 optimizations).

Check Encryption (Optional)

If any databases on SQL Server 2019 use Transparent Data Encryption (TDE), it’s essential to backup and restore the encryption certificate and private key from the old server to the new server before restoring the databases. Without this, the target instance will not be able to decrypt or bring the database online. We will verify TDE usage using the provided queries and ensure the certificate is safely transferred.

Run the following query to check which databases have TDE enabled:

select encryptor_type, key_length, key_algorithm, encryption_state, create_date
from sys.dm_database_encryption_keys
GO;
 
SELECT name, is_encrypted FROM sys.databases
GO;

Test Backups and Restores

Since we are going to perform a side-by-side migration, testing backups is an important step. It will not only help us to validate all the database backups, but also those backups can be used during migration. For testing purposes, choose a few best candidate databases from your existing server. Take the backups of those databases and restore them to verify that the backups are working fine. This will also give you an idea of how long the restores will take once you are ready to make the move.

BACKUP DATABASE [MyDB] TO DISK = ‘...’.
RESTORE DATABASE [MyDB] FROM DISK = '...' WITH MOVE ...

Update and Patch Considerations

Microsoft recommends updating (not mandatory) and patching SQL Server before proceeding with the migration activity. It will make your life easier during migration. Verify the current SQL Server version and apply the latest patches if necessary to ensure the source server is fully up to date before starting the migration.

You can run this command and also review this article for more information.

SELECT @@VERSION;

If you’re unable to patch the existing server, you can still proceed with the migration, but:

• Thoroughly test the destination (SQL Server 2022) post-migration.
• Use tools like SSMA to identify potential compatibility issues before migrating.
• Ensure the target server is fully patched with the latest updates for SQL Server 2022.
• Consider migrating first to a test/staging server, especially if upgrading across major builds.
• Perform a DBCC CHECKDB and review the SQL Server Error Log after restoring it to confirm integrity.

If patching is not possible on the source, mitigate risks by increasing pre-migration validation and post-migration testing.

Step-by-Step Guide to Migration

We have completed all our prerequisites by testing backups, compatibility checks, creating an inventory, and patching our existing SQL Server. Now, let’s start our migration activity.

Setup SQL Server 2022

Please refer to the SQL Server 2022 Downloadarticle for more information about the SQL Server installation.

Post installation of SQL Server on target server, the newly installed server should be patched to the latest service pack / cumulative update (if available any) immediately after server installation.

Check the configuration after installation to make sure the desired settings are in place prior to moving databases, logins, etc. You can also do a comparison of your old server to the new server.

Backup and Restore TDE Encryption Keys

If databases are using TDE, you should backup the TDE keys on the old instance and restore on the new instance. See this article for additional details Recovering a SQL Server TDE Encrypted Database Successfully

Backup Databases

Since we are performing a side-by-side migration, take full backups of the databases from SQL Server 2019 and copy them to the target SQL Server 2022. It is also a good idea to not have any database activity occurring on the old server, this way the backups will contain up to date data when they are restored. If this is not possible, you might also consider using transaction log backups along with the full backups.

Migrate Logins

Before initiating the data migration, migrate all the logins from the source server to the target server using the command below:

EXEC sp_help_revlogin;

The output of the above script will look like this:

-- Login: Atlas
CREATE LOGIN [Your_Login] WITH PASSWORD = 0x020XXXX……X, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
 
-- Login: DmsAppUser
CREATE LOGIN [Admin] WITH PASSWORD = 0x020XXXXX…X HASHED, SID = 0xAXXXXXX8, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

The output of sp_help_revlogin will be the login creation code from your source server. You can execute these statements on your target server to migrate the logins with same SID.

For more information, refer to these tips: Script SQL Server Logins for Disaster Recovery and Migrate SQL Server Logins with PowerShell.

Migrate Linked Servers

Creating linked servers before the full migration can make the task easy. Check all the available linked servers by executing the script below in source server.

SELECT    
    serv.NAME,
    serv.product,
    serv.provider,
    serv.data_source,
    serv.catalog,
    prin.name,
    ls_logins.uses_self_credential,
    ls_logins.remote_name
FROM
    sys.servers AS serv
    LEFT JOIN sys.linked_logins AS ls_logins
    ON serv.server_id = ls_logins.server_id
    LEFT JOIN sys.server_principals AS prin
    ON ls_logins.local_principal_id = prin.principal_id

This script provides a list of all the linked server available, like the example below from my server.

If you are using other providers, such as Oracle, make sure you install the drivers on the server as well.

Here are ways to migrate the linked servers:

• Recreate them manually on the new server
• Use Object Explorer to script out all the linked servers and execute that script on the target server to create all linked servers.

Below are the steps to migrate linked servers using Object Explorer Details:

Go to SSMS and expand ‘Server Objects Details’.

On the query panel in SSMS, select all the linked servers, then right click on ‘Script Linked Server as’ > ‘CREATE To’ > ‘New Query Editor Window’.

It will create a script with all the selected linked servers, which can be used to create linked servers on the new server.

Note: To successfully set up Linked Servers between SQL Server instances or between SQL Server and Oracle, the correct network ports must be open between the source and target servers. To connect between two SQL Server instances TCP port 1433 (default SQL Server port) should be open and if it’s a named instance (other than default SQL Server instance name) the TCP port 1434 must be also open.

In our environment, we are creating linked servers from SQL Server to an Oracle database, hence the TCP port 1521 which is default port of Oracle TNS listener should be open.

Restore Databases

Restore the databases using the backups on the new server. Here is an example script that shows to use the MOVE option if the database files need to be put into a different location on the new server.

RESTORE DATABASE [YourDatabase]
FROM DISK = 'C:\Backup\YourDatabase.bak'
WITH MOVE 'YourDatabase_Data' TO 'C:\SQLData\YourDatabase.mdf',
     MOVE 'YourDatabase_Log' TO 'C:\SQLData\YourDatabase.ldf',
     REPLACE, RECOVERY, STATS = 10;

Compatibility Level

Changing the compatibility level is an important consideration during or after a SQL Server migration, but whether you should change it depends on your application behavior, testing, and risk tolerance. After migrating (e.g., from SQL Server 2019 to 2022), it’s safe to keep the original compatibility level temporarily, complete functional and performance testing, then plan a controlled update to the latest level (e.g., 160 for SQL Server 2022) once you’re confident everything works as expected.

If you want to change the database compatibility level use the following command for each database.

ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 160;

Validate Database Status

Run this script to check the current state of the databases.

SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';

The output shows the status of the specified database in SQL Server, how to use the output: state_desc will return values like:

1. ONLINE – This means the database is healthy, accessible, and fully recovered. This is what you want to see after migration.
2. RESTORING – Indicates that the database restore is incomplete. You likely need to run RESTORE DATABASE … WITH RECOVERY.
3. RECOVERING, SUSPECT, OFFLINE, etc. – These indicate problems or states where the database is not ready for use and further action is required.

You use the output to confirm that the database is online and ready after restore/migration. If it’s not ONLINE, you should investigate further before switching application traffic to the new server.

Check for Database Errors

It would be a good idea to check for any errors prior to signing off on the migration.

DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

It’s strongly recommended to run DBCC CHECKDB on all databases after migration — not just one. The purpose of DBCC CHECKDB is to verify the physical and logical integrity of the database files. While corruption is rare, it can occur during backup/restore operations, file transfers, or if there are underlying any issues.

Update Statistics (Recommended for Performance)

This could be helpful to get updated data distribution statistics for query performance. This should be done for each database.

EXEC sp_updatestats;

Rebuild Indexes

There may also be a benefit to rebuild your indexes when moving to a new version of SQL Server. If you have the downtime, now might be a good time to get your indexes as clean as possible.

Migrate SQL Jobs

The SQL Jobs can be created manually one by one or you can generate scripts from SSMS. To generate SQL Jobs creation scripts use ‘Object Explorer Details’ like was done for the linked servers above, except select SQL Server Agent > Jobs.

Please refer to this article for more another approach: SSIS Transfer SQL Server Jobs and Transfer SQL Server Logins Tasks

Post-Migration Tasks

Maintenance Plans

If the old server has maintenance plans for database backups, rebuilding indexes, updating stats, and DBCC Check DB. You can either recreate or move the maintenance plans to the new server.

Enable Database Mail Features

Database mail must first be activated on the server (disabled by default). In SSMS, go to Management -> Right click on Database Mail -> Choose the option to ‘Configuration Database Mail’ > Click Next on the welcome screen, then select “View or change system parameters”.

A pop-up window will appear asking to “Enable the Database Mail feature.” Click Yes.

Then create mail profiles and accounts as per your requirements.

Check and Fix User Mapping

Verify user mapping if any user account is not working properly. This is a common post-migration issue where database users may become orphaned that means the mapping of the user to corresponding SQL Server login is broken. And if you check the user properties in SSMS you will see User type is‘SQL user without login’. This typically happens when we restore a database from other server, the login exists but has a different Security Identifier (SID) in new server.

To resolve this issue, the below query detects and automatically fix orphaned SQL logins by re-linking users to logins with the same name or creating the login if it doesn’t exist.

The below query reports the orphan users:

exec sp_change_users_login 'report'

The below query will fix all orphan users:

SET NOCOUNT ON;
 
DECLARE @user NVARCHAR(MAX);
 
DECLARE Orphans CURSOR FOR
SELECT dp.name AS user_name 
FROM sys.database_principals AS dp 
LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID 
WHERE sp.SID IS NULL 
AND authentication_type_desc = 'INSTANCE'
AND dp.name IN (SELECT name FROM sys.server_principals);
OPEN Orphans
FETCH NEXT FROM Orphans INTO @user
WHILE @@FETCH_STATUS = 0
   BEGIN
      DECLARE @Command NVARCHAR(MAX);
      SET @Command = N'ALTER USER ' + QUOTENAME(@user) + N' WITH LOGIN = ' + QUOTENAME(@user)
      PRINT @Command
 
      EXEC (@Command);
      FETCH NEXT FROM Orphans INTO @user
    END
CLOSE Orphans
DEALLOCATE Orphans

Potential Challenges and How to Overcome Them

During this migration of SQL Server 2019 to SQL Server 2022, we didn’t face any major issues, apart from a small hiccup during the account setup, which was resolved by adding it to the appropriate Group.

If you face any challenges during the migration process, please comment below, and we will try our best to help you.

Next Steps

• Understanding SQL Server Linked Servers
• SSRS 2022 Download and Install Instructions
• Configure SQL Server Transparent Data Encryption

Nivritti Suste

Nivritti Suste is a seasoned SQL Server Database Administrator with over 9 years of experience designing, implementing, and optimizing complex database environments for the industry. With a strong foundation in T-SQL, SSIS, SSRS, and SSAS, he has successfully delivered high-performance solutions. Passionate about sharing knowledge, he is dedicated to helping the SQL community grow by contributing to forums, and blogs. He specializes in performance tuning, database design, and cloud migration to Azure SQL Database. When not diving into SQL, Nivritti enjoys traveling and spending time with family.