locked
Using Certificates, I cannot start mirroring from Primary to secondary at end of install RRS feed

  • Question

  • Please someone who has installed mirroring USING CERTIFICATES respond to this.

    I am installing mirroring using certificates for authentication

    I am following the MSDN article found at https://msdn.microsoft.com/en-us/library/ms191140(v=sql.120).aspx

    I am able to start mirroring from the secondary server, next to the last step.

    The last step says to start a mirroring session on the primary server to the secondary server

    The database on the primary server is NOT in restoring mode as it is the live database.

    I get the error message "The server network address "TCP://LLDESQL1:7024" can not be reached or does not exist...

    I can elicit the same error from the secondary server by taking the database out of restoring mode.

    AM I actually done and the second mirror will be started by the fail over?


    Jim Hodges @ CMI

    Friday, June 17, 2016 4:28 PM

All replies

  • Hello Jim

    Please follow the steps as i have created mirroring with certificates

    Database Mirroring
    Prerequisites:
     Port 5022 should be enabled on both Production and DR.
     Ping and telnet (5022) should respond from Production to DR and vice versa.
     Both servers should be in domain, if not then Certificate authentication should be used.
     The service account for SQL Server on both servers should run under the same account and that account should be a domain account else create Logins and associate certificates to it.
     The database should be in Full recovery mode.
    Initial Configuration:
     As new P2P link was setup between Prod and DR, new IPs got added on both sides.
     Added the new IP (192.168.7.20) on SQL Server instance on DR.
     Added the new Clustered IP (192.168.6.25) on the Clustered instance on Production.
     After the addition of IPs on Prod and DR on SQL Instances, the SQL Instance would now be listening on two different IPs on both Prod and DR.
     Checking the Ping response from Prod to new DR IP and from DR to new Prod clustered IP.
    Step 1 :
    Restore the Full backup on DR in norecovery mode.
    restore database DBNAME from disk='BACKUPPATH\FILENAME.bak' with replace, norecovery
    Step 2 : Copy the latest Differential backup from Production to DR.
    Step 3 :
    Restore the latest Differential backup on DR that was copied.
    restore database DBNAME from disk='BACKUPPATH\FILENAME.bak' with replace, norecovery
    Step 4 :
    Copy all the T-log backup after the latest differential backup on DR.
    Step 5 :
    Restore all the T-log backup on DR sequentially.
    restore log DBNAME from disk='BACKUPPATH\FILENAME.trn' with norecovery
    Configuring Mirroring:
    As the production and DR are in different domains, certificate based authentication is used for setting up mirroring.
    Configuring Outbound Connections
    To configure Principal for outbound connections
    Step 1 :
    On the master database, create the database master key,
    USE MASTER
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'reliance@123'
    GO
    Step 2 :
    Make a certificate for this server instance.
    CREATE CERTIFICATE PrincipalServerCertificate
    WITH SUBJECT = 'PrincipalServer certificate',
    START_DATE = '20110601',
    EXPIRY_DATE = '20500101'
    GO
    Step 3 :
    Create a mirroring endpoint for server instance using the certificate.
    CREATE ENDPOINT MirroringEndPoint
    STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE PrincipalServerCertificate
    ,ENCRYPTION = REQUIRED ALGORITHM RC4,ROLE = ALL)
    GO
    Step 4 :
    Back up the Principal certificate, and copy it to other system, DR
    BACKUP CERTIFICATE PrincipalServerCertificate
    TO FILE = 'L:\Certificate\PrincipalServerCertificate.cer'
    GO
    Step 5 :
    Using any secure copy method, copy 'L:\Certificate\PrincipalServerCertificate.cer'
    to DR
    To configure DR for outbound connections
    Step 6 :
    On the master database, create the database master key, if needed.
    USE master
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'reliance@123'
    GO
    Step 7 :
    Make a certificate on the DR server instance.
    CREATE CERTIFICATE MirrorServerCertificate
    WITH SUBJECT = 'MirrorServer certificate',
    START_DATE = '20110601',
    EXPIRY_DATE = '20500101'
    GO
    Step 8 :
    Create a mirroring endpoint for the server instance on DR.
    CREATE ENDPOINT MirroringEndPoint
    STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE MirrorServerCertificate,
    ENCRYPTION = REQUIRED ALGORITHM RC4,ROLE = ALL)
    GO
    Step 9 :
    Back up DR certificate.
    BACKUP CERTIFICATE MirrorServerCertificate TO FILE = 'C:\Certificate\MirrorServerCertificate.cer';
    GO
    Step 10 :
    Using any secure copy method, copy 'C:\Certificate\MirrorServerCertificate.cer'
    to Principal.
    Step 11 :
    Create a login on Principal for DR and a user for that login.
    USE MASTER
    GO
    CREATE LOGIN MirrorLogin WITH PASSWORD = 'reliance@123'
    GO
    CREATE LOGIN PrincipalLogin WITH PASSWORD = 'reliance@123'
    GO
    CREATE USER MirrorUser FOR LOGIN MirrorLogin
    GO
    CREATE USER principalUser FOR LOGIN PrincipalLogin
    GO
    Step 12 :
    Associate the certificate with that user and Grant connect permission on the login for mirroring endpoint.
    CREATE CERTIFICATE MirrorServerCertificate AUTHORIZATION MirrorUser
    FROM FILE ='c:\MirrorServerCertificate.cer'
    GO
    GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [MirrorLogin]
    GO
    GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [principalLogin]
    GO
    Step 13 :
    Create a login on DR for Principal and a user for that login.
    USE MASTER
    GO
    CREATE LOGIN MirrorLogin WITH PASSWORD = 'reliance@123'
    GO
    CREATE LOGIN PrincipalLogin WITH PASSWORD = 'reliance@123'
    GO
    CREATE USER MirrorUser FOR LOGIN MirrorLogin
    GO
    CREATE USER principalUser FOR LOGIN PrincipalLogin
    GO
    Step 14 :
    Associate the certificate with that user and Grant connect permission on the login for mirroring endpoint.
    CREATE CERTIFICATE PrincipalServerCertificate AUTHORIZATION PrincipalUser
    FROM FILE = 'c:\PrincipalServerCertificate.cer'
    GO
    GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [MirrorLogin]
    GO
    GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [principalLogin]
    GO
    Step 15 :
    On the mirror server instance on DR, set the server instance on Principal as the partner (making it the initial principal server instance).
    --At DR, set server instance on Principal as partner (principal server):
    ALTER DATABASE DBNAME SET PARTNER = 'TCP://192.168.6.25:5022' GO
    Step 16 :
    On the principal server instance on Principal, set the server instance on DR as the partner (making it the initial mirror server instance).
    --At Principal, set server instance on DR as partner (mirror server).
    ALTER DATABASE DBNAME SET PARTNER = 'TCP://192.168.7.20:5022' GO

    Please mark me as answer if my post helps you

    Regards

    Chetanv

    Friday, June 17, 2016 4:33 PM
  • This is the procedure I am following.  The very last alter database is what fails

    Jim Hodges @ CMI

    Friday, June 17, 2016 4:37 PM
  • I assume you confirmed with TELNET that you can actually connect to those ports on both sides?

    Can you also (just for testing) try to open your endpoint to public? (GRANT CONNECT ON ENDPOINT::<Name> TO PUBLIC) Also, can you please script out the endpoints on both sides and see if they match? Should not be an issue, but just to play it safe.

    Saturday, June 18, 2016 7:14 PM
  • This is the procedure I am following.  The very last alter database is what fails

    Jim Hodges @ CMI

    Could you please post ALTER Statement and Error which you are getting.

    Please Mark me as answer if my post helps you.

    Regards

    ChetanV

    Monday, June 20, 2016 10:57 AM