none
Error 1418: trying to configure SQL Server 2008 mirroring

    質問

  • PROCEDURE:

    The following are the steps that I did to configure the mirror:

    1.- On PRINCIPAL.domain.com, I did a full backup for each database that we need to mirror on the other server. (Previusly, I checked that recovery method for each database are configured to FULL)

    2.- Then, on MIRROR.domain.com, I restored all the backups with NORECOVERY option selected. Until now, all it's ok. Databases are showed with "Restoring..." status.

    3.- When I finished to restore all the databases, I try to configure mirror on PRINCIPAL.domain.com

    4.- I select a database, I ran "Configure Security..." wizard. I select the options, the endpoint configuration finished without errors or warnings; On this moment appears the option to START MIRRORING. I selected and appear this error message:

    TITLE: Database Properties
    ------------------------------

    An error occurred while starting mirroring.

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

    Alter failed for Database 'SharePoint_AdminContent_0376bde9-ea3e-4609-be1a-401236010f8d'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&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://NABONIDUS.infrasoftcorp.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. (Microsoft SQL Server, Error: 1418)

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

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    5.- So, on MIRROR.domain.com, now the database appears with "In Recovery...", but the mirror is not function.

    End of the procedure.


    SOME IMPORTANT ISSUES:

    A.- When I ran the "Configure Security..." wizard, the Summary page shows the settings that I selected and that you can see below. (You can see that PRIMARY is configured to use encryption, but MIRROR don't. By the way, I'm unabled to change this setting, when PRINCIPAL page appears, the Encription option is grayed and checked). I don't know if this is the root cause of the problem:

    --------------------------------
    Click Finish to perform the following actions:

    On the principal server instance, PRIMARY.domain.com
    Modify the following properties of the mirroring endpoint:
    Name: Mirroring
    Listener Port: 5022
    Encryption: Yes
    Role: Partner
    On the mirror server instance, MIRROR.domain.com
    Modify the following properties of the mirroring endpoint:
    Name: Mirroring
    Listener Port: 5022
    Encryption: No
    Role: Partner
    --------------------------------

    B.- I checked firewall on servers. On both, the Windows Firewall is disabled.

    C.- I can ping PRIMARY from MIRROR, and viceversa.

    D.- I can TELNET on 5022 TCP port (both servers)


    I will appreciate all help that you can provide me. I have very little experience working with SQL.

    2009年6月15日 18:05

すべての返信

  • Hi,

    I have seen an issue like this before occasionally in my test environment, where through no fault of your own the GUI setups of the endpoints mismatching. You do want the 2 endpoints to be using the same form of encryption, and for them both to either have it set to on, or to off. I would suggest dropping these endpoints and setting them up again, but doing it manually. If you're unfamiliar with how to do this, a good way would be to script one of them now from SSMS, and then use that script as a template to re-create the endpoints again, therefore ensuring that you have the same configuration on both endpoints.

    If after trying this you are still getting a 1418 error, here's a blog post with a couple of other things to check for:

    http://blogs.msdn.com/grahamk/archive/2008/12/08/database-mirroring-error-1418-troubleshooter.aspx

    But first try to drop and re-create the endpoints with same encryption. (and if you do this, don't forget to grant the "connect" permission to your service accounts as well.

    Graham Kent [MSFT]
    SQL Server Support Escalation Engineer
    http://blogs.msdn.com/grahamk

    All postings are provided "AS IS" with no warranties, and confers no rights

    • 回答の候補に設定 globalgoat 2012年2月20日 19:40
    2009年6月16日 8:01
  • I was experiencing the exact issue you describe but didn't notice the difference in the encryption setting until reading your post.

    Drop the mirroring endpoint on both servers.

    Use the wizard to create a new endpoint choose Options when connecting to the Mirror instance and check the Encrypt connection option.

    2011年8月16日 6:10
  • To identify the listening ports and the processes that have those ports opened, follow these steps:

       1.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:
          Copy

          SELECT SERVERPROPERTY('ProcessID') 

       2. Match the process ID with the output of the following netstat command:

          netstat -ano


    Please check more help http://www.sqlsoldier.com/wp/sqlserver/troubleshooting-atabasemirroringerror1418



    http://uk.linkedin.com/in/ramjaddu
    2011年8月16日 9:11
  • Hello,

     

    Retry configuring the mirroring using T-sql, its very simple..

    Also try to add the server FQDNs to the hosts file in C drive, most of time this resolves.  


    Rgds, Krishna Http://Blogs.SQLServer.in/
    2011年8月17日 11:31
  • Hi,

    Follow below troubleshooting steps:

    Verify the network address name and reissue the command.

    Corrective action might be required on both partners. For example, if this message is raised when you are trying to run SET PARTNER on the principal server instance, the message might imply that you only have to take corrective action on the mirror server instance. However, corrective actions might be required on both partners.

    Additional Corrective Actions

    • 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.

    • Make sure that the principal server instance is listening on the port assigned to its database mirroring endpoint and that and the mirror server instance is listening on its port. For more information, see "Verifying Port Availability," later in this topic. If a partner is not listening on its assigned port, modify the database mirroring endpoint to listen on a different port.

    Using the Error Log File for Diagnosis

    In some cases, only error log files are available for investigation. In these cases, determine whether the error log contains error message 26023 for the TCP port of the database mirroring endpoint. This error, which is severity 16, might indicate that the database mirroring endpoint is not started. This message can occur even if sys.database_mirroring_endpoints shows the endpoint state as started.

    After resolving any issues that you encounter, rerun the ALTER DATABASE database_name SET PARTNER statement on the principal server.

    Verifying Port Availability

    When you are configuring the network for a database mirroring session, make sure the database mirroring endpoint of each server instance is used by only the database mirroring process. If another process is listening on the port assigned to a database mirroring endpoint, the database mirroring processes of the other server instances cannot connect to the endpoint.

    To display all the ports on which a Windows-based server is listening, use the netstat command-prompt utility. The syntax for netstat depends on the version of the Windows operating system. For more information, see the operating system documentation.

     

    Hope it helps!!


    Regards, Vishal Srivastava
    2011年8月20日 5:49
  • Please check these articles.

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

    http://www.mssqlfix.com/2011/11/setting-up-database-mirroring-using.html

    http://www.mssqlfix.com/2011/11/step-by-step-guide-to-configure.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.
    -------------------------------------------------------
    2011年12月14日 9:14
  • ere 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...

    2013年9月25日 18:37