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