Fail Over techniques for Database Mirroring using SQL SERVER 2008, Forced service

Fail-over techniques in database mirroring. There are different techniques for database mirroring.

1. Automatic fail over 
2. Manual fail over
3. Forced fail over

The following table summarizes which forms of failover are supported under each of the operating modes.

	High performance	High-safety mode without a witness	High-safety mode with a witness
Automatic failover	No	No	Yes
Manual failover	No	Yes	Yes
Forced service	Yes	Yes	No

We are now looking into Forced Service for database mirroring. This option is available against high safety but automatic failover is not available.  In the event of failure of the principal, the database service becomes unavailable. You need manual intervention to make the database service available.

This option is available only under all the following conditions:

·         The principal server is down.

·         WITNESS is set to OFF or is connected to the mirror server.

Forcing service suspends the session and starts a new recovery fork. The effect of forcing service is similar to removing mirroring and recovering the former principal database. However, forcing service facilitates resynchronizing the databases (with possible data loss) when mirroring resumes.

To force service in a database mirroring session when Principal is OFF

1.    Connect to the mirror server.

2.    Issue the following statement:

ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The mirror server immediately transitions to principal server, and mirroring is suspended.

·         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 thatPartner_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.

To verify that you need to go for following steps:


1. Configure Mirroring without Witness. You can see Principal and Mirror run healthy.

2. lets Disconnect principal and look at the mirror status, Mirror shows Disconnected and in recovery.

3. Mirror becomes disconnected then executed force service command

4. Mirror database become principal and you can access it now. 


5. Start Original principal server again and both databases goes into suspended mode

6. Go to Properties of principal database and press resume to get database back to online but message shows possible data lost.

6. Go to Properties of principal database and press resume to get database back to online. look at the final status here. Mirror has been active as principal and original principal working as mirror server.