upgrading-mirrored-instances.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	helpviewer_keywords
Upgrading Mirrored Instances	Learn how to reduce downtime when upgrading a SQL Server mirrored instance by using a rolling upgrade. This article includes best practices.	MikeRayMSFT	mikeray	02/01/2016	sql	database-mirroring	how-to	upgrading SQL Server, rolling upgrade of mirrored databases database mirroring [SQL Server], upgrading system rolling upgrades [SQL Server]

Upgrading Mirrored Instances

[!INCLUDE SQL Server] When upgrading a [!INCLUDEssNoVersion] mirrored instance to a new version, to a new [!INCLUDEssNoVersion] service pack or cumulative update, or to a new Windows service pack or cumulative update, you can reduce downtime for each mirrored database to only a single manual failover by performing a rolling upgrade (or two manual failovers if failing back to the original primary). A rolling upgrade is a multi-stage process that in its simplest form involves upgrading the [!INCLUDEssNoVersion] instance that is currently acting as the mirror server in a mirroring session, then manually failing over the mirrored database, upgrading the former principal [!INCLUDEssNoVersion] instance, and resuming mirroring. In practice, the exact process will depend on the operating mode and the number and layout of mirroring session running on the [!INCLUDEssNoVersion] instances that you are upgrading.

Note

For information on using database mirroring with log shipping during a migration, download this Database Mirroring and Log Shipping whitepaper.

Prerequisites

Before you begin, review the following important information:

• Supported Version and Edition Upgrades: Verify that you can upgrade to [!INCLUDEssNoVersion] 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 the latest version of [!INCLUDEssNoVersion].

• 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 [!INCLUDE ssnoversion-md]. If additional software is required, install it on each node before you begin the upgrade process to minimize any downtime.

Recommended Preparation (Best Practices)

Before starting a rolling upgrade, we recommend that you:

1. Perform a practice manual failover on at least one of your mirroring sessions:

   • Manually Fail Over a Database Mirroring Session (SQL Server Management Studio)

   • Manually Fail Over a Database Mirroring Session (Transact-SQL).

   [!NOTE]
   For information about how manual failover works, see Role Switching During a Database Mirroring Session (SQL Server).

2. Protect your data:

   1. Perform a full database backup on every principal database:

      Create a Full Database Backup (SQL Server).

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

Stages of a Rolling Upgrade

The specific steps of a rolling upgrade depend on the operating mode of the mirroring configuration. However, the basic stages are the same.

Note

For information about the operating modes, see Database Mirroring Operating Modes.

The following illustration is a flowchart that shows the basic stages of a rolling upgrade for each operating mode. The corresponding procedures are described after the illustration.

Important

A server instance might be performing different mirroring roles (principal server, mirror server, or witness) in concurrent mirroring sessions. In this case, you will have to adapt the basic rolling upgrade process accordingly. For more information, see Role Switching During a Database Mirroring Session (SQL Server).

Note

In many cases, after the rolling upgrade is completed, you will failback to the original principal server.

To change a session from high-performance mode to high-safety mode

1. If a mirroring session is running in high-performance mode, before you perform a rolling upgrade, change the operating mode to high safety without automatic failover.

   [!IMPORTANT]
   If the mirror server is geographically distant from the principal server, a rolling upgrade might be inappropriate.

   • In [!INCLUDEssManStudioFull]: Change the Operating mode option to High safety without automatic failover (synchronous) by using the Mirroring Page of the Database Properties dialog box. For information about how to access this page, see Start the Configuring Database Mirroring Security Wizard (SQL Server Management Studio).

   • In [!INCLUDEtsql]: Set transaction safety to FULL. For more information, see Change Transaction Safety in a Database Mirroring Session (Transact-SQL)

To remove a witness from a session

1. If a mirroring session involves a witness, we recommend that you remove the witness before you perform a rolling upgrade. Otherwise, when the mirror server instance is being upgraded, database availability depends on the witness that remains connected to the principal server instance. After you remove a witness, you can upgrade it at any time during the rolling upgrade process without risking database downtime.

   [!NOTE]
   For more information, see Quorum: How a Witness Affects Database Availability (Database Mirroring).

   • Remove the Witness from a Database Mirroring Session (SQL Server)

To perform the rolling upgrade

1. To minimize downtime, we recommend the following: Start the rolling upgrade by updating any mirroring partner that is currently the mirror server in all its mirroring sessions. You might have to update multiple server instances at this point.

   [!NOTE]
   A witness can be upgraded at any point in the rolling upgrade process. For example, if a server instance is a mirror server in Session 1 and is a witness in Session 2, you can upgrade the server instance now.

   The server instance to upgrade first depends on the current configuration of your mirroring sessions, as follows:

   • If any server instance is already the mirror server in all its mirroring sessions, upgrade the server instance to the new version.

   • If all your server instances are currently the principal server in any mirroring sessions, select one server instance to upgrade first. Then, manually fail over each of its principal databases and upgrade that server instance.

   After being upgraded, a server instance automatically rejoins each of its mirroring sessions.

2. For each mirroring session whose mirror server instance has just been upgraded, wait for the session to synchronize. Then, connect to the principal server instance, and manually fail over the session. On failover, the upgraded server instance becomes the principal server for that session, and the former principal server becomes the mirror server.

   The goal of this step is for another server instance to become the mirror server in every mirroring session in which it is a partner.

   Restrictions after you failover to an upgraded server instance.

   After failing over from an earlier server instance to an upgraded [!INCLUDE ssnoversion-md] server instance, the database session is suspended. It cannot be resumed until the other partner has been upgraded. However, the principal server is still accepting connections and allowing data access and modifications on the principal database.

   [!NOTE]
   Establishing a new mirroring session requires that the server instances all be running the same version of [!INCLUDEssNoVersion].

3. After you fail over, we recommend that you run the DBCC CHECKDB command on the principal database.

4. Upgrade each server instance that is now the mirror server in all mirroring sessions in which it is a partner. You might have to update multiple servers at this point.

   [!IMPORTANT]
   In a complex mirroring configuration, some server instance might still be the original principal server in one or more mirroring sessions. Repeat steps 2-4 for those server instances until all instances involved are upgraded.

5. Resume the mirroring session.

   [!NOTE]
   Automatic failover will not work until the witness has been upgraded and added back into the mirroring session.

6. Upgrade any remaining server instance that is the witness in all its mirroring sessions. After an upgraded witness rejoins a mirroring session, automatic failover becomes possible again. You might have to update multiple servers at this point.

To return a session to high-performance mode

1. Optionally, return to high-performance mode by using one of the following methods:

   • In [!INCLUDEssManStudioFull]: Change the Operating mode option to High performance (asynchronous) by using the Mirroring Page of the Database Properties dialog box.

   • In [!INCLUDEtsql]: Use ALTER DATABASE to set transaction safety to OFF.

To add a witness back into a mirroring session

1. Optionally, in high-safety mode, reestablish the witness to each mirroring session.

   To return a witness

   • Add or Replace a Database Mirroring Witness (SQL Server Management Studio)

   • Add a Database Mirroring Witness Using Windows Authentication (Transact-SQL)

See Also

Upgrade to SQL Server 2016 Using the Installation Wizard (Setup)
Install SQL Server 2016 from the Command Prompt
ALTER DATABASE Database Mirroring (Transact-SQL)
BACKUP (Transact-SQL)
View the State of a Mirrored Database (SQL Server Management Studio)
Database Mirroring (SQL Server)
Role Switching During a Database Mirroring Session (SQL Server)
Force Service in a Database Mirroring Session (Transact-SQL)
Start Database Mirroring Monitor (SQL Server Management Studio)
Database Mirroring Operating Modes