locked
Database Mirroring - Witness or No Witness RRS feed

  • Question

  • Hi,

    I'm using SQL Server 2008 r2 mirroring in our SharePoint 2010 environment.  I'm trying to weigh up the benefits of whether I should use a witness server or not.  We are using a stretched farm design.  2 Hyper V cores in different datacentres (dc) about 1/2 mile apart.  On each core is a WFE, APP and DB server. 

    If I use a witness server, which core should I place it on?

    What is the best approach for SQL Backups? (If I go for a witness server, both the SQL servers will require backups as I cant be certain where the principal DB's will be when the backup is run)

    Any advice and opinions would be appreciated.

    Cammie

    Friday, June 28, 2013 10:02 AM

Answers

  • My recommendations:

    • wittness server is not necessary for the mirroring session but if you want to handle failovers automatically you have to use it
    • place the whitness server to an independent machine from both cores
    • wittness server can be Express Edition (free)
    • set up a backup job for the Prinipal and the Mirror server also and the backups will run always automatically on Principal and on Mirror will fail until a failover

    m@te

    • Proposed as answer by farkas.mate Friday, June 28, 2013 11:58 AM
    • Marked as answer by Fanny Liu Tuesday, July 9, 2013 1:37 AM
    Friday, June 28, 2013 11:58 AM
  • Hello,   

    Just as others post above, Automatic failover is supported only in database mirroring sessions running with a witness in high-safety mode.
    Manual failover requires high-safety mode. The partners must be connected to each other, and the database must already be synchronized. For example, if your database mirroring is set to asynchronous (High Performance mode), we will need to synchronize the databases first:
    ALTER DATABASE databasename SET SAFETY FULL

    And then perform the manual failover using the following statement on the principal server for each database:
    ALTER DATABASE databasename SET PARTNER FAILOVER

    (Note:Manual failover can be initiated only from the principal server.)

    Reference:http://msdn.microsoft.com/en-us/library/ms189850.aspx#ManualFailover

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support



    • Edited by Fanny Liu Tuesday, July 2, 2013 8:29 AM typo
    • Marked as answer by Fanny Liu Tuesday, July 9, 2013 1:37 AM
    Tuesday, July 2, 2013 8:28 AM
  • Hello,

    If we go for high-safety without a witness and can only perform a manual failover from the principal server, then what happens if your principal server fails.  Is there no recovery methods if you only have a mirror server available.

    Regards

    Cammie


    There is a recovery method but it will require manual intervention. It won't be automatic
    • Proposed as answer by Fanny Liu Thursday, July 4, 2013 1:24 AM
    • Marked as answer by Fanny Liu Tuesday, July 9, 2013 1:37 AM
    Tuesday, July 2, 2013 2:10 PM

All replies

  • Hi Cammie

    You should place your witness on a seperate server. Remember that your witness won't actually have a copy of your database. The witness just facilitates automatic failover.

    Friday, June 28, 2013 10:59 AM
  • My recommendations:

    • wittness server is not necessary for the mirroring session but if you want to handle failovers automatically you have to use it
    • place the whitness server to an independent machine from both cores
    • wittness server can be Express Edition (free)
    • set up a backup job for the Prinipal and the Mirror server also and the backups will run always automatically on Principal and on Mirror will fail until a failover

    m@te

    • Proposed as answer by farkas.mate Friday, June 28, 2013 11:58 AM
    • Marked as answer by Fanny Liu Tuesday, July 9, 2013 1:37 AM
    Friday, June 28, 2013 11:58 AM
  • Thanks for the responses.

    OK, if I decide to opt for a no witness setup and handle failovers manually is the following correct?

    • I'll only need to backup the Principal databases
    • If the Principal fails, I run sql commands on the Mirror to swap the roles
    • Once the old Principal is back up and running I can run similar sql commands to swap the roles back
    • If SharePoint is setup with a failover database, it should continue to run throughout

    Thanks for your help with this.

    Tuesday, July 2, 2013 7:41 AM
  • Hello,   

    Just as others post above, Automatic failover is supported only in database mirroring sessions running with a witness in high-safety mode.
    Manual failover requires high-safety mode. The partners must be connected to each other, and the database must already be synchronized. For example, if your database mirroring is set to asynchronous (High Performance mode), we will need to synchronize the databases first:
    ALTER DATABASE databasename SET SAFETY FULL

    And then perform the manual failover using the following statement on the principal server for each database:
    ALTER DATABASE databasename SET PARTNER FAILOVER

    (Note:Manual failover can be initiated only from the principal server.)

    Reference:http://msdn.microsoft.com/en-us/library/ms189850.aspx#ManualFailover

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support



    • Edited by Fanny Liu Tuesday, July 2, 2013 8:29 AM typo
    • Marked as answer by Fanny Liu Tuesday, July 9, 2013 1:37 AM
    Tuesday, July 2, 2013 8:28 AM
  • Hello,

    If we go for high-safety without a witness and can only perform a manual failover from the principal server, then what happens if your principal server fails.  Is there no recovery methods if you only have a mirror server available.

    Regards

    Cammie

    Tuesday, July 2, 2013 10:45 AM
  • Hello,

    If we go for high-safety without a witness and can only perform a manual failover from the principal server, then what happens if your principal server fails.  Is there no recovery methods if you only have a mirror server available.

    Regards

    Cammie


    There is a recovery method but it will require manual intervention. It won't be automatic
    • Proposed as answer by Fanny Liu Thursday, July 4, 2013 1:24 AM
    • Marked as answer by Fanny Liu Tuesday, July 9, 2013 1:37 AM
    Tuesday, July 2, 2013 2:10 PM