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.
In This Topic:
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 2016.
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 2016. If additional software is required, install it on each node before you begin the upgrade process to minimize any downtime.
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.
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.
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.
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