locked
Principle, Disconnected; Mirror, Disconnected / In Recovery after network problem RRS feed

  • Question

  • Hi gurus,
    Three windows 2003 servers with following sql servers:
    Priciple: sql 2005 server
    Mirror: sql 2005 server
    Witness: sql 2005 server express

    All databases were fully mirrored (High safty with automatic failover) and has been working for about 2 years before a network problem.
    After the network problem got fixed, Principle is in Disconnected status; Mirror is in Disconnected / In Recovery status. Principle and witness are connected.

    1. All servers are up and nothing has been changed on those three servers.
    2. Can ping each other and telnet mirroring endpoint port on other servers (same firewall exception).
    3. Rebooted mirror and witness, nothing happened.
    4. Tried remove and reset mirroring on one user database, got 1418 error in the end.
    5. All servers in same network.

    Thanks.
    Wednesday, October 29, 2008 10:36 PM

All replies

  • For those that don't want to look it up, a 1418 error is: "The server network address "%.*ls" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational."

    Are you using fully qualified domain names (FQDN), or hostnames with no domains, or IP addresses?  I had a problem a few weeks ago that meant I had to add lines to my C:\Windows\System32\Drivers\etc\hosts file to map the hostnames of all three servers to an IP address, as FQDNs weren't an option.  
    Wednesday, October 29, 2008 11:17 PM
    Answerer
  • Thanks Jim.

    Yes, I am using hostnames with no domains. Added IPs and hostnames to hosts and lmhosts files in all three servers. Rebooted servers have Mirror and Witness. Nothing changed. The sever with Principle has not been rebooted so far.

    From Mirroring Monitor:
    Server  Current Role            Mirroring State         Witness Connection
    Srv1    Principle               Disconnected            Connected
    Srv2    Error retrieving data  
    Error retrieving data   Error retrieving data

    Run netstat on all three servers. On Mirror, it didn't show an established connection between Mirror endpoint port to Principle. However, on Principle, it did show a connection to Mirror endpoint port from Principle. Here are the results from netstat:

    On Principle:
      TCP    10.9.2.65:2711         10.9.2.68:30000        ESTABLISHED
      TCP    10.9.2.65:3027         10.9.2.69:30000        ESTABLISHED
      TCP    10.9.2.65:30000        10.9.2.68:1029         ESTABLISHED
      TCP    10.9.2.65:30000        10.9.2.69:1032         ESTABLISHED

    On Mirror:
      TCP    10.9.2.68:1029         10.9.2.65:30000        ESTABLISHED
      TCP    10.9.2.68:1030         10.9.2.69:30000        ESTABLISHED
      TCP    10.9.2.68:30000        10.9.2.69:1059         ESTABLISHED
        The connection to principle (
    10.9.2.65:2711) is missing here.

    On Witness:
      TCP    10.9.2.69:1032         10.9.2.65:30000        ESTABLISHED
      TCP    10.9.2.69:1059         10.9.2.68:30000        ESTABLISHED
      TCP    10.9.2.69:30000        10.9.2.65:3027         ESTABLISHED
      TCP    10.9.2.69:30000        10.9.2.68:1030         ESTABLISHED





    Thursday, October 30, 2008 5:42 PM
  • Wondering if any one was able to find a resolution to this problem? I currently am dealing with the same issue. No matter what I try I cannot recreate the mirror (keep getting the 1418 error) and the symptoms are exactly the same as posted above. Any help would be greatly appreciated. 

    Thanks!

    Tuesday, March 30, 2010 5:57 PM
  • What do you mean "recreate the mirror"? did you have it created and then removed it? I went through mirroring with every possible error you could get (pretty sure), and i compiled a document from a number of documents on the web. i can send this to you.
    Tony Auby
    Tuesday, June 14, 2011 4:22 AM
  • I have seen this problem time and again with SQL 2005 too. As far as I know it has been fixed in Service Pack 4, but please don't quote me on that, I am not using 2005 widely anymore. The solution to the problem in my case normally was to recycle the endpoint on the mirror (Do an ALTER ENDPOINT <name> STATE=STOPPED and then STATE=STARTED) Normally this lead to a reconnect. In some rare occasions the endpoint cycling led to a suspended state, but this can easily be fixed by an ALTER DATABASE <dbname> SET PARTNER RESUME.

    Hope it helps.

    Lucifer

    Wednesday, June 15, 2011 4:59 AM
  • Final Solution :-

    We have faced this problem earlier and very simple solution refer this as If mirror disconneted , it has to be synch automatically if not please follow below steps to recover mirror database with possible data loss which is minimal only.

     

    Please try Two Optio those will work perfactly.

    1) Reboot Principal Server  not the Mirror and Witness server because it will synch the seesion which are sending to mirror server in high safety Mode Transaction comitt on secondry first and then primary server and refresh the instance and check the status of databases , If this is Synch then no need to try below option.

    2) We need to apply Forced Service option

    a) Run below query on Mirror Server

    ALTER DATABASE BESMgmt SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS  

    Your Database recovered successfully and now configur mirroring again , remember you Database is connected with Application which is secondry server so try below steps to recover Principal again

    1) First make Mirror server as primary first

    2) Now Take Filover to Primary [ Which is working as Secondry currently ]

    3) Then chek status your all status will be ok .

     

    Friday, July 1, 2011 11:46 AM
  • Hi Moti,

    there is a possibility that you have a wrong auth method for SQL mirrors,

    1. run this tsql SELECT * FROM sys.database_mirroring_endpoints

    2. check the field connection_auth_method there should be value NEGOTIATE

    3. If there is KERBEROS DO

       a. DROP ENDPOINT "name of your endpoint"

       b. run this script, the PORT number you can chose by your self, dont forget to allow it on win FW

    CREATE ENDPOINT TEST
        STATE = STARTED
        AS TCP ( LISTENER_PORT = 5023)
        FOR DATABASE_MIRRORING (
           AUTHENTICATION = WINDOWS NEGOTIATE,
           ENCRYPTION = SUPPORTED,
           ROLE=ALL);
    GO

    Branislav


    Branislav Pastorek SEAL IT services

    Tuesday, November 6, 2012 11:09 PM
  • Restart all the MIrroring Endpoint


    Ramesh Babu Vavilla MCTS,MSBI

    Wednesday, November 7, 2012 5:37 AM
  • We had the same problem.

    The real solution was simple but effective :

    1) Manually failover the affected databases

    2) Check the mirror state on both principal and mirror environment.

    3) If ok (and it was ok) failover the affected databases back to the original principal.

    4) Check the mirror state on both principal and mirror environment. Everything ok again.

    It seems that a network problem causes this state to occur and that the failover/failback is the simple but effective solution.

    • Proposed as answer by Spriety Friday, October 4, 2013 8:03 AM
    Friday, October 4, 2013 8:03 AM