Upgrading Log Shipping to SQL Server 2016 (Transact-SQL)


Updated: February 1, 2016

Applies To: SQL Server 2016

When upgrading from a SQL Server log shipping configuration to a new SQL Server 2016 version, a new SQL Serverservice pack, or a SQL Servercumulative update, upgrading your log shipping servers in the appropriate order will preserve your log shipping disaster recovery solution.

System_CAPS_ICON_note.jpg Note

Backup compression was introduced in SQL Server 2008 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:

Before you begin, review the following important information:

As a best practice, we recommend that you protect your data before a log shipping upgrade.

To protect your data

  1. Perform a full database backup on every primary database.

    For more information, see Create a Full Database Backup (SQL Server).

  2. Run the DBCC CHECKDB command on every primary database.

System_CAPS_ICON_important.jpg 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).

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).

The upgrade process involves upgrading the secondary server instances of SQL Server to SQL Server 2016 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 SQL Server 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 simultanously 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.

System_CAPS_ICON_note.jpg Note

During the server upgrade, the secondary database itself is not upgraded to a SQL Server 2016 database. 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.

System_CAPS_ICON_important.jpg 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).

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.

System_CAPS_ICON_note.jpg 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.

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

Community Additions