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

Upgrading SQL Server from 2016 to 2019

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.

Upgrade from

Supported upgrade path

SQL Server 2012 (11.x) SP4 Enterprise

SQL Server 2019 (15.x) Enterprise

SQL Server 2012 (11.x) SP4 Developer

SQL Server 2019 (15.x) Developer

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2019 (15.x) Enterprise

SQL Server 2012 (11.x) SP4 Standard

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2012 (11.x) SP4 Web

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2012 (11.x) SP4 Express

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2019 (15.x) Express

SQL Server 2012 (11.x) SP4 Business Intelligence

SQL Server 2019 (15.x) Enterprise

SQL Server 2012 (11.x) SP4 Evaluation

SQL Server 2019 (15.x) Evaluation

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2019 (15.x) Developer

SQL Server 2014 (12.x) SP2 Enterprise

SQL Server 2019 (15.x) Enterprise

SQL Server 2014 (12.x) SP2 Developer

SQL Server 2019 (15.x) Developer

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2019 (15.x) Enterprise

SQL Server 2014 (12.x) SP2 Standard

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2014 (12.x) SP2 Web

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2014 (12.x) SP2 Express

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2019 (15.x) Express

SQL Server 2019 (15.x) Developer

SQL Server 2014 (12.x) SP2 Business Intelligence

SQL Server 2019 (15.x) Enterprise

SQL Server 2014 (12.x) SP2 Evaluation

SQL Server 2019 (15.x) Evaluation

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2019 (15.x) Developer

SQL Server 2016 (13.x) 13.0.1601.5 Enterprise

SQL Server 2019 (15.x) Enterprise

SQL Server 2016 (13.x) 13.0.1601.5 Developer

SQL Server 2019 (15.x) Developer

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2019 (15.x) Enterprise

SQL Server 2016 (13.x) 13.0.1601.5 Standard

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2016 (13.x) 13.0.1601.5 Web

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2016 (13.x) 13.0.1601.5 Express

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2019 (15.x) Express

SQL Server 2019 (15.x) Developer

SQL Server 2016 (13.x) 13.0.1601.5 Business Intelligence

SQL Server 2019 (15.x) Enterprise

SQL Server 2016 (13.x) 13.0.1601.5 Evaluation

SQL Server 2019 (15.x) Evaluation

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2019 (15.x) Developer

SQL Server 2017 (14.x) Enterprise

SQL Server 2019 (15.x) Enterprise

SQL Server 2017 (14.x) Developer

SQL Server 2019 (15.x) Developer

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2019 (15.x) Enterprise

SQL Server 2017 (14.x) Standard

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2017 (14.x) Web

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2017 (14.x) Express

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2019 (15.x) Express

SQL Server 2019 (15.x) Developer

SQL Server 2017 (14.x) Business Intelligence

SQL Server 2019 (15.x) Enterprise

SQL Server 2017 (14.x) Evaluation

SQL Server 2019 (15.x) Evaluation

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Standard

SQL Server 2019 (15.x) Web

SQL Server 2019 (15.x) Developer

SQL Server 2019 (15.x) release candidate*

SQL Server 2019 (15.x) Enterprise

SQL Server 2019 (15.x) Developer

SQL Server 2019 (15.x) Enterprise

Operating system support

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

SQL Server edition:

Enterprise

Developer

Standard

Web

Express

Windows Server 2019 Datacenter

Yes

Yes

Yes

Yes

Yes

Windows Server 2019 Standard

Yes

Yes

Yes

Yes

Yes

Windows Server 2019 Essentials

Yes

Yes

Yes

Yes

Yes

Windows Server 2016 Datacenter

Yes

Yes

Yes

Yes

Yes

Windows Server 2016 Standard

Yes

Yes

Yes

Yes

Yes

Windows Server 2016 Essentials

Yes

Yes

Yes

Yes

Yes

Windows 10 IoT Enterprise

No

Yes

Yes

No

Yes

Windows 10 Enterprise

No

Yes

Yes

No

Yes

Windows 10 Professional

No

Yes

Yes

No

Yes

Windows 10 Home

No

Yes

Yes

No

Yes

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.

Data Migration Assistant

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.

DMA Add new project

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.

dma report type

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.

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

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.SQL Server 2019 ISO mounted
  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.   Sql server 2019 installation hardware error
  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.SQL Server 2019 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.                                            SQL Server 2019, Select instance to upgrade
  6. Next, we will be selecting the features of our current installation that we want to upgrade to 2019.SQL Server 2019 selecting features to upgrade
  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.                                            SQL Server 2019 instance upgrade instance configuration
  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. Sql server 2019 upgrade server configuration
  9. After completing the last step we should now be on the ready to upgrade screen. At the bottom hit upgrade.Sql Server 2019 ready to upgrade
  10. The database and the selected features should now be in the process of upgrading from 2016 to 2019.      SQL Server 2016 to 2019 upgrade progress

Other Posts