Upgrading Log Shipping to SQL Server 2016 (Transact-SQL)
Applies to: 
SQL Server
To preserve your log shipping disaster recovery solution, upgrade, or apply servicing updates in the appropriate order. Servicing updates include service packs or cumulative updates.
Note
Backup compression was introduced in SQL Server 2008 (10.0.x) Enterprise. An upgraded log shipping configuration uses the backup compression default server-level configuration option to control whether backup compression is used for the transaction log backup files. The backup compression behavior of log backups can be specified for each log shipping configuration. For more information, see Configure Log Shipping (SQL Server).
In This Topic:
Prerequisites
Before you begin, review the following important information:
Supported Version and Edition Upgrades: Verify that you can upgrade to SQL Server 2016 from your version of the Windows operating system and version of SQL Server. For example, you cannot upgrade directly from a SQL Server 2005 instance to SQL Server 2019 (15.x).
Choose a Database Engine Upgrade Method: Select the appropriate upgrade method and steps based on your review of supported version and edition upgrades and also based on other components installed in your environment to upgrade components in the correct order.
Plan and Test the Database Engine Upgrade Plan: Review the release notes and known upgrade issues, the pre-upgrade checklist, and develop and test the upgrade plan.
Hardware and Software Requirements for Installing SQL Server 2016: Review the software requirements for installing SQL Server. If additional software is required, install it on each node before you begin the upgrade process to minimize any downtime.
Protect Your Data Before the Upgrade
As a best practice, we recommend that you protect your data before a log shipping upgrade.
To protect your data
Perform a full database backup on every primary database.
For more information, see Create a Full Database Backup (SQL Server).
Run the DBCC CHECKDB command on every primary database.
Important
Ensure that you have sufficient space on your primary server to hold the log backup copies for as long as the upgrade of the secondaries is expected to take. If you are failing over to a secondary, this same concern applies to the secondary (the new primary).
Upgrading the (Optional) Monitor Server Instance
The monitor server instance, if any, can be upgraded at any time. However, you do not need to upgrade the optional monitor server when you upgrade the primary and secondary servers.
While the monitor server is being upgraded, the log shipping configuration continues to work, but its status is not recorded in the tables on the monitor. Any alerts that have been configured will not be triggered while the monitor server is being upgraded. After the upgrade, you can update the information in the monitor tables by executing the sp_refresh_log_shipping_monitor system stored procedure. For more information about a monitor server, see About Log Shipping (SQL Server).
Upgrading the Secondary Server Instances
The upgrade process involves upgrading the secondary server instances of SQL Server before upgrading the primary server instance. Always upgrade the secondary SQL Server instances first. Log shipping continues throughout the upgrade process because the upgraded secondary server instances continue to restore the log backups from primary server instance. If the primary server instance is upgraded before the secondary server instance, log shipping will fail because a backup created on a newer version of SQL Server cannot be restored on an older version of SQL Server. You can upgrade the secondary instances simultaneously or serially, but all secondary instance must be upgraded before the primary instance is upgraded to avoid a log shipping failure.
While a secondary server instance is being upgraded, the log shipping copy and restore jobs do not run. This means that unrestored transaction log backups will accumulate on the primary and you need to have sufficient space to hold these unrestored backups. The amount of accumulation depends on the frequency of scheduled backup on the primary server instance and the sequence in which you upgrade the secondary instances. Also, if a separate monitor server has been configured, alerts might be raised indicating restores have not been performed for longer than the configured interval.
Once the secondary server instances have been upgraded, the log shipping agents jobs resume and continue to copy and restore log backups from the primary server instance to the secondary server instances. The amount of time required for the secondary server instances to bring the secondary database up to date varies, depending on the time taken to upgrade the secondary server instance and the frequency of the backups on the primary server.
Note
During the server upgrade, the secondary database itself is not upgraded the new version. It will get upgraded only if it is brought online by initiating a failover of the log shipped database. In theory, this condition could persist indefinitely. The amount of time to upgrade the database metadata when a failover is initiated is small.
Important
The RESTORE WITH STANDBY option is not supported for a database that requires upgrading. If an upgraded secondary database has been configured by using RESTORE WITH STANDBY, transaction logs can no longer be restored after upgrade. To resume log shipping on that secondary database, you will need to set up log shipping again on that standby server. For more information about the STANDBY option, see Restore a Transaction Log Backup (SQL Server).
Upgrading the Primary Server Instance
Since log shipping is primarily a disaster recovery solution, the simplest and most common scenario is to upgrade the primary instance in-place and the database is simply unavailable during this upgrade. Once the server is upgraded, the database is automatically brought back online, which causes it to be upgraded. After the database is upgraded, the log shipping jobs resume.
Note
Log shipping also supports the option to Fail Over to a Log Shipping Secondary (SQL Server), and optionally Change Roles Between Primary and Secondary Log Shipping Servers (SQL Server). However, since log shipping is rarely configured as a high availability solution anymore (newer options are much more robust), failing over generally will not minimize downtime because system database objects will not be synchronized and enabling clients to easily locate and connect to a promoted secondary can be an ordeal.
See Also
Upgrade to SQL Server 2016 Using the Installation Wizard (Setup)
Install SQL Server 2016 from the Command Prompt
Configure Log Shipping (SQL Server)
Monitor Log Shipping (Transact-SQL)
Log Shipping Tables and Stored Procedures