Failover techniques for Database Mirroring using SQL SERVER 2008

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

Automatic Fail-Over:

To test database mirroring auto fail over solution you need to restart database engine and your Principal and Mirror servers will be reversed but you need to set your mirroring solution as (Principal – Witness - Mirror). You can view how we can test auto fail over solution look at this link.

http://shamas-saeed.blogspot.com/2012/04/failover-techniques-using-database.html

Manual Fail-Over:

During a manual failover, the principal and mirror server roles are swapped for the database on which the failover occurs. The mirror database becomes the principal database and the principal database becomes the mirror. For example, the following table shows the how a manual failover swaps the roles of two mirroring partners: System

http://shamas-saeed.blogspot.com/2012/04/fail-over-techniques-using-sql-server.html

Forced Fail-Over:

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.

http://shamas-saeed.blogspot.com/2012/05/fail-over-techniques-for-database.html

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.