locked
Loss of Principal and Witness Server RRS feed

  • Question

  • Loss of Principal and Witness Server

    We have mirroring setup so that we have a Principal and Witness Server in one site and the Mirror at another site.

    We are simulating a site failure by disabling the NICs on the Witness and Principal servers in that order.

    The databases on the Mirror remain unavailble.

    I brought these database online by issuing the following commands:

    Alter database <database_name> set partner off

    Restore database <database_name> with recovery.

    This brings the databases online but breaks the mirroring sessions.

    Is there a way to bring these databases online without breaking the mirroring sessions?

    I ask because we have set up mirroring for SharePoint and when I bring the databases on the mirror online using the above commands I get the following error in the SharePoint log:

    “[powershell] [SPUpgradeSession] [ERROR] [DD/MM/YY HH:MM:SS]: Exception: Server dralias\instance, database ConfigDB is not configured for database mirroring.”

    Or do I need to change the way SharePoint is configured?

    Thanks.

    Friday, April 20, 2012 2:39 AM

Answers

  • Hi,

    Yes, if you are using high-safety mode WITH automatic failover then if you try use FORCE_SERVICE_ALLOW_DATA_LOSS you will likely get an error like:

    'Msg 1455, Level 16, State 106, Line 1
    The database mirroring service cannot be forced for database "databasename" because the database is not in the correct state to become the principal database.'

    If you try remove the witness e.g. ALTER DATABASE databasename SET WITNESS OFF, then you'll likely receive an error like:
     
    'Msg 1431, Level 16, State 4, Line 1
    Neither the partner nor the witness server instance for database "databasename" is available. Reissue the command when at least one of the instances becomes available.'

    So it looks like you cannot work around the FORCE_SERVICE_ALLOW_DATA_LOSS and the options are to either bring the witness or principal back online or remove mirroring on the mirror e.g. ALTER DATABASE databasename SET PARTNER OFF and then recover the database e.g. RESTORE DATABASE databasename WITH RECOVERY and then re-establish mirroring when the environment is ready again.

    • Marked as answer by charocpc Wednesday, April 25, 2012 11:38 PM
    Friday, April 20, 2012 7:48 AM

All replies

  • Is there a way to bring these databases online without breaking the mirroring sessions?

    Hi,

    This link should help http://technet.microsoft.com/en-us/library/ms189270.aspx

    '...forcing service facilitates resynchronizing the databases (with possible data loss) when mirroring resumes.'

    Friday, April 20, 2012 3:10 AM
  • Hi,

    Once you lose the witness you lose the automatic failover. Are you certain that the ConfigDB is configured for mirroring? Can you run this script to check the mirroring status of the databases on the instance?

    SELECT a.name, 
    CASE
        WHEN B.mirroring_state is NULL THEN 'Mirroring not configured'
        ELSE 'Mirroring configured'
    END as MirroringState 
    FROM
    sys.databases A 
    INNER JOIN sys.database_mirroring B 
    ON A.database_id=B.database_id 
    WHERE a.database_id > 4 
    ORDER BY A.NAME 
    -- Read more: http://www.sqldbadiaries.com/2011/02/17/script-to-check-if-the-databases-are-configured-for-mirroring/#ixzz1sXzXxs53


    Sean Massey | Consultant, iUNITE

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

    Friday, April 20, 2012 3:24 AM
  • Thanks Kevin for your reply.

    That link (http://technet.microsoft.com/en-us/library/ms189270.aspx) indicates that this is for when you have "high-performance mode and high-safety mode without automatic failover" but we have high-safety mode WITH automatic failover. It also mentions that "This option is available only under all the following conditions:

    The principal server is down.

    WITNESS is set to OFF or is connected to the mirror server."

    I guess I could try setting WITNESS to OFF on the mirror and then issuing:

    ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

    Thanks again.


    Regards, Charocpc

    Friday, April 20, 2012 3:24 AM
  • Sean, thanks for your reply,

    I am certain the ConfigDB was configured for mirroring as in our DR Test prior to testing for site loss we tested for loss of the Primary. This database became the Primary on what was the mirror previously.

    It seems SharePoint objected to the database not being mirrored.


    Regards, Charocpc

    Friday, April 20, 2012 3:32 AM
  • Hi,

    Yes, if you are using high-safety mode WITH automatic failover then if you try use FORCE_SERVICE_ALLOW_DATA_LOSS you will likely get an error like:

    'Msg 1455, Level 16, State 106, Line 1
    The database mirroring service cannot be forced for database "databasename" because the database is not in the correct state to become the principal database.'

    If you try remove the witness e.g. ALTER DATABASE databasename SET WITNESS OFF, then you'll likely receive an error like:
     
    'Msg 1431, Level 16, State 4, Line 1
    Neither the partner nor the witness server instance for database "databasename" is available. Reissue the command when at least one of the instances becomes available.'

    So it looks like you cannot work around the FORCE_SERVICE_ALLOW_DATA_LOSS and the options are to either bring the witness or principal back online or remove mirroring on the mirror e.g. ALTER DATABASE databasename SET PARTNER OFF and then recover the database e.g. RESTORE DATABASE databasename WITH RECOVERY and then re-establish mirroring when the environment is ready again.

    • Marked as answer by charocpc Wednesday, April 25, 2012 11:38 PM
    Friday, April 20, 2012 7:48 AM
  • Once you have issued the SET PARTNER OFF the mirror is broken and you are gone. To bring the DB online in your scenario the simple command is:

    ALTER DATABASE <databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

    With this you force the DB online without breaking the mirror. Assuming that your principle did not get any new requests during this (best way to ensure that is to also cut the NIC connections between principle and witness at the same time...) you can resume the mirror after your connection has been brought up again by issuing

    ALTER DATABASE <databasename> SET PARTNER RESUME.

    Your sharepoint error message will also be gone by using this method by the way.

    Hope it helps

    Lucifer

    Friday, April 20, 2012 8:42 AM