locked
SQL Server 2005 Mirroring: witness server role on a separate server or located on the same server ? RRS feed

  • Question

  • Hello All !

    I have a SQL Server 2005 installation (US , x64, Enterprise Edition, SP3) for mirroring. We have 2 servers:

    - SRV1 is the principal and SRV2 is the mirror

    On SRV1, we have an application which needs 3 SQL Server instances (1 DB per instance) mirrored with asynchronous mode : ok

    DBA customer team wants to add a new witness role for a new application (which uses high-safety full synchronous mode with witness)  without to add a new server for the witness

    Is it possible to do this ?

    If yes, how to do it ?

    If we try to add a new SQL Server instance for the witness role, on (the mirrored server ?), it doesn't function ( I don't remember the exact message) or on the principal server ?


     


    Thanks for advance for your ideas / help - Regards - Have a nice day ! RHUM2
    Tuesday, May 31, 2011 5:01 PM

Answers

  • It makes no sense to put the witness on either the principal or the mirror.  If it is on the principal if you pull the plug on the principal you lose the witness and the principal and no failover will happen.  If you put it on the mirror and you pull the plug on the mirror the principal will come offline as it can no longer speak to the witness of the mirror.  You need to put the witness on a 3rd server regardless of whether or are ABLE to get it to work on one of the existing nodes.
    David A. Bermingham, MVP Senior Technical Evangelist, SIOS Technology Corp
    Tuesday, May 31, 2011 6:17 PM
  • If you lose a node and a witness (as is likely when they are running on the same server) you will have no failover if it was on the principal and even worse if it was on the mirror I believe your active server will come offline as it cannot speak to the witness or the mirror!  So...while it may be POSSIBLE I would reconsider that design.
    David A. Bermingham, MVP Senior Technical Evangelist, SIOS Technology Corp
    Tuesday, May 31, 2011 7:41 PM

All replies

  • It makes no sense to put the witness on either the principal or the mirror.  If it is on the principal if you pull the plug on the principal you lose the witness and the principal and no failover will happen.  If you put it on the mirror and you pull the plug on the mirror the principal will come offline as it can no longer speak to the witness of the mirror.  You need to put the witness on a 3rd server regardless of whether or are ABLE to get it to work on one of the existing nodes.
    David A. Bermingham, MVP Senior Technical Evangelist, SIOS Technology Corp
    Tuesday, May 31, 2011 6:17 PM
  • Hello David,

    Thanks for your response, but why Microsoft says this:

    http://msdn.microsoft.com/en-us/library/ms190430%28v=SQL.90%29.aspx

    To set up a witness for a database, the database owner assigns a Database Engine instance to the role of witness server. The witness server instance can run on the same computer as the principal or mirror server instance, but this substantially reduces the robustness of automatic failover.

    I try to do it and it runs ok, I agree with it's not very secure (servers have many powersupplies, so "pull unplug"..... if instance down, it can run no ?

     

     


    Thanks for advance for your ideas / help - Regards - Have a nice day ! RHUM2
    Tuesday, May 31, 2011 7:06 PM
  • If you lose a node and a witness (as is likely when they are running on the same server) you will have no failover if it was on the principal and even worse if it was on the mirror I believe your active server will come offline as it cannot speak to the witness or the mirror!  So...while it may be POSSIBLE I would reconsider that design.
    David A. Bermingham, MVP Senior Technical Evangelist, SIOS Technology Corp
    Tuesday, May 31, 2011 7:41 PM