How to Upgrade SQL Server 2014 to 2019?

Why Upgrade SQL Server 2014 to 2019?

Support is the main reason. Three facts from the Microsoft lifecycle pages explain the urgency:

• Extended Support. SQL Server 2014 extended support ended in July 2024.
• Security Updates. Paid Extended Security Updates for SQL Server 2014 end in July 2027.
• Lifecycle. SQL Server 2019 receives extended support until January 2030.

Which Upgrade Path Fits Your Server?

Select the path that matches your situation:

What Must You Check Before the Upgrade?

Seven checks come before any upgrade:

1. Check that the instance has SQL Server 2014 SP2 or later with this query:

SELECT SERVERPROPERTY('ProductVersion') AS version,
       SERVERPROPERTY('ProductLevel') AS service_pack;

2. Confirm that the instance and the operating system are 64 bit.
3. Restart Windows when a restart is pending. Setup stops the upgrade in that case.
4. Confirm that the Windows Installer service works.
5. Add the SQL Server Agent service account to the sysadmin role and enable Windows Authentication for the Agent.
6. Take a full backup of every user database and of master, model and msdb. Scripts for every backup type sit in Explore Different Types of Backups in SQL Server.
7. List the logins, linked servers and Agent tasks that the application uses.

4 Ways to Upgrade SQL Server 2014 to 2019

Three methods use tools from Microsoft and the fourth adds software for live databases.

Method #1: Upgrade in Place With Setup (Free, Same Server)

An in place upgrade replaces the SQL Server 2014 engine with SQL Server 2019 on the same server. Nine steps upgrade the default instance:

1. Right click setup.exe on the SQL Server 2019 media and select Run as administrator.
2. Select Installation in SQL Server Installation Center.
3. Select Upgrade from a previous version of SQL Server.
4. Enter the product key and accept the license terms.
5. Wait for Install Rules to finish and fix every failed rule.
6. Select the SQL Server 2014 instance on Select Instance.
7. Review the feature list and click Next.
8. Click Upgrade on Ready to Upgrade.
9. Open SSMS after the progress bar ends and execute SELECT @@VERSION; to confirm version 15.

   Setup cannot add new features while it upgrades an existing instance. Add them afterward.

Note: An in place upgrade replaces the engine files and cannot be undone. Take the backups from the checklist first.

Method #2: Restore Backups on a New Server (Free, Easy Rollback)

In a side by side upgrade the SQL Server 2014 instance remains online while you restore the databases on a new SQL Server 2019 server. Six steps move ShopDB:

1. Install SQL Server 2019 on the new server.
2. Back up ShopDB on the old instance:

BACKUP DATABASE ShopDB
TO DISK = N'G:\Upgrade\ShopDB.bak'
WITH CHECKSUM, STATS = 10;

3. Copy ShopDB.bak to the new server.
4. Restore ShopDB on the new instance and relocate the files:

RESTORE DATABASE ShopDB
FROM DISK = N'H:\Upgrade\ShopDB.bak'
WITH MOVE N'ShopDB' TO N'H:\Data\ShopDB.mdf',
     MOVE N'ShopDB_log' TO N'I:\Logs\ShopDB_log.ldf',
     STATS = 10;

5. Create the logins of the old instance on the new instance.
6. Raise the compatibility level after the application tests.

Rollback needs no extra work because the old instance remains untouched. The Restore a SQL Database From a BAK File guide explains every restore dialog.

Method #3: Use Copy Database Wizard for the Upgrade (Free, Moves Logins)

Microsoft names the Copy Database Wizard as a way to move a SQL Server database to a later version. Copy Database Wizard needs the sysadmin role on both servers and does not exist in Express edition. Five steps copy ShopDB:

1. Right click ShopDB on the old instance and select Tasks and then Copy Database.
2. Enter the SQL Server 2019 server as the destination.
3. Select Use the SQL Management Object method when users still need ShopDB.
4. Select Copy and add the logins on the server objects page.
5. Finish the wizard and check the new database.

Note: Copy Database Wizard also offers a detach and attach mode. The guide on Copy SQL Database From One Server to Another compares both modes.

Method #4: Migrate Live Databases to SQL Server 2019 With EmailDoctor (Free Preview, Paid Export)

Teams that must move databases without scripts choose EmailDoctor SQL Server Data Migration Tool. The migration software connects to the SQL Server 2014 instance and exports databases with schema and data to the SQL Server 2019 instance. Vendor notes list SQL Server 2019 and earlier releases.

The free trial previews every object but exports only 25 records per table and 5 other categories. Paid versions remove that limit.

Connect to the SQL Server 2014 Instance

Six steps open the database and show its objects:

1. Start the software on a Windows PC that reaches both instances and click Open.
2. Select Live SQL Server with Authentication in Online mode and add the login details of the 2014 instance.
3. Choose the database in the Drop Down List and click OK.
4. Close the scan message.
5. Browse the tree to review tables, views and procedures.
6. Click Export.

Export to the SQL Server 2019 Instance

Eight steps send the database to the new instance:

7. Select SQL Server Database as the type.
8. Add the Server Name with Authentication of the 2019 instance.
9. Select Create New Database or Export to Existing Database.
10. Set Collation Settings to match ShopDB.
11. Tick the objects to move.
12. Select With Schema & Data.
13. Click Save.
14. Click OK when Export Completed appears.

Note: Offline mode opens an MDF file when the old instance is down. Select SQL Server Compatible SQL Scripts to create a script file.

Which Tasks Finish the Upgrade?

Four tasks finish the work on every method. Microsoft lists the compatibility level as a separate step after the engine.

1. Take a new full backup of every upgraded database.
2. Update the statistics as Microsoft recommends after an upgrade:

USE ShopDB;
EXEC sp_updatestats;

3. Repopulate the full text catalogs of databases that use them.
4. Raise the compatibility level after the application tests, with Query Store to watch for regressions:

SELECT name, compatibility_level FROM sys.databases;

ALTER DATABASE ShopDB SET QUERY_STORE = ON;
ALTER DATABASE ShopDB SET COMPATIBILITY_LEVEL = 150;

Which Problems Stop the Upgrade?

Five problems stop an upgrade:

Frequently Asked Questions

Author’s Verdict

In place upgrades save hardware but give the least room for a rollback. Side by side restores keep the old instance online for a quick return.

Copy Database Wizard adds logins and needs sysadmin rights. Migration software fits live databases when scripts take too long.

Raise the compatibility level only after the application tests pass. Save the old backups for the first week.