Database Mirroring and Log Shipping

A given database can be mirrored or log shipped; it can also be simultaneously mirrored and log shipped. To choose what approach to use, consider the following:

• How many destination servers do you require?
  If you require only a single destination database, database mirroring is the recommended solution.
  If you require more than one destination database, you need to use log shipping, either alone or with database mirroring. Combining these approaches gives you the benefits of database mirroring along with the support for multiple destinations provided by log shipping.
• If you need to delay restoring log on the destination database (typically, to protect against logical errors), use log shipping, alone or with database mirroring.

This topic discusses considerations for combining log shipping and database mirroring.

Combining Log Shipping and Database Mirroring

The principal database in a mirroring session can also act as the primary database in a log shipping configuration, or vice versa, as the log shipping backup share is intact. The database mirroring session run in any operating mode, whether synchronous (with transaction safety set to FULL) or asynchronous (with transaction safety set to OFF).

Typically, when combining log shipping and database mirroring, the mirroring session is established before log shipping, although this is not required. Then the current principal database is configured as the log shipping primary (the principal/primary database), along with one or more remote secondary databases. Also, the mirror database must be configured as a log shipping primary (the mirror/primary database). The log shipping secondary databases should be on different server instances than either the principal/primary server or mirror/primary server.

During a log shipping session, backup jobs on the primary database create log backups in a backup folder. From there, the backups are copied by the copy jobs of the secondary servers. For the backup jobs and copy jobs to succeed, they must have access to the log shipping backup folder. To maximize availability of the primary server, we recommend that you establish the backup folder in a shared backup location on a separate host computer. Ensure that all the log shipping servers, including the mirror/primary server, can access the shared backup location (known as a backup share).

To allow log shipping to continue after database mirroring fails over, you must also configure the mirror server as a primary server, using the same configuration you use for the primary on the principal database. The mirror database is in the restoring state, which prevents the backup jobs from backing up the log on the mirror database. This ensures that the mirror/primary database does not interfere with the principal/primary database whose log backups are currently being copied by secondary servers. To prevent spurious alerts, after the backup job executes on the mirror/primary database, the backup job logs a message to the log_shipping_monitor_history_detail table, and the agent job returns a status of success.

The mirror/primary database is inactive in the log shipping session. However, if mirroring fails over, the former mirror database comes online as the principal database. At that point, that database also becomes active as the log shipping primary database. The log shipping backup jobs that were previously unable to ship log on that database, begin shipping log. Conversely, a failover causes the former principal/primary database to become the new mirror/primary database and enter the restoring state, and backup jobs on that database cease to backup log.

To run in high-safety mode with automatic failover the mirroring session is configured with an additional server instance known as the witness. If the principal database is lost for any reason after the database is synchronized and if the mirror server and witness can still communicate with each other, automatic failover occurs. An automatic failover causes mirror server to assume the principal role and bring its database online as the principal database. For more information, see Automatic Failover. If the log shipping backup location is accessible to the new principal/primary server, its backup jobs begin to ship log backups to that location. The database mirroring synchronous mode guarantees that the log chain is unaffected by a mirroring failover and that only valid log is restored. The secondary servers continue to copy log backups without knowing that a different server instance has become the primary server.

When using a local log shipping monitor, no special considerations are necessary to accommodate this scenario. For information about using a remote monitoring instance with this scenario, see, "The Impact of Database Mirroring on a Remote Monitoring Instance," later in this topic.

Failing over from the Principal to the Mirror Database

The following figure shows how log shipping and database mirroring work together when mirroring is running in high-safety mode with automatic failover. Initially, Server_A is both the principal server for mirroring and the primary server for log shipping. Server_B is the mirror server and is also configured as a primary server, which is currently inactive. Server_C and Server_D are log shipping secondary servers. To maximize availability of the log shipping session, the backup location is on a share directory on a separate host computer.

After a mirroring failover, the primary server name defined on the secondary server is unchanged. .

The Impact of Database Mirroring on a Remote Monitoring Instance

When log shipping uses with a remote monitoring instance, combining the log shipping session and database mirroring affects the information in the monitor tables. The information about the primary is a combination of the one configured at the principal/primary and the monitor configured on each secondary.

To keep monitoring as seamless as possible, when you use a remote monitor, we recommend that you specify the original primary name when configuring the primary at the secondary. This approach also facilitates changing the log shipping configuration from Microsoft SQL Server Agent. For more information about monitoring, see Monitoring Log Shipping.

Setting Up Mirroring and Log Shipping Together

To set up database mirroring and log shipping together, the following steps are required:

1. Restore backups of the principal/primary database with NORECOVERY onto another server instance to be later used as database mirroring mirror database for the principal/primary database. For more information, see Preparing a Mirror Database for Mirroring.

2. Set up database mirroring. For more information, see How to: Configure a Database Mirroring Session (SQL Server Management Studio) or Setting Up Database Mirroring.

3. Restore backups of the principal/primary database to other server instances for later use as log shipping secondary databases for the primary database. For more information, see Configuring Log Shipping.

4. Set up log shipping on the principal database as the primary database for one or more secondary databases.
   You should set up a single share as the backup directory (a backup share). This ensures that after role switching between the principal and mirror servers, backup jobs continue to write to the same directory as before. A best practice is to ensure that this share is located on a different physical server from the servers hosting the databases involved in mirroring and log shipping.
   For more information, see How to: Enable Log Shipping (SQL Server Management Studio).

5. Manually fail over from the principal to the mirror.
   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)

6. Set up log shipping on the new principal (previously mirror) as the primary database. .

   Important

   Do not perform any setup from a secondary.

7. Perform another manual failover to fail back to the original principal.

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

See Also

Concepts

Configuring Log Shipping

Other Resources

Database Mirroring
Log Shipping

Help and Information

Getting SQL Server 2005 Assistance

Change History