locked
Basic Mirroring Setup Help RRS feed

  • Question

  • After going through the Enterprise Manage mirroring setup, I get the error message ...

    The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist.  Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)

    I have three instances of SQL Server 2005 Enterprise Edtion
    MSSQLSERVER (primary) endpoint 5022
    MSSQLSERVER (partner) endpoint 5023
    MSSQLSERVER (witness) endpoint 5024

    I have enabled TCP\IP and named pipes on all three protocols.
    In surface configuration I have enabled everything.
    Each instance has the -T1400 flag set.
    Each instance is running under mixed security.
    Each sql Server and sql agent instance runs exactly in the same domain account.
    The machine has been rebooted.
    The following tests all pass

    telnet MYMACH.mynet.net 5022
    telnet MYMACH.mynet.net 5023
    telnet MYMACH.mynet.net 5024


    This problem happens both with Enterprise Edition and Developer Edition.

    Any ideas?

    AIM

    Wednesday, April 5, 2006 2:58 PM

Answers

  • So, to initialize the mirror, you must do a full backup of the database on the princpal, restore the full backup to the mirror with NO_RECOVERY, and then usually (there are some cases where you do not have to do another backup or restore, but they are not normal operating scenarios) take a log backup and restore it to the mirror with NO_RECOVERY. You must also restore any other log backups that are taken on the principal to the mirror.

    Thanks,

    Mark

    Thursday, April 13, 2006 5:47 PM

All replies

  • Hi,

    Thank you for providing informtaion into the problem that occurred. It is very difficult to diagnose some of these problems with so little information.

    My first guess is security, but lets start from the top.

    I assume that you have all three instances on the same machine, correct?  That should work just fine. And you have setup the endpoints to listen on the different ports, which is correct. Now you said that you can telnet to the different ports, I'm assuming that you did it from the same machine. If you can try the telnet test again from a different machine that would be good, but not neccessary. Should be fine.

    If this is the recent SP1 CTP build, http://www.microsoft.com/sql/ctp_sp1.mspx, then you do not need the 1400 trace flag (it is just a noop now). Should be fine.

    Now the account that the instances are running under?  What type of account is it?  Can we make it a Sys Admin account on all three instances? Look in the errorlog for the instances. If you see anything along the lines of "denied access" in there?

    Also, one last thing; you state that the error message is:

    "The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist.  Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

    If you want the principal to listen on the 5022 port, then you should have gotten an error saying "... MYMACH.mynet.net:5023..."  Can you go back and check your setup?

    Thanks,

    Mark

    Wednesday, April 5, 2006 6:12 PM
  • O.K.

    FIRST

    All three instances are running under a Windows Account that is an Adminstrator on the machine.  This has always been this way.

    SECOND

    I said:

    "The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist.  Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

    I meant to say:

    "The server network address "TCP://MYMACH.mynet.net:5023" can not be reached or does not exist.  Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

    O.K.  WHAT NEXT SHOULD I TRY?

    THANKS.

    AIMDBA

     

    Monday, April 10, 2006 2:54 PM
  • Can you verify that the Windows account that the instances are running as are "SQL Server System Administrators?"  Thanks.

    If you could attach the error logs during the time that you tried to setup database mirroring, that would help a lot.  Grab the error logs from both the principal and mirror instance. By default these are located at:

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

    Get the "errorlog" that contains the time at which you issued the start mirroring command.

    At this point, I see two approaches.

    1. posting the errorlogs so that I can look at them.

    2. trying to setup mirroring just using t-sql. There are some examples in BOL.

    Let me know how things are going.

    Thanks,

    Mark

    Monday, April 10, 2006 7:20 PM
  • I'm getting the same error here. Below is my sql server error (machines are on different IP's) and below this is my error log.

    An error occurred while starting mirroring.

    ------------------------------
    ADDITIONAL INFORMATION:

    Alter failed for Database 'UOP_GSO'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2040.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    The server network address "TCP://BOGEYWRKSTATION.svc.nt.edu:5022" 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. (Microsoft SQL Server, Error: 1418)

     

    2006-04-12 16:48:01.75 spid51      Using 'xpstar90.dll' version '2005.90.2040' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
    2006-04-12 16:48:02.55 spid12s     The Service Broker protocol transport is disabled or not configured.
    2006-04-12 16:48:02.57 spid12s     Server is listening on [ 'any' <ipv4> 5022].
    2006-04-12 16:48:02.57 spid12s     The Database Mirroring protocol transport is now listening for connections.
    2006-04-12 16:48:02.77 spid12s     Service Broker manager has started.
    2006-04-12 16:48:04.49 spid5s      Recovery is complete. This is an informational message only. No user action is required.

     

     

     

     

     

     

    Wednesday, April 12, 2006 8:53 PM
  • First thing to do is to go to the "Troubleshooting Database Mirroring Setup" topic in BOL. It was designed to solve this first basic question of "why do I get error 1418?"

    The snippette of the errorlog that you posted indicates that the database mirroring endpoint was started at one point, but that doesn't tell me much.

    If you could post the part of the errorlog around the time you got the 1418 error, that would tell me much more. It is generally one of two things.

    1. Network error where we tried to open the port and for some reason the network said we failed.

    2. Security problem where we connected to the port but for some reason, our security credentials were not correct.

    Thanks,

    Mark

    Wednesday, April 12, 2006 9:45 PM
  • Got it up and running.  Had nothing to do with the specific error that I was receiving but I had not restored the failover correctly. 

     

    thanks

    Thursday, April 13, 2006 1:58 PM
  • Bogey1,

    What did method did you use, (what did you do) to do the correct failover restore?

    AIMDBA

    Thursday, April 13, 2006 4:34 PM
  • Two seperate machines on the network, but still in the same domain.  Running domain accounts for sql server.  I did a backup of my primary and applied that to my mirror, but I kept getting the error my mirror is not in sync.  So I just restored my backup to my primary and started the mirroring.  Works quite well.
    Thursday, April 13, 2006 5:08 PM
  • So, to initialize the mirror, you must do a full backup of the database on the princpal, restore the full backup to the mirror with NO_RECOVERY, and then usually (there are some cases where you do not have to do another backup or restore, but they are not normal operating scenarios) take a log backup and restore it to the mirror with NO_RECOVERY. You must also restore any other log backups that are taken on the principal to the mirror.

    Thanks,

    Mark

    Thursday, April 13, 2006 5:47 PM
  • Rather that starting an entirely new thread, I figured I would post my question here since my issues are stemming from the next point of the mirror creation process.

    Basically I take my full principal backup and then run the restore with no recovery to the mirror, once the restore is underway and then complete, according to my sql logs, I have the DBname (Restoring) indicator in management studio. Low and behold this message can be there for countless hours and in this scenario the database is unalvailable for use. Any ideas on what could be causing this?

    Thanks
    Monday, May 1, 2006 7:02 PM
  • This is correct behavior. Doing any type of restore with NO_RECOVERY leaves the database in an unrecovered state.

    Continue with setting up database mirroring.

    Thanks,

    Mark

    Monday, May 1, 2006 8:16 PM
  • I'm facing the same issue with error 1418. I have 3 VM servers ( hosted in the same host machine) that host each an instance of SQL server. I'm using a domain account with admin right on each server to run SQL server and my database mirroring. I have granted System admin right to that account and full right on all sql folder. Telnet test is successfull. after the mirroring failed, there is no failure audit in the security audit log. Everything seems to be setup properly. I have added the service account in all group created by SQL Server during the installation. But I'm still receiving this error.

    In another network, where the servers are not VM Ware server but real physical machine I do not have any problem The mirroring is working fine. But with the VMWare server , I have the problem. Is this problem  linked to  the virtual machine architecture? Why I don't have the problem with physical machine?

     

    Thursday, August 9, 2007 12:27 AM
  • I have the above problems too….

    After try and error and some readings, i fix the problem……

    Hope this help everyone:

    http://alan328.com/SQL2005_Database_Mirroring_Tutorial.aspx

     

    Saturday, November 10, 2007 4:18 AM
  • I am setting up a Database mirroring: I have two servers in a domain installed with same version (9.00.3200)

     

    Here are the steps i followed:

     

    ***********************************************************

     

    --Creating End Point on Principal server

     

    CREATE ENDPOINT Endpoint_Mirroring

        STATE=STARTED

        AS TCP (LISTENER_PORT=5022)

        FOR DATABASE_MIRRORING (ROLE=ALL)

    GO

     

     

    --Creating End Point on Mirror Server 

    CREATE ENDPOINT Endpoint_Mirroring

        STATE=STARTED

        AS TCP (LISTENER_PORT=5022)

        FOR DATABASE_MIRRORING (ROLE=ALL)

    GO

     

    -- Backing up Database on Principal Srever (FULL BACKUP)

     

    BACKUP DATABASE SME_DB_COPY

        TO DISK = 'H:\SME_DB_COPY_FULL_200807140810.bak'

        WITH FORMAT

    GO

     

     

    -- Restoring the Database on Mirror Srever (FULL restore)

     

     

    RESTORE DATABASE SME_DB_COPY

        FROM DISK = 'H:\SME_DB_COPY_FULL_200807140810.bak'

        WITH NORECOVERY

    GO

     

    -- Log Backup on Principal server 

    BACKUP LOG SME_DB_COPY

        TO DISK = 'H:\SME_DB_COPY_LOG_200807140810.trn'

        WITH FORMAT

    GO

     

     

    -- Log Restore on Mirror Server 

    RESTORE LOG SME_DB_COPY

        FROM DISK = 'H:\SME_DB_COPY_LOG_200807140810.trn'

        WITH NORECOVERY

    GO

     

    -- Set the Partner on Mirror server

     

    ALTER DATABASE SME_DB_COPY

        SET PARTNER =

        'TCP://PrincipalSrever.domain.com:5022'

    GO

     

     

    -- Set the Partner on DBSW9021 (Principal server )

     

    ALTER DATABASE SME_DB_COPY

        SET PARTNER =

        'TCP://MirrorSrever.domain.com:5022'

    GO

     

    ****************************************************************************

     

    Got the following eror while executing the

    'ALTER DATABASE SME_DB_COPY SET PARTNER = 'TCP://MirrorSrever.domain.com:5022'

     

    Msg 1418, Level 16, State 1, Line 1
    The server network address "TCP://MirrorSrever.domain.com:5022" 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.

     


    ******Message from Principal Server SQL Error Log:

    Message
    Error: 1443, Severity: 16, State: 2.
    Database mirroring has been terminated for database 'SME_DB_Copy'. This is an informational message only. No user action is required.

     

    *****Here is the Event Viewer log from the Mirror Server:
    See the below error on Mirror server Event Viewer:
    An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')

    SQL Error Log:

    Message
    Error: 9642, Severity: 16, State: 3.

     

     

    Can anyone help me?

     

     

    Monday, July 14, 2008 3:30 PM
  • Hi 

    i was also getting the same error.Now i've resolved.

    Do the following steps to resolve the issue..

    1 . Enable TCP on your SQL server configuration manager

    2. Check your firewall (That means sql port is open or not)

    3. Telnet the port (which is shown in Principal& mirror text box)  from Primary to secondary.

    4.check your sql authentication.

     If your ports are reachable means the problem with your sql authentication.If you are using windows authentication,please add that user to sql login.


    -Thanks 
    Arun karthikeyan

    Monday, October 6, 2008 7:31 AM
  • hai
    How to restore the database with norecovery

    I tried by using the query but it is showing restoring with database name but not restoring..

    show me how to restore tyhe  database with norecovery and how to do the mirroring 

    thanks
    neelan
    Wednesday, January 13, 2010 10:37 AM
  • Hi,

    I am trying to set up database mirroring between two servers. My environment has SQL Server 2005 Standard Edition and has service pack 2 on both servers. SQL Server Service runs under Local System. I get the following error.

    TITLE: Database Properties

    ------------------------------

     

    An error occurred while starting mirroring.

     

    ------------------------------

    ADDITIONAL INFORMATION:

     

    Alter failed for Database 'MirrorDatabase'.  (Microsoft.SqlServer.Smo)

     

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

     

    ------------------------------

     

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

     

    ------------------------------

     

    The server network address "TCP://TestServer.X.ad:5022" cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

     

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

     

    ------------------------------

    BUTTONS:

     

    OK

    Please share your things if any one has come accorss this issue and resolved it. Thanks for the help.

     

    I checked following things:

    - TCP\IP and named pipes are enabled on both Principal and Mirror.

    - I will be able to ping the mirror server from the prilcipal server.

    - Endpoints are started.

    - Restored full backup and transaction log backup with NO RECOVERY option.

    I am still getting the same error. Any help on this issue would be great!!!

     

    Thanks

    Monday, June 28, 2010 2:41 PM