How to: Minimize Downtime for Mirrored Databases When Upgrading Server Instances

SQL Server Setup

previous page next page

SQL Server Setup Help

How to: Minimize Downtime for Mirrored Databases When Upgrading Server Instances
	See Also

: : : : : : :

(Declaration)

This topic describes how to minimize downtime for mirrored databases when you update your server instances from Microsoft SQL Server 2005 to Microsoft SQL Server 2008. This process involves sequentially upgrading the instances of SQL Server 2005 that are participating in database mirroring. This form of upgrade, which is known as a rolling upgrade, reduces downtime to only a single failover. Note that for high-performance mode sessions in which the mirror server is geographically distant from the principal server, a rolling upgrade might be inappropriate.

Note:
For information about performing a rolling upgrade to install a service pack or hotfix, see How to: Install a Service Pack on a System with Minimal Downtime for Mirrored Databases.

A rolling upgrade is a multi-stage process that consists of the following stages:

Protect your data.
If the session includes a witness, we recommend that you remove the witness. 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.
If a session is running in high-performance mode, change the operating mode to high-safety mode.
Upgrade each server instance that is involved in database mirroring. A rolling upgrade involves upgrading the server instance that is currently the mirror server, manually failing over each of its mirrored databases, and upgrading the server instance that was first the principal server (and is now the new mirror server). At this point, you will have to resume mirroring.

Note:
Before starting a rolling upgrade, we recommend that you perform a practice manual failover on at least one of your mirroring sessions.
Revert to high-performance mode, if it is required.
Return the witness to the mirroring session, if it is required.

Note:
For more information, see Quorum: How a Witness Affects Database Availability.

Note:
Before starting a rolling upgrade, we recommend that you perform a practice manual failover on at least one of your mirroring sessions.

The procedures for these stages are described here.

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 Database Mirroring Sessions.

To protect your data before an upgrade (a best practice)

Perform a full database backup on every principal database.

To back up a database
- How to: Back Up a Database (SQL Server Management Studio).
- How to: Create a Full Database Backup (Transact-SQL)
Run the DBCC CHECKDB command on every principal database.

To remove a witness from a session

If a mirroring session involves a witness, we recommend that you remove the witness before you perform a rolling upgrade.

To remove the witness
- How to: Remove the Witness from a Database Mirroring Session (SQL Server Management Studio)
- How to: Remove the Witness from a Database Mirroring Session (Transact-SQL)

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

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. Use one of the following methods:
- In SQL Server Management Studio: 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 How to: Start the Configuring Database Mirroring Security Wizard (SQL Server Management Studio).
- In Transact-SQL: Set transaction safety to FULL. For more information, see How to: Change Transaction Safety in a Database Mirroring Session (Transact-SQL)
  
  Note:
  For more information, see Transact-SQL Settings and Database Mirroring Operating Modes.

Note:
For more information, see Transact-SQL Settings and Database Mirroring Operating Modes.

To perform the rolling upgrade

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.

To perform a manual failover

How to: Manually Fail Over a Database Mirroring Session (SQL Server Management Studio)
How to: Manually Fail Over a Database Mirroring Session (Transact-SQL).

For information about how manual failover works, see Manual Failover.

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 a SQL Server 2005 server instance to a SQL Server 2008 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 SQL Server.
After you fail over, we recommend that you run the DBCC CHECKDB command on the principal database.

Note:
Establishing a new mirroring session requires that the server instances all be running the same version of SQL Server.

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.

Resume the mirroring session.

Note:
Automatic failover will not work until the witness has been upgraded.
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.

Note:
Automatic failover will not work until the witness has been upgraded.

To return a session to high-performance mode

Optionally, return to high-performance mode by using one of the following methods:
- In SQL Server Management Studio: Change the Operating mode option to High performance (asynchronous) by using the Mirroring Page of the Database Properties dialog box.
- In Transact-SQL: Use ALTER DATABASE to set transaction safety to OFF.

To return a witness to a mirroring session

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

To return a witness
- How to: Add or Replace a Database Mirroring Witness (SQL Server Management Studio)
- How to: Add a Database Mirroring Witness Using Windows Authentication (Transact-SQL)