SQL Server Performance

Multiple Instances on one Server; Mirror Connection Problem

Discussion in 'SQL Server 2005 Database Mirroring' started by bLumEi, Oct 23, 2007.

  1. bLumEi New Member

    Hello,
    I want to mirror on one Server with two named Instances, but I always get following Errormessage when I attempt to run the SET Partner Statement :
    ALTER DATABASE MirrorTest SET PARTNER = 'TCP://STAGE02.intnet.local:4009' --SAFETY FULL
    Msg 1418, Level 16, State 1, Line 1
    The server network address "TCP://STAGE02.intnet.local:4009" 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.
    I've tried a wide range of different things, to solve the problem :
    -Tried different Ports (4008, 4009)
    -Tried different Authorization Users (User with Administrator Rights on the Machine, User from a different domain (we have a delegation group which forwards the User from one Domain to another)
    -Tried to mirror another Database
    -Tried without Authorization (I think, for this case, the Sessionuser, which generates the Endpoint is used (in this special case my own User, and I have full Administrator rights too on this Server)
    -Tried different Authentication Modes (WINDOWS NTLM, NEGOTIATE)
    -Tried with Hostname-Entries in the drivers/etc/hosts - File 2 different full qualified Domainnames for every Instance with the same Ip, you will recognize this in the SET PARTNER Statement above; STAGE01.intnet.local is the First Entry, and STAGE02.intnet.local is the second entry in the hostsFile, both are using the same Ip-Adress)
    The Authorization Users, which are used were always ident on both of the Endpoints
    Following Messages occurs in the Errorlog :
    10/23/2007 09:53:50,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed). State 67.'. [CLIENT: 10.153.64.12]
    I've read in other Topics this is probably a Authorization-Problem, but I've tried different Users with different Rights on the SQL-Server and also on the Windows-Server itself.
    Question :
    When mirroring on one Server with multiple Instances, how is the traffic managed between the Instances?
    Do they use the TCP Protocol or Named Pipes because on one of the Instances Named Pipes are deactived, so if I want to activate, I have to restart the Service and I don't want to do this just for testing purposes.
    If the Endpoints are using TCP (and I suppose they do) , does this Traffic just go over the Loopback-address over the NIC, or does it go to the first network Hop (Router, Switch, whatever) and then return to the Server? and if the Traffic runs over 127.0.0.1, do I have to explicit declare this Adress on the Endpoints?
    Don't want to bug our Network-Team if the traffic just runs over LoopbackAdress.
    best Regards,
    Markus










  2. satya Moderator

    By default the communiction goes with enabled protocol, on that TCP/IP takes upper hand in communication when both TCP and Named pipes are enabled. In case of database mirroring TCP is only best way to go and takes the importance.
    In this case ensure you have used the domain account with proper privileges, which is SYSADMIN on SQL and relevant admin on the operating system. Also I have noticed that principal tries to connect to the mirror using 'NT AUTHORITYANONIMOUS ACCESS' (look at the errorlog file in C:programmiMicrosoft SQL ServerMSSQL.1MSSQLLOG), so I just added that user with sysadmin role and everything works fine at my end previously.
  3. bLumEi New Member

    Hi satya,
    Thank you for your fast answer, I've solved the problem.
    The problem was very different to that, what I was searching for.
    Murphys Law owned me ;)
    At first, the restore command for the Database on the Mirror-Instance was not successfully.
    So i tried to execute the SET PARTNER statement on the Mirror-Instance, which was not successfully, because the Database was in Restoring and not in norecovery Mode (I believe, you can't see the difference in the GUI of the databasestate)
    My Conclusion was, that I have to execute the SET PARTNER Statement at first on the Prinicpal Instance, which raised this Network-Error described above.

    Because of this facts, I used the same order of the SET PARTNER statement (At first Principal, then Mirror) which raised again this Network-Error.
    Thank you for your help, topic can be closed.

Share This Page