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

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?

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!

|||

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.

|||

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.

|||

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.

|||

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.

|||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...|||

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?

|||

I found that simply restarting SQL Server on the witness server solves this issue.

-dta

|||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