locked
Newly created witness server only connects to one of the partners? RRS feed

  • Question

  • We've been running a mirrored database (using certificates since we don't have a domain) and it's all working well. Last week we decided to add a witness for automatic failovers, but for some reason I just can not get the witness to connect to the Partner2 server.

     

    See screenshot here

     

     

    Please help me troubleshoot this - I re-created the endpoints / users / certificates but it's still not working. Where can I get more information on what exactly the problem is? Can I test the endpoints somehow?

    Monday, April 2, 2007 4:06 AM

All replies

  • Hello. I started from scratch on a test environment and I get the exact same problem. On the mirror I get that Witness Connection status as Disconnected.

    I don't use certificates and also no encrypton as in this tutorial: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

    The direct result is that automatic failover does not work.

     

    Some help will be great!

    Monday, April 2, 2007 7:59 PM
  • I have the exact problem. I don't use certificates and encryption is off as suggested in this article: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

    I get status disconnected and the automatic failover does not work.

     

    Monday, April 2, 2007 8:03 PM
  • I have the exact problem. I don't use certificates and encryption is off as suggested in this article: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

    I get status disconnected and the automatic failover does not work.

     

    Monday, April 2, 2007 8:18 PM
  • I managed to make it work, but my solution is really convoluted and made me feel really uneasy:

     

    - Setup a HOSTS file entry on Partner1 and Partner2 to point the witness IP address to the name WITNESS:

     

    10.1.0.123 WITNESS

     

    - Add the witness to the DB using the HOSTS entry (in TSQL)

     

    alter database MyDatabase set witness = 'tcp://WITNESS:5022'

     

    - Check mirroring monitor. Monitor confirms that the winess is only connected to Partner1

    - Change the HOSTS entry on Partner1 to the wrong IP address for WITNESS:

     

    0.0.0.0 WITNESS

     

    - Restart the SQL Service on the Witness server. After the restart, mirroring monitor shows that the witness is now connected to Partner2, and not to Partner1

    - Fix the HOSTS entry on Partner1 to the correct IP address again

    - Wait 10 mins

    - Check mirroring monitor - it now shows the witness connected to both servers! (if it's still not connected to Partner1, restart the witness again)

     

    What? This makes no sense, but it worked for me.

    • Proposed as answer by XtendedLearner Wednesday, April 6, 2016 4:19 PM
    Tuesday, April 3, 2007 12:19 AM
  • You know what. I followed your instructions step by step and indeed it works. But this is not a way I declare myself satisfied either...

    It makes no sense... I wonder how anyone was able to make this work as it seems that this is a clear bug.

    Tuesday, April 3, 2007 2:37 AM
  • If someone wants the step by step to reproduce this I can tell it. It's a fairly basic process, following the instructions and still it doesn't work...
    Tuesday, April 3, 2007 1:04 PM
  • Anyway just to let you know I downloaded from Microsoft their SQL2005 in VHD (virtual hard drive) format and ran it under virtual server. And I tried step by step and it's giving the exact problem... there is something wrong there for sure.

    No one willing to comment on this so far?

    Tuesday, April 3, 2007 6:49 PM
  • I found that simply restarting SQL Server on the witness server solves this issue.

     

      -dta

    Wednesday, May 23, 2007 7:57 PM
  • how exactlly do you do that?  add entry to the hosts file?  be exact please, i'm new and not very good with t-sql
    Tuesday, May 29, 2007 8:13 PM