locked
Messing with mirroring and recovery RRS feed

  • Question

  • I am doing db mirroring and recovery testing on SQL Svr 2008 R2. I set up mirroring with no witness, everything is fine.

    I shut down the principal's SQL service and corrupted the mdb file by randomly writing bytes to it.

    As expected, when the principal's service started again the db was suspect.

    I shut down the service again and on the mirror issued ALTER DATABASE SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS.

    The mirror became the principal, restarted the former principal which was still suspect.

    On the new principal I resumed mirroring, and the former principal then looked ok displaying mirror, synchronizing / restoring.  The roles looked reversed.

    Ok so thinking to restore the situation back to the way it was, from the new principal I resumed mirroring, and then did a forced fail over. This brought me back to a suspect principle and a errored mirror.

    From this, the only recover back to the original principle server that I can see is to:

    Take the failed principal offline
    Force the mirror to principal
    Delete the former principle's db
    Stop mirroring on the new principal
    Restore the former principle's db from a new backup of the former mirror
    Reestablish mirroring
    Do a forced fail over

    Is there an easier way?

    Looking to get a solid understanding, thanks in advance-

    Friday, February 3, 2012 1:32 AM

Answers

  • Hi,

    In a real scenario the if the original principal goes offline could be due to corruption or whatever if you have to force service with data loss on the mirror once the new principal is back online and available you would resume mirroring from the new principal to the old principal then once they were synced state rather than forcing service with data loss you would perform a manual failover to bring the original principal state mirror to principal.

    http://technet.microsoft.com/en-us/library/ms179481.aspx

    Therefore I think this should work in your scenario: -

    Take the failed orig princ offline

    Force service with data loss to make the mirror the princ and server the database

    Restore the orig princ server database so that it is online

    Resume mirroring to sync from the new princ to orig princ

    Monitor the re-sync until it is in a SYNCRONIZED state (which is different to syncing/restoring)

    Then manual failover from new princ to original princ


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Iric Wen Monday, February 6, 2012 7:16 AM
    • Marked as answer by Iric Wen Monday, March 5, 2012 10:03 AM
    Friday, February 3, 2012 2:01 AM

All replies

  • Hi,

    In a real scenario the if the original principal goes offline could be due to corruption or whatever if you have to force service with data loss on the mirror once the new principal is back online and available you would resume mirroring from the new principal to the old principal then once they were synced state rather than forcing service with data loss you would perform a manual failover to bring the original principal state mirror to principal.

    http://technet.microsoft.com/en-us/library/ms179481.aspx

    Therefore I think this should work in your scenario: -

    Take the failed orig princ offline

    Force service with data loss to make the mirror the princ and server the database

    Restore the orig princ server database so that it is online

    Resume mirroring to sync from the new princ to orig princ

    Monitor the re-sync until it is in a SYNCRONIZED state (which is different to syncing/restoring)

    Then manual failover from new princ to original princ


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Iric Wen Monday, February 6, 2012 7:16 AM
    • Marked as answer by Iric Wen Monday, March 5, 2012 10:03 AM
    Friday, February 3, 2012 2:01 AM
  • FAilover db onto mirroring server make principal (fallied one) as mirroring server then remove the mirroring on new principal then re-configure mirroring again from scratch that should resolve the issue
    http://uk.linkedin.com/in/ramjaddu
    Friday, February 3, 2012 9:57 AM