Problem with Automatic Failover RRS feed

  • Question

  • I have a SQL Server 2005 mirrored database with a witness.  Each instance is running on a different machine.  I've had it running for some time thinking all was working well until recently when the mirror server went down.  I could no longer access my database.  Once I got the computer back up and running, I started doing some testing.  I found that if I disconnected either the Principal or the Mirror, I could no longer connect.  Also, when I disconnected the Principal, it would not failover to the Mirror.  After a lot of trial and error, it appears for some reason when you disconnect the Principal or mirror, the one that is still connected looses connection with the witness.  When I go to the server that is still connected in SQL management studio, it shows the database as disconnected/recovering on that server.  When I select mirroring on that database on that server, I get an error message saying that this database is enabled for mirroring but neither the partner or witness are available.  I can't understand why they can no longer connect to the witness.  Anyone have any suggestions for me?

    Thanks Scott    
    Sunday, July 5, 2009 7:37 PM

All replies

  • I wanted to follow this up with some more details.  After much more trial and error, I have found that the witness always shows up as disconnected to the mirror in the mirror monitoring.  I have varied which computer is the principal, mirror, and witness.  No matter what configuration I try, the withess always shows up as disconnected from the mirrror.

    Thanks Scott
    Monday, July 6, 2009 2:51 PM
  • So it allows set up without error, but not use of the witness.  Could be a permissions issue with the endpoint security.  When setting it up you set it up under your context and then endpoints may be set to use another account.  May want to try moving the endpoints to using nt authority system or local admin and see if that helps.  As always make sure UAC is off.
    Monday, July 6, 2009 4:21 PM
  • Zelocka,

    Thanks for the response.  I found something on another forum where someone was having a similiar issue.  Their solution was to reboot the witness.  I tried that and now it all works normally. 

    Monday, July 6, 2009 4:58 PM