locked
MS Sql Server Error 1418 RRS feed

  • Question

  • Hi,

    As I am going to configure Database Mirroring in sql server 2008 R2. It is giving error 1418.

    The server network address "TCP://xyzserver.domain.con:5022" can not reached or does not exist. Check network address name and that the ports for the local and remote endpoints sre operational.(Microsoft SQL Server, Error: 1418)

    I have tried the below steps:

    1. ALTER ENDPOINT Mirroring FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = SUPPORTED)

     

    2. ALTER DATABASE PROD_DB SET PARTNER = 'TCP://xyzserver.domain.Com:5022'

     

    3. DROP ENDPOINT Mirroring

     

     

    SELECT *

    FROM sys.endpoints

     

    4. CREATE ENDPOINT Mirroring

        STATE = STARTED

        AS TCP ( LISTENER_PORT = 60801 )

        FOR DATABASE_MIRRORING (

           AUTHENTICATION = WINDOWS KERBEROS,

           ENCRYPTION = SUPPORTED,

           ROLE=ALL);

    SELECT state ,state_desc FROM sys.database_mirroring_endpoints 

     

     

    3. GRANT CONNECT ON ENDPOINT::Mirroring TO ALL

     

     

    4. GRANT CONNECT ON ENDPOINT::Mirroring TO SA

        WITH GRANT OPTION;

    GO

    I have tried all above the steps. But still I can not resolve the issue.

    Can anyboby help me?

    Sachin Jain

    Monday, July 25, 2011 11:46 PM

Answers

  • Please check below points
    Make sure that the mirror database is ready for mirroring.
    Make sure that the name and port of the mirror server instance are correct.
    Make sure that the destination mirror server instance is not behind a firewall.
    Make sure that the principal server instance is not behind a firewall.
    Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. If either endpoint is not started, execute an ALTER ENDPOINT statement to start it.
    To identify the listening ports and the processes that have those ports opened, follow these steps:
    Obtain the process ID.
    To learn the process ID of an instance of SQL Server, connect to that instance and use the following Transact-SQL statement:
     SELECT SERVERPROPERTY('ProcessID') 
    Match the process ID with the output of the following netstat command:
    netstat -ano
    also please check local host file are set-up correctly ipaddress and hostnames for (principal, mirror and witness)

    http://uk.linkedin.com/in/ramjaddu
    • Proposed as answer by Peja Tao Wednesday, July 27, 2011 6:40 AM
    • Marked as answer by Peja Tao Monday, August 1, 2011 1:26 AM
    Tuesday, July 26, 2011 12:52 PM

All replies

  • This error message pertains to connectivity between the principal and the mirror. TELNET and PING tests are your friends to test connectivity.

    Are you configuring database mirroring for databases in a SQL Server instance that are not in an Active Directory domain?

    Check out thisMSDN Documentation to Troubleshoot Database Mirroring Deployments

    http://msdn.microsoft.com/en-us/library/ms189127.aspx


    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    Tuesday, July 26, 2011 12:07 AM
  • Hi,

    First confirm that your principal and mirror servers are in domain ,If not you should use certificate authentication .

    1. Make sure your sql service is running under the same domain account on all of your sql servers in the mirroring.
    2. Make your sql service account sysadmin on your sql servers.
    3. Make your sql service account local admin on your servers.


    Sivaprasad.L Together We can Achieve
    Tuesday, July 26, 2011 12:23 AM
  • Hi,

    Thanks for the quick reply.

    My account is from AD domain & had full admin access of domain.

    Till getting the issue.


    Sachin Jain
    Tuesday, July 26, 2011 12:29 AM
  • I also done the PING & TELNET
    Sachin Jain
    Tuesday, July 26, 2011 12:30 AM
  • HI Shiva,

     

    Both the servers (P & M) are in domain.

    And the service is also running under the domain account.

    My Principal server is having A/P clustering. And it is named instance as well.


    Sachin Jain
    Tuesday, July 26, 2011 12:33 AM
  • 2. Make your sql service account sysadmin on your sql servers.

    I discourage making any account a member of the sysadmin role unless it is really necessary due to security reasons. A domain user account or a local user account is the minimum requirement for the SQL Server service account.Check out this documentation on Setting Up Windows Service Accounts

    http://msdn.microsoft.com/en-us/library/ms143504.aspx


    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    Tuesday, July 26, 2011 12:36 AM
  • Hi,

    If your answer is yes for my previous reply ,then try this appraoch

    1.is your mirror database state is in recovery ?

    2.have you restored recent tlog backup file on mirror database ?

    3. are you using same  port number (60801) for all server .if yes what is the status of telnet of that ?

    4.If port is not listening then add exception in firewall

    below steps you have already mentioned but try once again

    5.Check once again endpoints state ,start them if they are not started

    6.Grant connect permission to end points

    7.If none of the above steps work for for you then drop all end points and fallow from first step once again.

    Sure your issue will get resolve


    Sivaprasad.L Together We can Achieve
    • Proposed as answer by Sams Sun Friday, April 26, 2013 7:01 AM
    • Unproposed as answer by Sams Sun Friday, April 26, 2013 7:01 AM
    Tuesday, July 26, 2011 12:39 AM
  • Yes..

    Service accounts used had sysadmin as well as domain admin privileges.


    Sachin Jain
    Tuesday, July 26, 2011 12:40 AM
  • 1. DB state is restoring.

    2. I have restored recent logs on mirror DB.

    3. Port for Principal Server is 60801 & for Mirror Server is 5023

    4. Both ports are listening vice versa.

    5. Firewall is already disabled. 


    Sachin Jain
    Tuesday, July 26, 2011 12:45 AM
  • Please check below points
    Make sure that the mirror database is ready for mirroring.
    Make sure that the name and port of the mirror server instance are correct.
    Make sure that the destination mirror server instance is not behind a firewall.
    Make sure that the principal server instance is not behind a firewall.
    Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. If either endpoint is not started, execute an ALTER ENDPOINT statement to start it.
    To identify the listening ports and the processes that have those ports opened, follow these steps:
    Obtain the process ID.
    To learn the process ID of an instance of SQL Server, connect to that instance and use the following Transact-SQL statement:
     SELECT SERVERPROPERTY('ProcessID') 
    Match the process ID with the output of the following netstat command:
    netstat -ano
    also please check local host file are set-up correctly ipaddress and hostnames for (principal, mirror and witness)

    http://uk.linkedin.com/in/ramjaddu
    • Proposed as answer by Peja Tao Wednesday, July 27, 2011 6:40 AM
    • Marked as answer by Peja Tao Monday, August 1, 2011 1:26 AM
    Tuesday, July 26, 2011 12:52 PM
  • here are few steps further to check. These corrected issue for me and mirroring started perfectly.

    1. Check for the services - SQL server and SQL browser. Set the SQL browser service to automatic and make sure it runs.

    2. Make sure on both servers the services are not runningon local accounts. Try changing these to domain account (i used the Domain administrator windows credentials that i use for the sql instance) 

    3. Under sql server configuration manager , on both servers , make sure have tcp and named pipes enabled under native configuration services. 

    Hope this helps...

    • Proposed as answer by Poonam K Gill Wednesday, September 25, 2013 6:33 PM
    Wednesday, September 25, 2013 6:32 PM
  • hi All,

    Mentioned link will help you.

    http://nerdwords.blogspot.in/2010/11/troubleshooting-sql-mirror-errors.html


    Kirpal Singh

    Friday, January 24, 2014 9:56 AM