SQL Server Training | Tutorials | How to's

What is Automatic Failover in SQL Server Mirroring?

When the principal server is offline, the mirror and the witness will form a quorum and promote the mirror to be the principal. Once this is done users can continue work as usual on the other server (New Principal Server)

An automatic failover causes the mirror server to take over the role of principal server and bring its copy of  the database online as the principal database. Requiring that the database be synchronized prevents data loss during failover, because every transaction committed on the principal database is also committed on the mirror database.

In this Article, I am going to test a automatic Mirroring failover using three SQL Server Instances, where

1. Principal Server
2. Mirror Server
3. Witness Server

Principal Server Details

• SQL Server Instance Name : <SERVER_NAME>\SQL1
• Version : SQL Server 2008 R2
• Edition : Evaluation Copy
• Database Name : Test_SQLServer_Mirroring

Mirror Server Details

• SQL Server Instance Name : <SERVER_NAME>\SQL2
• Version : SQL Server 2008 R2
• Edition : Evaluation Copy
• Database Name : Test_SQLServer_Mirroring

Witness Server Details

• SQL Server Instance Name : <SERVER_NAME>\SQL3
• Version : SQL Server 2008 R2
• Edition : Evaluation Copy

as shown in the following screen shot (Prior to Failover)

Testing Automatic Mirroring Failover

What we will be testing, Currently Database named "Test_SQLServer_Mirroring" is available at SQL Server Instance – .\SQL01, we will stop SQL Services on this Server and will, what happens

OUTPUT / RESULT

What happened in the past, Once, we stopped the SQL Services on Instance : .\SQL1 the Witness server sensed these changes and resulted an automatic failover causes the mirror server to take over the role of principal server and bring its copy of the database online as the principal database.

Notes:

1. Automatic failover is supported in database mirroring sessions that are running with a witness in high-safety mode (high-safety mode with automatic failover).
2. In high-safety mode with automatic failover, once the database is synchronized, if the principal database becomes unavailable, an automatic failover occurs.
3. An automatic failover causes the mirror server to take over the role of principal server and bring its copy of the database online as the principal database. Requiring that the database be synchronized prevents data loss during failover, because every transaction committed on the principal database is also committed on the mirror database.
4. For automatic failover to improve reliability, the mirror and principal databases must reside on different computers.
5. Automatic failover requires the following conditions:
   1. The database is already synchronized.
   2. The failure occurs while all three server instances are connected, and the witness and mirror server remain connected.

Please check my previous post to know

• What is Mirroring in SQL Server ?
• What is the Hardware and software requirement for implementing Mirroring in SQL Server ?
• How to Setup Mirroring in SQL Server ?(Screen Shots)
• How to Set up Database Mirroring in SQL Server 2008 (Video)
• How mirroring is configured to provide availability of high availability
• Server roles in a database mirroring solution
• What is SQL Server Mirroring Failover ?
• How to initiate SQL Server Mirroring manual Failover ?
• What SQL Server Does, when we manually initiate a  failover ?

If you liked this post then,

Subscribe to this Blog via Email:

Click here to Subscribe to FREE email updates from "DBATAG ", so that you do not miss out anything on SQL Server !!!

TAGS : SQL Server Mirroring failover,Failover in SQL Server Mirroring, SQL Server Mirroring Failover Manually,SQL Server Manual Mirroring Failover,SQL Server Mirroring failover,test SQL Server Mirroring failover,testing SQL Server Mirroring failover manually,SQL Server Mirroring manual failover