locked
SQL Server 2005 Failovery from only Mirror DB RRS feed

  • Question

  • Hi Everyone;

    I have a SQL Server 2005 DB server (SRV1) and a mirror DB server (SRV2) without witness server. I saw that there is a "Failover" button on Mirror window of SRV1. When click it servers changes roles.

    My question is that: If my mirrored DB server crashs and I can not open windows or if I lost all data on SRV1, how can I failover from only mirror DB (SRV2)?

     

    Thanks in advance.

    Senol Akbulak

    Monday, November 20, 2006 3:14 PM

Answers

  • Hello Senol,

    I think this document useful for you,....

    Regards..

    Tarkan...

    GTA

    Loss of the principal

    If the principal fails, the failover scenario depends on the transaction safety level and whether you have a witness.

    Scenario 1: Safety FULL with a witness

    This scenario provides the high safety with automatic failover. In the event of the failure of the principal, the mirror forms a quorum with the witness. Automatic failover will take place, thereby minimizing the database downtime.

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror, respectively. Server_A fails. Following the automatic failover, Server_B takes on the role of the principal. However, since there is no mirror after the failover, the mirroring state is DISCONNECTED and the principal is exposed. Once the database on Server_A becomes operational, it automatically assumes the role of the mirror.

    Scenario 2: Safety FULL without a witness

    This scenario provides high safety, but automatic failover is not allowed. In the event of failure of the principal, the database service becomes unavailable. You need manual intervention to make the database service available. You must break the mirroring session and then recover the mirror database.

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

    ALTER DATABASE <database name> SET PARTNER OFF

    RESTORE DATABASE <database name> WITH RECOVERY

    Once Server_A becomes available, you need to re-establish the mirroring session.

    Scenario 3: Safety OFF

    If the safety level is OFF, the witness doesn’t add any value to the database mirroring scenario. Therefore, it is recommended that if you plan to run database mirroring when the safety level is OFF, don’t configure a witness. In the event of failure of the principal, the database service becomes unavailable. You can perform a force service to make the database service available on the mirror. However, since the safety level is OFF, it is possible that there were transactions that didn’t make it to the mirror at the time of the failure of the principal. These transactions will be lost. Therefore, manual failover with safety OFF involves acknowledging the possibility of data loss.

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

    ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

    Once the database on Server_A becomes operational, it automatically assumes the role of the mirror. However, the mirroring session remains SUSPENDED, and you will need to manually RESUME the mirroring session.


     

    Another option in the event of failure of the principal, is to break the mirroring session and then recover the mirror database, as in Scenario 2: Safety FULL without a witness. Whether you choose to break the mirroring session or force service, you will lose the transactions that haven’t yet made it to the mirror at the time of failure.

    Tuesday, November 21, 2006 8:19 AM

All replies

  •  

    I'm not sure I follow your question - but I'm assuming you want to failover using T-SQL? You can "alter database <dbname> set partner failover".

    Regards,

    Matt Hollingsworth

    Sr. Program Manager

    Microsoft SQL Server

    Monday, November 20, 2006 4:50 PM
  •  

    I want to failover when principle server crashed.

    When I run "alter database <dbname> ser partner failover" T-SQL on mirror server I get this error :

    The alter database for this partner config values may only be initiated on the current principal server for database "TEST002".

    The problem also is here. So the principle server crashed or disk failed on principle server and there is no way to reach principle server. How can I  failover without principle server?

     

    Thanks a lot.

    Senol Akbulak

    Tuesday, November 21, 2006 7:35 AM
  • Hello Senol,

    I think this document useful for you,....

    Regards..

    Tarkan...

    GTA

    Loss of the principal

    If the principal fails, the failover scenario depends on the transaction safety level and whether you have a witness.

    Scenario 1: Safety FULL with a witness

    This scenario provides the high safety with automatic failover. In the event of the failure of the principal, the mirror forms a quorum with the witness. Automatic failover will take place, thereby minimizing the database downtime.

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror, respectively. Server_A fails. Following the automatic failover, Server_B takes on the role of the principal. However, since there is no mirror after the failover, the mirroring state is DISCONNECTED and the principal is exposed. Once the database on Server_A becomes operational, it automatically assumes the role of the mirror.

    Scenario 2: Safety FULL without a witness

    This scenario provides high safety, but automatic failover is not allowed. In the event of failure of the principal, the database service becomes unavailable. You need manual intervention to make the database service available. You must break the mirroring session and then recover the mirror database.

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

    ALTER DATABASE <database name> SET PARTNER OFF

    RESTORE DATABASE <database name> WITH RECOVERY

    Once Server_A becomes available, you need to re-establish the mirroring session.

    Scenario 3: Safety OFF

    If the safety level is OFF, the witness doesn’t add any value to the database mirroring scenario. Therefore, it is recommended that if you plan to run database mirroring when the safety level is OFF, don’t configure a witness. In the event of failure of the principal, the database service becomes unavailable. You can perform a force service to make the database service available on the mirror. However, since the safety level is OFF, it is possible that there were transactions that didn’t make it to the mirror at the time of the failure of the principal. These transactions will be lost. Therefore, manual failover with safety OFF involves acknowledging the possibility of data loss.

    For example, prior to the failure, Server_A and Server_B acted as principal and mirror respectively. Server_A fails. You need to execute the following on Server_B to make the database service available:

    ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

    Once the database on Server_A becomes operational, it automatically assumes the role of the mirror. However, the mirroring session remains SUSPENDED, and you will need to manually RESUME the mirroring session.


     

    Another option in the event of failure of the principal, is to break the mirroring session and then recover the mirror database, as in Scenario 2: Safety FULL without a witness. Whether you choose to break the mirroring session or force service, you will lose the transactions that haven’t yet made it to the mirror at the time of failure.

    Tuesday, November 21, 2006 8:19 AM
  •  

    Thank you Tarkan.

    This answer is which I search for.

     

    Senol Akbulak

    Tuesday, November 21, 2006 8:28 AM
  • Hello Senol,

    It's sounds good...

    Regards..

    Tarkan G.

    GTA

    Tuesday, November 21, 2006 12:18 PM