locked
Witness disconnected to Principal but connected to Mirror RRS feed

  • Question

  • Hi,

    Mirroring with witness  is configured in  prod environment 10 days ago and works fine. Today during monitoring i have  found that the witness is disconnected to principal but connected to mirror. 

    I have checked the mirroring end points and its works fine for the port 5022.

    Can any body tell me why is this happen and how can it come back.


    Monday, April 21, 2014 7:33 AM

Answers

  • Hi,

    According to the description, I know the witness disconnected from principal and from Mirror server, the witness is connected.

    1. Ping of FDQN from mirror/principal/witness and see if you can successfully ping all 3 servers.

    2. Check the error message from SQL error log on principal and witness server.

    3. When a witness has been set, the principal server requires a quorum with at least one other server to keep its database in service. If a principal cannot form a quorum, it can no longer serve the database. Located this information: http://technet.microsoft.com/en-us/library/ms191456.aspx

    If the witness becomes disconnected, we recommend that you set WITNESS OFF until the witness server instance becomes available

    Given that witness was disconnected, you may reset the witness from Principal using following command, test it and verify

    1. Connect to either partner and issue the following statement:

       ALTER DATABASE <database_name>  SET WITNESS OFF

    2. Connect to the principal server and issue the following statement:

    ALTER DATABASE <database_name> SET WITNESS =’TCP://<system-address>:<port>

    Remove the Witness from a Database Mirroring Session (SQL Server)

    http://technet.microsoft.com/en-us/library/ms190430.aspx

    Add a Database Mirroring Witness Using Windows Authentication (Transact-SQL)

    http://technet.microsoft.com/en-us/library/ms190430.aspx

    Hope it helps.

    Tracy Cai
    TechNet Community Support

    • Proposed as answer by Shanky_621MVP Tuesday, April 22, 2014 8:34 AM
    • Marked as answer by tracycai Tuesday, April 29, 2014 12:22 PM
    Tuesday, April 22, 2014 2:47 AM

All replies

    Check physical link between Mirroring Partner and Witness server.                  

    Initiate a manual mirroring session resume: alter database <dbname> set partner resume; on principal                                                                                                                 Make sure the SQL Agent is configured and running on the server. Then you can retrieve information using the sp_dbmmonitorresults procedure.

    Verify the error log for any connectivity related messages, on both sides.                                                                                                                                                                              Use SQL Profiler to monitor for events in the Audit Database Mirroring Login Event ClassDatabase Mirroring State Change Event Class and Broker: Connection Event Class, on principal to find the casus



    • Edited by Vanchan Monday, April 21, 2014 10:43 AM
    Monday, April 21, 2014 9:54 AM
  • Did you observed the SQL and event logs?

    Any communication link failures or any other mirror database warning logs. Please share, if any.

    Also, check below link seems to be similar issue:

    http://dba.stackexchange.com/questions/35592/database-mirroring-link-failure-between-principal-and-witness


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, April 21, 2014 1:01 PM
  • Hi,

    According to the description, I know the witness disconnected from principal and from Mirror server, the witness is connected.

    1. Ping of FDQN from mirror/principal/witness and see if you can successfully ping all 3 servers.

    2. Check the error message from SQL error log on principal and witness server.

    3. When a witness has been set, the principal server requires a quorum with at least one other server to keep its database in service. If a principal cannot form a quorum, it can no longer serve the database. Located this information: http://technet.microsoft.com/en-us/library/ms191456.aspx

    If the witness becomes disconnected, we recommend that you set WITNESS OFF until the witness server instance becomes available

    Given that witness was disconnected, you may reset the witness from Principal using following command, test it and verify

    1. Connect to either partner and issue the following statement:

       ALTER DATABASE <database_name>  SET WITNESS OFF

    2. Connect to the principal server and issue the following statement:

    ALTER DATABASE <database_name> SET WITNESS =’TCP://<system-address>:<port>

    Remove the Witness from a Database Mirroring Session (SQL Server)

    http://technet.microsoft.com/en-us/library/ms190430.aspx

    Add a Database Mirroring Witness Using Windows Authentication (Transact-SQL)

    http://technet.microsoft.com/en-us/library/ms190430.aspx

    Hope it helps.

    Tracy Cai
    TechNet Community Support

    • Proposed as answer by Shanky_621MVP Tuesday, April 22, 2014 8:34 AM
    • Marked as answer by tracycai Tuesday, April 29, 2014 12:22 PM
    Tuesday, April 22, 2014 2:47 AM
  • Sql server Error logs for  principal server are given below :-

    • Database mirroring connection error 2 'Connection attempt failed with error: '10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)'.' for witness server.
    • Database mirroring connection error 2 'DNS lookup failed with error: '11004(The requested name is valid, but no data of the requested type was found.)'.' for witness server.
    • Database mirroring connection error 4 '64(The specified network name is no longer available.)' for witness server

    But the witness server have no logs.

    Tuesday, April 22, 2014 1:20 PM
  • Thanks, Tracy Cai its really helpful to me.
    Tuesday, April 22, 2014 1:41 PM