none
Error 1418 and Good Basic Setup Guide

    Question

  • Hi,

    I'm new to DB Mirroring, and I am trying to get it going in a test enviroment between two SQL 2005 Dev Servers. I have followed documentation that I have found but cannot get past a 1418 error when establishing a connection between the servers initially. Does anyone know of any good step by step guides that I could look at, in case I have missed something stupid.

     

    Thanks in advance

     

    Stupots

    Friday, June 16, 2006 3:03 PM

All replies

  • Hi,

    the easiest way is to run the sql services of all partners as local admins (domain user), then there should not be a great problem to establish a session. Furthermore make sure, that you mack a full backup and a transaction log backup what you both restore on the mirror. While establishing the mirroring session ensure that no further trans. log backup proceeds.

    Thats it. ;-)

    Torsten

    • Proposed as answer by vr.babu Tuesday, September 11, 2012 3:48 PM
    Monday, June 19, 2006 8:23 AM
  • Look at the troubleshooting section for setting up mirroring in BOL.

    Thanks,

    Mark

    Monday, June 19, 2006 3:21 PM
  • Great, thanks for that, I'm sure it's something simple that is stopping it working properly! I'll have another play.
    Tuesday, June 20, 2006 9:53 AM
  • Hi,

    Still having major problems with this.

    So far I have tried:

    1. Set my main database to 'FULL' recovery mode, with autoclose set to false.

    2.Backed the databse to my mirror server with NORECOVERY and left it in a restoring state.

    3. Used the following T-SQL on both servers to create my endpoints, using port 5022 on the principal and port 5023 on the mirror:

    create endpoint mirror

    state=started

    as tcp(listener_port = 5022)

    for database_mirroring (role =partner)

    4. I'm using a domain account on both servers, and I granted connect permissions to the endpoints using:

    grant connect

    on endpoint::mirror

    TO [DOMAIN\DOMAINUSER]

    Finally i try and initiate the mirror by running with differnt ports and server names on each server:

    alter database LR

    set partner = 'tcp://SERVERNAME.DOMAIN.COM:5022'

    the mirror server works fine, but the principal comes up with;

    Msg 1418, Level 16, State 1, Line 1

    The server network address "TCP://mirrorserver.domain.com:5023" 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.

    In the logs I also get

    2006-06-21 11:39:30.84 spid26s Error: 1443, Severity: 16, State: 2.

    2006-06-21 11:39:30.84 spid26s Database mirroring has been terminated for database 'LR'. This is an informational message only. No user action is required.

    I can telnet to both servers on 5022 and 5023, and see the ports listening if I do a netstat -abn.

    I also get this error if I use the security wizard on the database mirror page. Is there anything I may have missed or need to check, I think I'm gonna cry

    Thanks in advance

     

    Stu

     

    Wednesday, June 21, 2006 11:42 AM
  • Why you use differential ports? 5022 and 5023?

    You sets the partner connectionon 5022 for all partners (it looks like), but one of the partners endpoint on 5023? I see no requirement to do that...

    I use 5022 for all partners and witness, and it works fine!

    Torsten

    Wednesday, June 21, 2006 12:26 PM
  • It doesn't seem to matter if I use the same or different ports! I used different ports as I was getting another error saying the port was already in use. Shouldn't make a difference should it?
    Wednesday, June 21, 2006 1:16 PM
  • Yes, it shouldn't matter... There are a few things, that shouldn't matter - i've been crying a few times.

    Have you tried to use on all partner the same port (5023)? An by the way - why is 5022 used by what application? I would clarify these...

    Dont cry ;-) 

    Wednesday, June 21, 2006 1:21 PM
  • Thanks Torsten, I'm holding back the tears

    I managed to get a step closer, as it seems I had a problem with my dns servers. I still get the same error, but in the error log I get:


    2006-06-21 15:40:14.85 Logon       Error: 17836, Severity: 20, State: 1.
    2006-06-21 15:40:14.85 Logon       Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 192.168.1.48]
    2006-06-21 15:40:17.37 Logon       Error: 17836, Severity: 20, State: 1.
    2006-06-21 15:40:17.37 Logon       Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 192.168.1.48]
    2006-06-21 15:40:19.87 Logon       Error: 17836, Severity: 20, State: 1.
    2006-06-21 15:40:19.87 Logon       Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 192.168.1.48]

    Anyone seen this before?

    Thanks

    Stu

    Wednesday, June 21, 2006 4:20 PM
  • Try using certificates to connect and make sure that the server instance is listenening on port 5022 for mirroring by checking the event log.

    Here is some info

    Set the database recovery model for database mirroring

     

    • Select the database to be mirrored on the Principal Instance
    • Set recovery model by executing the following statement

     

    ALTER DATABASE [DATABASE] SET RECOVERY FULL.

     

    Alternatively, you can right click on the database, select properties, options, set recovery model to full and click OK.

     

    Create endpoints for database mirroring

     

    Use the following procedure to create and configure endpoints for certificate-based authentication

     

    • Create a database master key in the master database, as shown in the following TSQL example:

     

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MiP0wEr’

     

    • Create a certificate. For example, the following TSQL creates a certificate with subject “SQLServer1 mirroring certificate”:

     

    CREATE CERTIFICATE Sql1MirroringCert WITH SUBJECT = ‘SQLServer1 mirroring certificate’

     

    • Create an endpoint that uses the certificate for outbound authentication, as shown in the following TSQL example:

     

    CREATE ENDPOINT EPMirroring STATE = STARTED

                AS TCP (LISTENER_PORT = 6025)

                FOR DATABASE_MIRRORING (

                AUTHENTICATION = CERTIFICATE Sql1MirroringCert

                , ENCRYPTION = REQUIRED ALGORITHM RC4

                , ROLE = PARTNER ) [1]

     

    • Check the Event Log to make sure that SQL Server instance is listening to port 6025 (In this case) for database mirroring sessions.

     

    • Export the certificate and copy it securely to the other servers in the mirroring solution. The following TSQL shows how to export a certificate:

     

    BACKUP CERTIFICATE Sql1MirroringCert TO FILE = ‘C:\Sql1MirroringCert.Cer’

     

     

    • Create a login and a user for each remote server, and create a certificate for those users from the certificates copied from the remote servers, as shown in the following TSQL example:

     

    -- create a login for the remote server

                USE master

                CREATE LOGIN SqlServer2Lgn WITH PASSWORD = ‘MiP0wEr’

     

    -- create a user

                CREATE USER SqlServer2Usr FOR LOGIN SqlServerLgn

     

    -- create a certificate for the user

                CREATE CERTIFICATE Sql2MirroringCert AUTHORIZATION

    SqlServer2Usr FROM FILE = ‘C:\Sql2MirroringCert.Cer’

     

     

    • Grant the logins for the remote servers CONNECT permission on the endpoint, as shown in the following TSQL example:

     

    GRANT CONNECT ON ENDPOINT::EPMirroring TO SqlServer2Lgn

     

    Prepare the mirror database for a mirroring session

     

    Before a mirroring session can be initialized, the mirror database has to be prepared

     

    • Backup database on Principal Server. The following TSQL example can be used:

     

    BACKUP DATABASE [DATABASE]

    TO DISK = ‘C:\BACKUPS\DATABASE.BAK’

    WITH FORMAT

     

    • Restore the database on the Mirror server with NORECOVERY. You will need to copy the backup file to the mirror server and restore the database there as shown in the following TSQL example:

     

    RESTORE DATABASE [DATABASE] FROM

    DISK ‘C:\BACKUPS\DATABASE.BAK’

    WITH NORECOVERY

     

    Setting the NORECOVERY option is very important and this will place the Database in a Restoring state. The mirror database remains in restoring state during the mirroring session and continuously applies the transactions from the principal database’s transaction log.

     

    • Create server-level resources on Mirror Server. Database mirroring operates at the database level and sever-level resources such as logins, certificates, endpoints, a SQL Agent jobs are not mirrored. These resources will need to be duplicated on the mirror server to ensure continued operation in the event of a failover.

     

    Establish a mirroring session

     

    The order of the following steps is very important to establish a mirroring session.

     

    • Set Partner on mirror database. To initiate a mirroring session, start by setting the mirroring partner endpoint on the mirror database as shown in the following TSQL example:

     

    ALTER DATABASE [DATABASE] SET

    PARTNER = ‘TCP://WHITE:6025’

     

    • Set Partner on principal database

     

    ALTER DATABASE [DATABASE] SET

    PARTNER = ‘TCP://BIG-BLUE:6025’

     

    • Set Witness on principal or mirror

     

    ALTER DATABASE [DATABASE] SET

    PARTNER = ‘TCP://WITNESS:6025’

     

    • Check the event logs on all three servers to make sure that the database mirroring session is established on the selected databases.

     

    Manage a mirroring session

     

    • Configure Transaction Safety – Transaction safety level can be set to FULL (Transactions are forwarded synchronously) or OFF (Transactions are forwarded asynchronously).

     

    ALTER DATABASE [DATABASE] SET TRANSACTION

    SAFETY OFF

     

    The above statement will configure transaction safety to asynchronous mode.

     

    • Remove Witness

     

    ALTER DATABASE [DATABASE] SET WITNESS OFF

     

    • Manual Failover

     

    From principal database you can use the following TSQL statement:

     

    ALTER DATABASE [DATABASE] SET PARTNER FAILOVER

     

    If transaction safety is OFF, transactions are forwarded asynchronously and the mirror server remains online but in a restoring state in the event of a principal server failure. You can force restore on the mirror server, even though some transaction may have been lost, as shown in the following example:

     

    ALTER DATABASE [DATABASE] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS



    [1] The ROLE argument can be PARTNER, for a principal or mirror server; WITNESS, for a witness server; or ALL, for servers that will be both a partner (principal or mirror) and a witness server for another mirroring session

    Use thiese SQL statements for trouble shooting

    SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc, t.port, e.is_encryption_enabled, e.encryption_algorithm_desc, e.connection_auth_desc
    FROM   sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t ON     e.endpoint_id = t.endpoint_id

    SELECT d.name, d.database_id, m.mirroring_role_desc, m.mirroring_state_desc, m.mirroring_safety_level_desc, m.mirroring_partner_name, mirroring_partner_instance,       m.mirroring_witness_name, m.mirroring_witness_state_desc FROM   sys.database_mirroring m JOIN sys.databases d ON     m.database_id = d.database_id WHERE  mirroring_state_desc IS NOT NULL

    Wednesday, July 05, 2006 5:28 AM
  • Hi there,

    I was just wondering if the resolution posted by (harsh88) resolved the matter?  Also, are you working in a particular domain (i.e. Partners/Extranet, vs. Redmond or Phoenix)?

    Thanks,

    Angela

    Wednesday, July 12, 2006 10:00 PM
  • hey stupots

    hope you solved the mirroring problem. i had the problem once and it was because i forgot to restore the database on the mirror as 'non recovery' mode. after i changed that, the error (the same one you encountered) went away.  i spent so much time tried to trouble shoot tcp and listening port 5022 but the cuase was total unrelated to that. typical microsoft 'misleading' error message.

    Friday, August 18, 2006 6:29 PM
  • I am newbie to this and am getting quite confused, I have been following Harsh 88s post and it all makes sense to me except this part:

    • Create a login and a user for each remote server, and create a certificate for those users from the certificates copied from the remote servers, as shown in the following TSQL example:

     

    -- create a login for the remote server

                USE master

                CREATE LOGIN SqlServer2Lgn WITH PASSWORD = ‘MiP0wEr’

     

    -- create a user

                CREATE USER SqlServer2Usr FOR LOGIN SqlServerLgn

     

    -- create a certificate for the user

                CREATE CERTIFICATE Sql2MirroringCert AUTHORIZATION

    SqlServer2Usr FROM FILE = ‘C:\Sql2MirroringCert.Cer’

     

    Firstly I assume all the above is to be run on the primary server. Where does SqlServerLgn come from? Was Sql2MirroringCert.cer generated on the second SQL server? I know these are simple questions but this is the only part of the process I'm not clear on.

     

    Tuesday, November 21, 2006 10:31 AM
  • Hello,

    Please follow this steps..You can find sample codes and it is very useful

    http://msdn2.microsoft.com/en-us/library/ms191140.aspx

    Regards...

    Tarkan G.

    GTA

    Tuesday, November 21, 2006 11:33 AM
  • Thanks Tarkan, That guide is quite clear.

    I think I'm almost there now but am still having trouble making the Primary DB partner with the secondary DB (Secondary is in restore). I get this error:

    Msg 1478, Level 16, State 0, Line 1

    The mirror database, "Testing", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database.

    This is how I prepped the database:

    On Primary:

    ALTER DATABASE Testing SET RECOVERY FULL;

    BACKUP DATABASE Testing
    TO DISK = 'C:\Testing.bak'
    WITH FORMAT;

    *Copy File to C:\ On Secondary

    On Secondary:

    RESTORE DATABASE Testing FROM
    DISK = 'C:\Testing.bak'
    WITH NORECOVERY;

    On Secondary:

    ALTER DATABASE Testing 
        SET PARTNER = 'TCP://MsSql1.test.com:5022';
    GO


    On Primary:

    (Alter statement below causes the error)

    ALTER DATABASE Testing 
        SET PARTNER = 'TCP://MsSql2.test.com:5022';
    GO

    Logs on Primary give this info:

    2006-11-21 13:39:48.13 spid24s     Error: 1443, Severity: 16, State: 2.
    2006-11-21 13:39:48.13 spid24s     Database mirroring has been terminated for database 'Testing'. This is an informational message only. No user action is required.
    2006-11-21 13:41:30.34 spid24s     Error: 1474, Severity: 16, State: 1.
    2006-11-21 13:41:30.34 spid24s     Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://MsSql2.test.com:5022'.

    Logs on Secondary give this info:

    2006-11-21 13:36:12.65 spid53      Starting up database 'Testing'.
    2006-11-21 13:36:12.67 spid53      The database 'Testing' is marked RESTORING and is in a state that does not allow recovery to be run.
    2006-11-21 13:36:12.68 Backup      Database was restored: Database: Testing, creation date(time): 2006/11/21(13:03:20), first LSN: 18:426:37, last LSN: 18:442:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Testing.bak'}). Informational message. No user action required.
    2006-11-21 13:36:49.67 spid25s     Error: 1443, Severity: 16, State: 2.
    2006-11-21 13:36:49.67 spid25s     Database mirroring has been terminated for database 'Testing'. This is an informational message only. No user action is required.

     

    Do you know what might be causing the issue?

    Tuesday, November 21, 2006 2:02 PM
  • I've just noticed that the final error I get is surprisingly similar to the one you get in the thread you have started. Do you think there is something wrong with the instructions or a bug?
    Tuesday, November 21, 2006 2:25 PM
  • Hello Krober,

    I think your mirror database status has changed ( Restoring--->In Recovery) Please refresh mirror database

    then restore mirror database again but dont forget you must restore WITH NORECOVERY  option.

    Regards..

    Tarkan G.

    GTA

    Tuesday, November 21, 2006 3:23 PM
  • I did restore with the No RECOVERY option but this did not help.

    What I had to do was backup the log seperately and restore it:

    BACKUP LOG Testing
    TO DISK = 'C:\Testing.log'
    WITH FORMAT;

    RESTORE LOG Testing FROM
    DISK='C:/Testing.log' WITH NORECOVERY

     

    It Works Now!!!!!
    :) :)

    Wednesday, November 22, 2006 10:13 AM
  • Krobar, this answered my woes!! Thanks.

    Wednesday, October 05, 2011 7:06 AM
  • Please find the solution here...

     

    http://www.mssqlfix.com/2011/08/fix-microsoft-sql-server-error-1418.html

     


    Nareshkar Pakanati 

    MCTS, MCITP SQL Server 2008 Admin
    My Blog: www.mssqlfix.com
    Twitter: @mssqlfix
    -------------------------------------------------------
    Please mark the post as Answered if it answers your question.
    -------------------------------------------------------
    Wednesday, December 14, 2011 9:08 AM