Failover techniques for Database Mirroring using SQL SERVER 2008, Automatic

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

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/verify-database-mirroring-solution-for.html

The following illustration shows a single instance of automatic failover copy from msdn help.

Initially, all three servers are connected (the session has full quorum). Partner_A is the principal server and Partner_B is the mirror server. Partner_A (or the principal database on Partner_A) becomes unavailable. The witness and Partner_B both recognize that the principal is no longer available the session retains quorum. Partner_B becomes the principal server and makes its copy of the database available as the new principal database. Eventually, Partner_A reconnects to the session and discovers that Partner_B now owns the principal role. Partner_A then takes on the mirror role.

Let see changes in SSMS

Server	Before failover	After failover
MSSQLSERVER	PRINCIPAL	MIRROR
MIRROR	MIRROR	PRINCIPAL

Look at this screen shot. Principal and Mirror are fully synchronized.

Look at this screen shot. We are going to restart database engine to test our fail over solution.

After successful restart Principal and Mirror has been changed.

Automatic fail-Over for one database:

If you want to test over fail over for any single database then you need to go to database mirror properties and press fail-over option to set one database as auto fail-over.

Future Work:

• Manual fail-over with out Witness
• Forced fail-over