SQL Server Mirroring Automatic Failover

Leave a comment (0) Go to comments

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)


SQL Server Mirroring Automatic Failover What is Automatic Failover in SQL Server Mirroring example of Automatic Failover in SQL Server Mirroring demo for sql server mirroring automatic 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

SQL Server Mirroring Automatic Failover What is Automatic Failover in SQL Server Mirroring example of Automatic Failover in SQL Server Mirroring demo for sql server mirroring automatic failover

OUTPUT / RESULT

SQL Server Mirroring Automatic Failover What is Automatic Failover in SQL Server Mirroring example of Automatic Failover in SQL Server Mirroring demo for sql server mirroring automatic failover

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

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

EOF - SQL Server Mirroring Automatic Failover, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.