locked
Still need help with mirroring (Error 1418) RRS feed

  • Question

  • Hello again,

    This is further to my previous post, which has had over thirty viewing but as yet no replies.  Is there really no solution to this problem?

    I have now given up trying to use the wizard to get mirroring running, as although I'm not entirely sure, some stuff I've read implies that the wizard only works using Windows authentication.  Maybe someone can confirm this.  Either way, the wizard doesn't work for me (see my previous post).

    So instead of using the wizard I've now tried to set up mirroring manually using SQL statements.  Following are the steps I've taken.  I've tried to replicated exactly what it says in the online documentation.  At the end of the post are the SQL statements issued.

    1. Enable encrypted outbound connections on the primary server
    2. Enable encrypted outbound connections on the mirror server
    3. Enable encrypted outbound connections on the witness server
    4. Enable encrypted inbound connections on the primary server
    5. Enable encrypted inbound connections on the mirror server
    6. Enable encrypted inbound connections on the witness server
    7. Set mirror's partner to the primary
    8. Set primary's partner the mirror (EXPLOSION)

    There might be more stuff to do after this, but here is where it breaks down.  Again, the error is the same as before when using the wizard:

    Msg 1418, Level 16, State 1, Line 1
    The server network address "TCP://10.152.58.243:7024" can not be reached or does not exist. Check the network address name and reissue the command.

    Which is **INCORRECT** or at least highly unhelpful because:
    i) netstat -abn shows the sql server process listening on port 7024; no other processes are listening on this port
    ii) I can telnet to port 7024 on this machine and issue the 16 keystrokes

    Please, someone help, I am crying tears of despair.  SQL below.

    Cheers,

    Mike




    /*
    The following is a complete list of the SQL statements issued.
    Please assume they are issued on the relevant master databases.
    */



    /* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */

    DROP ENDPOINT Mirroring
    GO
    DROP CERTIFICATE BILL_PRIMARY_CERT
    GO
    DROP MASTER KEY
    GO

    CREATE MASTER KEY
      ENCRYPTION BY PASSWORD = '**************'  -- real password used
    GO

    CREATE CERTIFICATE BILL_PRIMARY_CERT
       WITH SUBJECT = 'BILL_PRIMARY_CERT for database mirroring',
       START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
    GO

    CREATE ENDPOINT Mirroring
       STATE = STARTED
       AS TCP (
          LISTENER_PORT=7024
          , LISTENER_IP = ALL
       )
       FOR DATABASE_MIRRORING (
          AUTHENTICATION = CERTIFICATE BILL_PRIMARY_CERT
          , ENCRYPTION = REQUIRED ALGORITHM AES
          , ROLE = ALL
       )
    GO

    BACKUP CERTIFICATE BILL_PRIMARY_CERT
      TO FILE = 'C:\certificates\BILL_PRIMARY_CERT.cer'
    GO

    -- then copy certificate to other two machines




    /* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */

    DROP ENDPOINT Mirroring
    GO
    DROP CERTIFICATE BILL_SECONDARY_CERT
    GO
    DROP MASTER KEY
    GO

    CREATE MASTER KEY
      ENCRYPTION BY PASSWORD = '**************'  -- real password used
    GO

    CREATE CERTIFICATE BILL_SECONDARY_CERT
       WITH SUBJECT = 'BILL_SECONDARY_CERT for database mirroring',
       START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
    GO

    CREATE ENDPOINT Mirroring
       STATE = STARTED
       AS TCP (
          LISTENER_PORT=7024
          , LISTENER_IP = ALL
       )
       FOR DATABASE_MIRRORING (
          AUTHENTICATION = CERTIFICATE BILL_SECONDARY_CERT
          , ENCRYPTION = REQUIRED ALGORITHM AES
          , ROLE = ALL
       )
    GO

    BACKUP CERTIFICATE BILL_SECONDARY_CERT
      TO FILE = 'C:\certificates\BILL_SECONDARY_CERT.cer'
    GO

    -- then copy certificate to other two machines




    /* -------- 3. ENABLE OUTBOUND CONNECTIONS ON THE WINTESS -------- */


    DROP ENDPOINT Mirroring
    GO
    DROP CERTIFICATE BILL_WITNESS_CERT
    GO
    DROP MASTER KEY
    GO

    CREATE MASTER KEY
      ENCRYPTION BY PASSWORD = '**************'  -- real password used
    GO

    CREATE CERTIFICATE BILL_WITNESS_CERT
       WITH SUBJECT = 'BILL_WITNESS_CERT for database mirroring',
       START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
    GO

    CREATE ENDPOINT Mirroring
       STATE = STARTED
       AS TCP (
          LISTENER_PORT=7024
          , LISTENER_IP = ALL
       )
       FOR DATABASE_MIRRORING (
          AUTHENTICATION = CERTIFICATE BILL_WITNESS_CERT
          , ENCRYPTION = REQUIRED ALGORITHM AES
          , ROLE = ALL
       )
    GO

    BACKUP CERTIFICATE BILL_WITNESS_CERT
      TO FILE = 'C:\certificates\BILL_WITNESS_CERT.cer'
    GO

    -- then copy certificate to other two machines




    /* -------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY -------- */

    /* enable inbound from the mirror */

    DROP CERTIFICATE BILL_SECONDARY_CERT
    GO
    DROP USER MIRROR_SECONDARY_USER
    GO
    DROP LOGIN MIRROR_SECONDARY_LOGIN
    GO

    CREATE LOGIN MIRROR_SECONDARY_LOGIN
      WITH PASSWORD = '****************'  -- real password used
    GO

    CREATE USER MIRROR_SECONDARY_USER
      FOR LOGIN MIRROR_SECONDARY_LOGIN
    GO

    CREATE CERTIFICATE BILL_SECONDARY_CERT
      AUTHORIZATION MIRROR_SECONDARY_USER
      FROM FILE = 'c:\certificates\BILL_SECONDARY_CERT.cer'
    GO

    GRANT CONNECT ON ENDPOINT::Mirroring
      TO MIRROR_SECONDARY_LOGIN
    GO

    /* enable inbound from the witness */


    DROP CERTIFICATE BILL_WITNESS_CERT
    GO
    DROP USER MIRROR_WITNESS_USER
    GO
    DROP LOGIN MIRROR_WITNESS_LOGIN
    GO

    CREATE LOGIN MIRROR_WITNESS_LOGIN
      WITH PASSWORD = '****************'  -- real password used
    GO

    CREATE USER MIRROR_WITNESS_USER
      FOR LOGIN MIRROR_WITNESS_LOGIN
    GO

    CREATE CERTIFICATE BILL_WITNESS_CERT
      AUTHORIZATION MIRROR_WITNESS_USER
      FROM FILE = 'c:\certificates\BILL_WITNESS_CERT.cer'
    GO

    GRANT CONNECT ON ENDPOINT::Mirroring
      TO MIRROR_WITNESS_LOGIN
    GO




    /* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */

    /* enable inbound from the primary */

    DROP CERTIFICATE BILL_PRIMARY_CERT
    GO
    DROP USER MIRROR_PRIMARY_USER
    GO
    DROP LOGIN MIRROR_PRIMARY_LOGIN
    GO

    CREATE LOGIN MIRROR_PRIMARY_LOGIN
      WITH PASSWORD = '****************'  -- real password used
    GO

    CREATE USER MIRROR_PRIMARY_USER
      FOR LOGIN MIRROR_PRIMARY_LOGIN
    GO

    CREATE CERTIFICATE BILL_PRIMARY_CERT
      AUTHORIZATION MIRROR_PRIMARY_USER
      FROM FILE = 'c:\certificates\BILL_PRIMARY_CERT.cer'
    GO

    GRANT CONNECT ON ENDPOINT::Mirroring
      TO MIRROR_PRIMARY_LOGIN
    GO

    /* enable inbound from the witness */

    DROP CERTIFICATE BILL_WITNESS_CERT
    GO
    DROP USER MIRROR_WITNESS_USER
    GO
    DROP LOGIN MIRROR_WITNESS_LOGIN
    GO

    CREATE LOGIN MIRROR_WITNESS_LOGIN
      WITH PASSWORD = '****************'  -- real password used
    GO

    CREATE USER MIRROR_WITNESS_USER
      FOR LOGIN MIRROR_WITNESS_LOGIN
    GO

    CREATE CERTIFICATE BILL_WITNESS_CERT
      AUTHORIZATION MIRROR_WITNESS_USER
      FROM FILE = 'c:\certificates\BILL_WITNESS_CERT.cer'
    GO

    GRANT CONNECT ON ENDPOINT::Mirroring
      TO MIRROR_WITNESS_LOGIN
    GO




    /* -------- 6. ENABLE INBOUND CONNECTIONS ON THE WITNESS -------- */

    /* enable inbound from the mirror */

    DROP CERTIFICATE BILL_SECONDARY_CERT
    GO
    DROP USER MIRROR_SECONDARY_USER
    GO
    DROP LOGIN MIRROR_SECONDARY_LOGIN
    GO

    CREATE LOGIN MIRROR_SECONDARY_LOGIN
      WITH PASSWORD = '****************'  -- real password used
    GO

    CREATE USER MIRROR_SECONDARY_USER
      FOR LOGIN MIRROR_SECONDARY_LOGIN
    GO

    CREATE CERTIFICATE BILL_SECONDARY_CERT
      AUTHORIZATION MIRROR_SECONDARY_USER
      FROM FILE = 'c:\certificates\BILL_SECONDARY_CERT.cer'
    GO

    GRANT CONNECT ON ENDPOINT::Mirroring
      TO MIRROR_SECONDARY_LOGIN
    GO

    /* enable inbound from the primary */

    DROP CERTIFICATE BILL_PRIMARY_CERT
    GO
    DROP USER MIRROR_PRIMARY_USER
    GO
    DROP LOGIN MIRROR_PRIMARY_LOGIN
    GO

    CREATE LOGIN MIRROR_PRIMARY_LOGIN
      WITH PASSWORD = '****************'  -- real password used
    GO

    CREATE USER MIRROR_PRIMARY_USER
      FOR LOGIN MIRROR_PRIMARY_LOGIN
    GO

    CREATE CERTIFICATE BILL_PRIMARY_CERT
      AUTHORIZATION MIRROR_PRIMARY_USER
      FROM FILE = 'c:\certificates\BILL_PRIMARY_CERT.cer'
    GO

    GRANT CONNECT ON ENDPOINT::Mirroring
      TO MIRROR_PRIMARY_LOGIN
    GO




    /* -------- 7. SET MIRROR'S PARTNER TO THE PRIMARY SERVER -------- */

    ALTER DATABASE failover_test
      SET PARTNER OFF
    GO

    ALTER DATABASE failover_test
      SET PARTNER = 'TCP://10.152.58.242:7024';
    GO



    /* -------- 8. SET PRIMARY'S PARTNER TO THE MIRROR SERVER -------- */

    ALTER DATABASE failover_test
      SET PARTNER OFF
    GO

    ALTER DATABASE failover_test
      SET PARTNER = 'TCP://10.152.58.243:7024';
    GO


    /*
    Response:
    Msg 1418, Level 16, State 1, Line 1
    The server network address "TCP://10.152.58.243:7024" can not be reached or does not exist. Check the network address name and reissue the command.
    */
    Friday, March 17, 2006 3:19 AM

Answers

  • I haven't heard anything, so I am going to mark this as answered...

    Hope you got it working...

    Thanks,

    Mark

    Thursday, April 27, 2006 8:38 PM

All replies

  • If you could post the errorlog (s) from both the principal and mirror that cover the time period when you execute the commands, that would be most helpful.

    Also, if you could run the sql service as a domain SA account, that would make the security setup easier.

    Meanwhile, I'm going to restart my local SQL server process as Local System and try to get your scripts working.

    Thanks,

    Mark

    Tuesday, April 18, 2006 6:30 PM
  • OK,

    Can you telnet from ALL the servers to ALL the other servers? A network connection for database mirroring requires both servers to login in both directions.

    We did run into a problem where windows was grabbing some ports around 5000 for its own use but was not reporting them as in use.  That's why some of the examples use 7000, etc.

    Now I changed my SQL processes to run as Local System, and I ran this from the principal:

    (NOTE: You have to run some of the commands in order on both the Pincipal and Mirror)

    CREATE DATABASE BILLSDB

    GO

    BACKUP DATABASE BILLSDB TO DISK = '\\MWISTROM5\SHARED\BILLSDB.BAK' WITH INIT

    GO

    /* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */

    DROP ENDPOINT Mirroring

    GO

    DROP CERTIFICATE BILL_PRIMARY_CERT

    GO

    DROP MASTER KEY

    GO

    CREATE MASTER KEY

    ENCRYPTION BY PASSWORD = 'Password1234567890' -- real password used

    GO

    CREATE CERTIFICATE BILL_PRIMARY_CERT

    WITH SUBJECT = 'BILL_PRIMARY_CERT for database mirroring',

    START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'

    GO

    SELECT * FROM sys.tcp_endpoints

    DROP ENDPOINT Endpoint_Mirroring

    CREATE ENDPOINT Mirroring

    STATE = STARTED

    AS TCP (

    LISTENER_PORT=7024

    , LISTENER_IP = ALL

    )

    FOR DATABASE_MIRRORING (

    AUTHENTICATION = CERTIFICATE BILL_PRIMARY_CERT

    , ENCRYPTION = REQUIRED ALGORITHM AES

    , ROLE = ALL

    )

    GO

    BACKUP CERTIFICATE BILL_PRIMARY_CERT

    TO FILE = 'C:\certificates\BILL_PRIMARY_CERT.cer'

    GO

    -- then copy certificate to other two machines

     

     

     

    /* -------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY -------- */

    /* enable inbound from the mirror */

    DROP CERTIFICATE BILL_SECONDARY_CERT

    GO

    DROP USER MIRROR_SECONDARY_USER

    GO

    DROP LOGIN MIRROR_SECONDARY_LOGIN

    GO

    CREATE LOGIN MIRROR_SECONDARY_LOGIN

    WITH PASSWORD = 'Password1234567890' -- real password used

    GO

    CREATE USER MIRROR_SECONDARY_USER

    FOR LOGIN MIRROR_SECONDARY_LOGIN

    GO

    CREATE CERTIFICATE BILL_SECONDARY_CERT

    AUTHORIZATION MIRROR_SECONDARY_USER

    FROM FILE = 'c:\certificates\BILL_SECONDARY_CERT.cer'

    GO

    GRANT CONNECT ON ENDPOINT::Mirroring

    TO MIRROR_SECONDARY_LOGIN

    GO

    /* enable inbound from the witness */

    /* -------- 8. SET PRIMARY'S PARTNER TO THE MIRROR SERVER -------- */

    ALTER DATABASE failover_test

    SET PARTNER OFF

    GO

    ALTER DATABASE BILLSDB

    SET PARTNER = 'TCP://mwistrom5:7024';

    GO

     

     

    And then I ran this on the mirror:

    RESTORE DATABASE BILLSDB FROM DISK = 'C:\SHARED\BILLSDB.BAK' WITH NORECOVERY

    GO

     

    /* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */

    DROP ENDPOINT Mirroring

    GO

    DROP CERTIFICATE BILL_SECONDARY_CERT

    GO

    DROP MASTER KEY

    GO

    CREATE MASTER KEY

    ENCRYPTION BY PASSWORD = 'Password1234567890' -- real password used

    GO

    CREATE CERTIFICATE BILL_SECONDARY_CERT

    WITH SUBJECT = 'BILL_SECONDARY_CERT for database mirroring',

    START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'

    GO

    SELECT * FROM sys.tcp_endpoints

    DROP ENDPOINT Endpoint_Mirroring

    CREATE ENDPOINT Mirroring

    STATE = STARTED

    AS TCP (

    LISTENER_PORT=7024

    , LISTENER_IP = ALL

    )

    FOR DATABASE_MIRRORING (

    AUTHENTICATION = CERTIFICATE BILL_SECONDARY_CERT

    , ENCRYPTION = REQUIRED ALGORITHM AES

    , ROLE = ALL

    )

    GO

    BACKUP CERTIFICATE BILL_SECONDARY_CERT

    TO FILE = 'C:\certificates\BILL_SECONDARY_CERT.cer'

    GO

    -- then copy certificate to other two machines

     

     

     

    /* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */

    /* enable inbound from the primary */

    DROP CERTIFICATE BILL_PRIMARY_CERT

    GO

    DROP USER MIRROR_PRIMARY_USER

    GO

    DROP LOGIN MIRROR_PRIMARY_LOGIN

    GO

    CREATE LOGIN MIRROR_PRIMARY_LOGIN

    WITH PASSWORD = 'Password1234567890' -- real password used

    GO

    CREATE USER MIRROR_PRIMARY_USER

    FOR LOGIN MIRROR_PRIMARY_LOGIN

    GO

    CREATE CERTIFICATE BILL_PRIMARY_CERT

    AUTHORIZATION MIRROR_PRIMARY_USER

    FROM FILE = 'c:\certificates\BILL_PRIMARY_CERT.cer'

    GO

    GRANT CONNECT ON ENDPOINT::Mirroring

    TO MIRROR_PRIMARY_LOGIN

    GO

     

     

     

    /* -------- 7. SET MIRROR'S PARTNER TO THE PRIMARY SERVER -------- */

    ALTER DATABASE failover_test

    SET PARTNER OFF

    GO

    ALTER DATABASE BILLSDB

    SET PARTNER = 'TCP://mwistrom1:7024';

    GO

     

    and it worked fine.

     

    OK, the best thing that we can do now is send me the errorlogs so that I can look into them.

    Thanks,

    Mark

    Tuesday, April 18, 2006 7:57 PM
  • I haven't heard anything, so I am going to mark this as answered...

    Hope you got it working...

    Thanks,

    Mark

    Thursday, April 27, 2006 8:38 PM
  • hi there.

    i'm having de same problem. but de quorum i setup is without witness and certificate.

    its a straightforward database mirroring with two servers - principal and mirror.

    had setup the endpoints, checked port specified listening and

    from the mirror server, i can set partner to de principal server from de mirror,

    but not de other way round.

    i got de same error msg when i try to set partner to the mirror server, from principal.

    any ideas?

    thanks in advance.

    mchart

    Friday, May 5, 2006 3:57 AM
  • Looks at the trouble shooting section in BOL.

    Attach the errorlogs from the session to help further debugging.

    Thanks,

    Mark

    Monday, May 8, 2006 8:32 PM