locked
Unable to automatically failover to mirror server RRS feed

  • Question

  • Hi Folks,

     

    I have 3 standalone SQL 2005 Standard Edition SP2 servers acting as Principal, Mirror and Witness respectively.

     

    I configured mirroring using certificates and the Principal and Mirror work just fine and I can manually failover to the mirror and back successfully.

     

    I then added the witness and the Principal happily sees the witness. When I stop the SQL service on the principal to simulate a failure, the mirrored database shows 'Mirror, Disconnected / In Recovery', and if I try to select data from a table in the mirrored DB I get:

     

    Msg 955, Level 14, State 1, Line 1

    Database mirrortestdb is enabled for Database Mirroring, but neither the partner nor witness server instances are available: the database cannot be opened.

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'tblTestData'.

     

    I have ensured that each servers HOSTS file has the relevant entries and that each machine can ping the others by name as well as IP.

     

    When the principal is running, if I query the sys.database_mirroring catalog view on both the principal and mirror I get:

     

    Principal -

     

    mirroring_witness_state = 1

    mirroring_witness_state_desc = CONNECTED

     

    Mirror -

     

    mirroring_witness_state = 2

    mirroring_witness_state_desc = DISCONNECTED

     

    Is this correct? Should the mirror be connected to witness at all times?

     

    Any help would be much appreciated.

     

    Cheers

    Kev

     

    Thursday, October 9, 2008 12:44 PM

Answers

  • Gary,

     

    I finally got this working. I was initially using IP addresses in the ALTER DATABASE SET PARTNER/WITNESS statements. Using server names and making sure they were resolvable magically got it all working.

     

    Quite annoying really because the docs do say that IP addresses are perfectly acceptable:

     

    http://msdn.microsoft.com/en-us/library/ms189921(SQL.90).aspx

     

    I can also confirm that when querying 'sys.database_mirroring', the principal and mirror should report the witness as connected.

     

    Now I'm off to see if the Automatical Failover cleverness in the SQL Native Client actually works Smile

     

    Cheers

    Kev

     

    Saturday, October 11, 2008 2:41 AM

All replies

  • This description is EXACTLY the same as I am experiencing.  I am trying to get automatic failover to work but cannot though I can do it manually.  I also get the Disconnected when checking on the sys.database_mirroring catalog view on the mirror server.

     

    We have a dns server and all servers are listed there.  I can easily ping all servers by name (FQDN).

     

    I would also be very interested in finding out how to solve this problem.

     

    Gary

     

    Thursday, October 9, 2008 9:53 PM
  • Gary,

     

    I finally got this working. I was initially using IP addresses in the ALTER DATABASE SET PARTNER/WITNESS statements. Using server names and making sure they were resolvable magically got it all working.

     

    Quite annoying really because the docs do say that IP addresses are perfectly acceptable:

     

    http://msdn.microsoft.com/en-us/library/ms189921(SQL.90).aspx

     

    I can also confirm that when querying 'sys.database_mirroring', the principal and mirror should report the witness as connected.

     

    Now I'm off to see if the Automatical Failover cleverness in the SQL Native Client actually works Smile

     

    Cheers

    Kev

     

    Saturday, October 11, 2008 2:41 AM