Upgrading SQL Server from 2016 to 2019 easy Step-By-Step Guide

In this tutorial, we will be looking at upgrading SQL Server from 2016 to 2019 in a few easy steps. This tutorial will work for any version of SQL Server. Click here to jump straight to the tutorial. 

Important

According to Microsoft’s website: You can upgrade instances of SQL Server 2012 (11.x), SQL Server 2014 (12.x), SQL Server 2016 (13.x), or SQL Server 2017 (14.x) directly to SQL Server 2019 (15.x). For SQL Server 2008, and SQL Server 2008 R2, you’ll either need to do a side-by-side upgrade, or a migration, to move to SQL Server 2019 as there is no common overlap between a supported mainstream operating system.

Pre Upgrade Checklist

• Before upgrading from one edition of SQL Server 2019 (15.x) to another, verify that the functionality you are currently using is supported in the edition to which you are moving.
• Verify supported hardware and software.
• Before upgrading SQL Server, enable Windows Authentication for SQL Server Agent and verify the default configuration: that the SQL Server Agent service account is a member of the SQL Server sysadmin group.
• To upgrade to SQL Server 2019 (15.x), you must be running a supported operating system. For more information, see Hardware and Software Requirements for Installing SQL Server.
• Upgrade will be blocked if there is a pending restart.
• Upgrade will be blocked if the Windows Installer Service is not running.

Upgrade Table Matrix

Please see the table below that will show you the versions of SQL server you can upgrade from to SQL Server 2019.

Operating system support

The following table shows which editions of SQL Server 2019 are compatible with which versions of Windows:

Running DMA

Before we begin the upgrade we will first need to download Microsoft’s Data Migration Assistant (DMA) to check for compatibility issues before attempting the upgrade. To do this, download and install the file from the URL below.

After installation search for and run the Data Migration Assistant. See the image below.

Once the application is open, hit the + in the left panel to start a new project. Fill in the appropriate data and hit create to continue. see the example in the image below.

On the next screen, select the target type (database version you will be upgrading to) and the report you wish to generate. Select next to move on to the next section.

Next, we will enter the database instance/server name that we will be upgrading. See the image below. Click next to move on to the next section.

This section is pretty straight forward. Just select the server name at the top in the right panel to highlight all the databases in that instance. Once you’ve selected that, click add, then click start assessment.

The compatibility report may take a while to generate depending on the number of databases in your instance and their sizes.

Upgrading SQL Server from 2016 to 2019

Once we’ve satisfied the pre-upgrade checks above and are satisfied with our compatibility report, we can start upgrading SQL server from 2016 to 2019.

Before starting it is important that we take a backup of our existing system so that we can do a rollback in the event of the upgrade failing for whatever reason. In my case, I will be taking a snapshot of my server.

1. Providing that you’ve already downloaded the installation media (ISO), go ahead and mount it by right-clicking it and selecting “mount“. Once mounted, a directory should be open (see image below). Execute setup.exe to start the installation.
2. If your OS is not up to date you might get the message in the image below. You can fix this by applying the appropriate patches to the server or upgrading the OS to the recommended version. Please see the table in the Operating System Support section.   
3. On the screen that appears when you’ve executed the setup.exe script, in the left panel click installation then in the right panel that appears click Upgrade from a previous version of SQL Server.
4. The next few steps are pretty straight forward. If you have a product key, you should enter it in the licenses section if not you can select developer or evaluation (depending on your environment).
5. Now we should be at the select instance screen, this is where we will be selecting the instance to upgrade. If you have multiple instances installed on your server, you will see them listed in the right panel. Use the drop-down menu to select the one you want to upgrade.                                           
6. Next, we will be selecting the features of our current installation that we want to upgrade to 2019.
7. On the instance configuration screen, we will leave everything as default. Your instance name might be different from mine so don’t be alarmed.                                           
8. Base on your selection you might need to do server configurations. On this screen, you will be selecting the service accounts that will be used to run certain database services.
9. After completing the last step we should now be on the ready to upgrade screen. At the bottom hit upgrade.
10. The database and the selected features should now be in the process of upgrading from 2016 to 2019.     

Other Posts

• How to add a new Named Instance in SQL Server
• How to add a new Named Instance in SQL Server
• How To: SQL Server job progress check
• Step-by-Step Guide: How to install SQL Server 2019 on RHEL 8