locked
Best Location for SQL Mirroring witness Server . RRS feed

  • Question

  • Hi,

    I have  implemented the SharePoint  2013 production site with DR. but still configurations are not completed  for mirroring. Now we are planning on configuring Mirror Production with DR SQL instances. Therefore I’m yet to decide whether it is best to have the witness server when only two locations are available. what is best location to host witness server?

    Thanks,

    I Priyankara




    Thursday, October 6, 2016 5:05 PM

Answers

  • Hi I S Priyankara,

    The witness is an optional instance of SQL Server that enables the mirror server in a high-safety mode session to recognize whether to initiate an automatic failover. Unlike the two partners, the witness does not serve the database. Supporting automatic failover is the only role of the witness.

    Whether to use witness server depends on your requirement, there are three Database Mirroring Operating Modes for selection, they are Asynchronous Database Mirroring (High-Performance Mode), Synchronous Database Mirroring (High-Safety Mode), High-Safety Mode with Automatic Failover. The first and third mode need witness server. If we choose Synchronous Database Mirroring (High-Safety Mode), we can create it without witness server. For more information, please review this article.

    Besides, the witness server can set either on the machine setting principle server or the machine setting mirror server.

    Best Regards,
    Teige
    Friday, October 7, 2016 8:44 AM
  • It  also depends on the critical business for your applications more ever- in simple see here-

    as per BOL:-

    The witness should be set only when you intend to use high-safety mode with automatic failover. In high-performance mode, for which a witness is never required, we strongly recommend setting the WITNESS property to OFF. For information about how a witness affects database availability in a high-performance mode session, see Asynchronous Database Mirroring (High-Performance Mode).

    ref-https://technet.microsoft.com/en-us/library/ms189902(v=sql.105).aspx

    so first you should understand your environments,critical along with noted outage too.

    this witness is acts like a Quorum in "high-safety mode with automatic failover", so it ensures that at any point of a time one should own role of principle encase if any issues.


    Regards, S_NO "_"

    Friday, October 7, 2016 9:07 AM

All replies

  • You should keep the witness server in the same site as the principal database. So in your situation a witness for server A in NY and a witness for server B in MX. The main reason for this is your second scenario would halt transactions on your principal.

    From the mirroring best practices:

    Loss of the mirror and the witness

    Assume you have configured database mirroring with a witness. When the mirror is unavailable, the principal runs exposed. While the mirror is unavailable, if the witness is also lost, the principal becomes isolated and can’t service the clients. Even though the principal database is running, it is not available to the clients. If you attempt to connect to the database, you get the message “Database <dbname> is enabled for database mirroring, but neither the partner nor witness server instances are available: the database cannot be opened.”

    http://www.sqlservercentral.com/Forums/Topic1099063-1549-1.aspx

    https://msdn.microsoft.com/en-us/library/ms175191.aspx


    Please click Mark As Answer if my post helped.

    Thursday, October 6, 2016 7:17 PM
  • Hi I S Priyankara,

    The witness is an optional instance of SQL Server that enables the mirror server in a high-safety mode session to recognize whether to initiate an automatic failover. Unlike the two partners, the witness does not serve the database. Supporting automatic failover is the only role of the witness.

    Whether to use witness server depends on your requirement, there are three Database Mirroring Operating Modes for selection, they are Asynchronous Database Mirroring (High-Performance Mode), Synchronous Database Mirroring (High-Safety Mode), High-Safety Mode with Automatic Failover. The first and third mode need witness server. If we choose Synchronous Database Mirroring (High-Safety Mode), we can create it without witness server. For more information, please review this article.

    Besides, the witness server can set either on the machine setting principle server or the machine setting mirror server.

    Best Regards,
    Teige
    Friday, October 7, 2016 8:44 AM
  • It  also depends on the critical business for your applications more ever- in simple see here-

    as per BOL:-

    The witness should be set only when you intend to use high-safety mode with automatic failover. In high-performance mode, for which a witness is never required, we strongly recommend setting the WITNESS property to OFF. For information about how a witness affects database availability in a high-performance mode session, see Asynchronous Database Mirroring (High-Performance Mode).

    ref-https://technet.microsoft.com/en-us/library/ms189902(v=sql.105).aspx

    so first you should understand your environments,critical along with noted outage too.

    this witness is acts like a Quorum in "high-safety mode with automatic failover", so it ensures that at any point of a time one should own role of principle encase if any issues.


    Regards, S_NO "_"

    Friday, October 7, 2016 9:07 AM