Forced Service (with Possible Data Loss)

Database mirroring provides forcing service (with possible data loss) as a disaster recovery method to allow you to use a mirror server as a warm standby server. Forcing service is possible only if the principal server is disconnected from the mirror server in a mirroring session. Because forcing service risks possible data loss, it should be used cautiously and sparingly.

Support for forced service depends on the operating mode and the state of the session, as follows:

• Typically, high-performance mode supports forcing service whenever the principal server is disconnected. However, though unnecessary, a witness can exist for a high-performance mode session. In this case, forcing service requires that the mirror server and witness are connected to each other.
• High-safety mode without automatic failover supports forcing service whenever the principal server is disconnected.
• High-safety mode with automatic failover supports forcing service whenever the mirror server and witness are connected to each other and neither is connected to the principal server (as long as the mirror server was not in the process of rolling back the mirror database when it was last connected to the principal).

We recommend forcing service only if you must restore service to the database immediately and are willing to risk losing data. The effect of forcing service is similar to removing mirroring, except that forcing service facilitates resynchronizing the databases when mirroring is resumed, at the risk of possible data loss. Forcing service initiates a smooth transition of the principal role to the mirror database. The mirror server assumes the role of principal server and immediately serves its copy of the database to clients. The new principal database runs without a mirror (that is, it runs exposed).

Typical Case of Forced Service

The following figure illustrates a typical case of forced service (with possible data loss).

In the figure, the original principal server, Partner_A, becomes unavailable to the mirror server, Partner_B, causing the mirror database to be disconnected. After ensuring that Partner_A is not available to clients, the database administrator forces service, with possible data loss, on Partner_B. Partner_B becomes the principal server and runs with the database exposed (that is, unmirrored). At this point, clients can reconnect to Partner_B.

When Partner_A becomes available, it reconnects to the new principal server, rejoining the session and assuming the mirror role. The mirroring session is suspended immediately, without having synchronized the new mirror database. Suspending the session allows the database administrator to decide whether to resume the session or, in extreme cases, remove mirroring and attempt to salvage data from the former principal database. In this case, the database administrator chooses to resume mirroring. At that point, Partner_A takes over the role of mirror server and rolls back the former principal database to the point in time of the last successfully synchronized transaction; if any committed transactions were not written to disk on the mirror server before service was forced, they are lost. Partner_A then rolls forward the new mirror database by applying any changes made on the new principal database since the former mirror server became the new principal server.

Risks of Forcing Service

It is essential to understand that forcing service can cause data loss. Data loss is possible because the mirror server cannot communicate with the principal server and, therefore, cannot guarantee that the two databases are synchronized. Forcing service starts a new recovery fork. Because the original principal database and mirror database are on different recovery forks, each database now contains data that the other database does not: the original principal database contains whatever changes were not yet sent from its send queue to the former mirror database (the unsent log); the former mirror database contains whatever changes occur after service was forced.

If service is forced because the principal server has failed, potential data loss is depends on whether any transaction logs were not sent to the mirror server before the failure. Under high-safety mode, this is possible only until the mirror database becomes synchronized. Under the high-performance mode, accumulated unsent log is always a possibility.

The implications of forcing service depend partly on whether the session has a witness:

• In the absence of a witness, service can be forced if the partners become disconnected, for example, because their network connection is broken. If the original principal server is still running, both partners own the principal role. Clients connecting to the new principal server will access the current version of the database, while clients connecting to the original principal server will access the original principal database. This situation increases the potential for data loss. If the partners are allowed to reconnect, the original principal server assumes the mirror role and changes the status of its database to "recovering," before mirroring is suspended. If the session is resumed, transactions on the original principal database whose log was in the send queue as of the most recent disconnection are lost. In addition, any the transactions that occurred after service was forced are also lost.
• In the presence of a witness, if the mirror server is disconnected from both the principal server and the witness, as long as the latter two remain connected to each other, the principal runs exposed. If the principal server then becomes disconnected from the witness, it stops serving the database. Thereafter, if the mirror server reconnects to the witness, forcing service becomes possible. If service is forced, all the changes made while the original principal server was running exposed will be lost if the original principal server reconnects.

For more information, see "Managing the Potential Data Loss," later in this topic.

Managing the Potential Data Loss

After service is forced, once the former principal server is available, assuming that its database is undamaged, you can attempt to manage the potential data loss. The available approach for managing potential data loss depends on whether the original principal server has reconnected to its partner and rejoined the mirroring session. Assuming that the original principal server can access the new principal instance, reconnecting occurs automatically and transparently.

The Original Principal Server Has Reconnected

Typically, after a failure, when the original principal server restarts it quickly reconnects to its partner. On reconnecting, the original principal server becomes the mirror server. Its database becomes the mirror database and enters the recovering state before the session is suspended. The mirror database will not be not rolled back unless you resume mirroring.

However, the recovering database is inaccessible; therefore, you cannot inspect it to evaluate what data would be lost if you were to resume mirroring. Therefore, the decision on whether to resume or remove mirroring depends on whether you are willing to accept any data loss at all.

• If losing any data would be unacceptable, you should remove mirroring to salvage them.
  Removing mirroring would allow the database administrator to recover the original principal database and attempt to recover the data that would have been lost. However, when the former mirror database comes online, the former partners will be serving divergent databases with the same name. The database administrator needs to make one of the databases inaccessible to clients to avoid further divergence of the database and to prevent client-failover issues.
• If losing any data would be acceptable, you can resume mirroring.
  Resuming mirroring causes the new mirror database to be rolled back as the first step in synchronizing the database. If any log records were waiting in the send queue at the time of failure, the corresponding transactions are lost, even if they were committed.

The Original Principal Server Has Not Reconnected

If you can temporarily prevent the original principal server from reconnecting over the network to the new principal server, you can inspect the original principal database to evaluate what data would be lost if mirroring were resumed.

• If the potential data loss is acceptable
  Allow the original principal server to reconnect to its partner. Reconnecting causes mirroring to be suspended. To proceed with mirroring, simply resume the session. The former principal server assumes the mirror role. The new mirror server drops the original recovery fork, losing any transactions that were never sent to or received by the former mirror server.
• If the data loss is unacceptable
  If the original principal database contains critical data that would be lost if you resumed the session, you can preserve the data on the original principal server by removing mirroring. We recommend that you attempt to back up the tail of the principal's log at this point. Then, you can update the current principal (the former mirror database) by exporting the data you want to salvage from the original principal database and importing it into the current principal database. We recommend taking a full database backup of the updated database as quickly as possible.
  To re-establish mirroring with the updated database as the initial principal database, use this backup (and least one subsequent log backup) to create a new mirror database. Every log backup taken after mirroring was removed must be applied. Therefore, we recommend delaying additional log backups of the principal database until the new mirroring session starts.

To force service

• How to: Force Service in a Database Mirroring Session (Transact-SQL).

To resume database mirroring

• How to: Pause or Resume a Database Mirroring Session (SQL Server Management Studio)
• How to: Resume a Database Mirroring Session (Transact-SQL)

To create a new mirror database

How to: Prepare a Mirror Database for Mirroring (Transact-SQL)

To start up database mirroring

• Setting Up Database Mirroring
• How to: Configure a Database Mirroring Session (SQL Server Management Studio)

See Also

Concepts

Asynchronous Database Mirroring (High-Performance Mode)
Estimating the Interruption of Service During Role Switching
Mirroring States
Performing a Complete Database Restore (Full Recovery Model)
Possible Failures During Database Mirroring
Recovery Paths
Synchronous Database Mirroring (High-Safety Mode)

Other Resources

Connecting Clients to a Mirrored Database

Help and Information

Getting SQL Server 2005 Assistance

Change History