locked
Failover Scenarios.. RRS feed

  • Question

  • Hello guys !

    I am using SQL Server 2005 Enterprise Edition with SP3..
    I have two servers(Principal, Mirror), no witness server in between.
    I configured the mirroring in High Performance (Asynchronized) Operating mode, and everything is working fine.

    I have some questions regarding the failover process:

    Question # 1:

    What if the Principal Server just restarted? the mirroring session will be disconnected right? but when it comes back, it will remains\initialized automatically? or are there any additional steps needed to be done in order to make sure that the former principal server still functioning and the mirroring session is started again.

    Question # 2:

    The Service of the SQL Server of the principal server goes down for any reason, what will happen? what shall i do on the Mirror server to make it the new principal server? and when the former principal server come back up and running, what shall i do to make it the new mirror server?
    In this Scenario, shall i re-configure the mirroing from the scratch?

    Question # 3:

    A Hardware failure happened on the principal server; a RAM failure for example; what will happen?
    I can run the force service will data loss command on the mirror server, but when i run this command, the mirror server will be the new Principal server?
    No we fix the hardware failure on the former principal server, what shall i do to make it the new mirror server?
    Shall i configure the mirrirng from the scratch in case of any hardware failure?
    Or in other word, what is the scenario that i must re-configure the mirroring from the SCRATCH  (i.e take a full back up from the principal DB + a full backup for the trans log and restore both of them with NORECOVERY option, etc....)?

    And what is the scenario(s) that i have just to run the configure security button to crearte a new endpoints and listeners? i mean from the scratch..

    Thanks alot in advance guys.

    Have a wonderful day all..

    Best regards,
    ROY



    Tuesday, April 21, 2009 2:33 PM

All replies

  • Where are ya SQL Experts??  :(

    Saturday, April 25, 2009 7:59 AM
  • (1) Fine - no additional steps are required.
    (2) Initially the mirror's status will change from "Mirror, Synchronized" to "Mirror, Disconnected". To manually failover you can use
    ALTER DATABASE yourdbname SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
    No Need to set up from scratch.
    (3) Initially the same as (2). Once old principal is back available, the new principal will be in "Principal, Suspended" state. You can try to resume mirroring from this Principal and then swap roles if you like. If you try to resume, you are then faced with a choice - (a) recreate mirroring or (b) if there were transactions in the original princopal's log which were committed and not sent over, they are rolled back...
    HTH,
    Paul Ibison

    • Proposed as answer by Paul_Ibison Tuesday, April 28, 2009 8:43 AM
    Monday, April 27, 2009 3:29 PM