none
SQL Mirroring, Error Number: 1418

    Pregunta

  • Hi all, I know there are other posts that have had the same problem but I have followed them and still not able to set up sql mirroring. Both machines are SQL server 2008 standard edtion

    What i've done so far:

    >repeated the process 4 times so far from start to finish

    >backed up database on principal server (backup type: full and transitional logs)

    >restored on mirror server (backup type: full and transitional logs) with no recovery

    >configure security wizard which works fine

    >click start mirroring and this is where I get the 1418 error

    What i've done to try and resolve it:

    >changed the names of the machines to IP addresses

    >turned firewalls off on both machines

    >performed port scans on both machines which show port 5022 open

    >performed netstat -anob on both machines which show a few established connections on port 5022 (I think this is the same amount of times I clicked start mirroring, which seems to suggest it has established a connection but for some reason still doesnt mirror)

     

    Does anybody have any further recommendations? 

     

    Below is the full error:

    ===================================

    An error occurred while starting mirroring.

    ===================================

    Alter failed for Database 'testdatabase'.  (Microsoft.SqlServer.Smo)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

    ------------------------------
    Program Location:

      at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()
      at Microsoft.SqlServer.Management.Smo.Database.Alter()
      at Microsoft.SqlServer.Management.SqlManagerUI.DBPropMirroring.StartMirroring(Object sender, EventArgs e)

    ===================================

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    Program Location:

      at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
      at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
      at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
      at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
      at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplFinish(StringCollection alterQuery, ScriptingOptions so)
      at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplWorker()
      at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()

    ===================================

    The server network address "TCP://192.168.5.70: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. (.Net SqlClient Data Provider)

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

    ------------------------------
    Server Name: 192.168.5.51
    Error Number: 1418
    Severity: 16
    State: 1
    Line Number: 1


    ------------------------------
    Program Location:

      at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
      at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

    martes, 09 de agosto de 2011 21:44

Respuestas

  • 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
    • Propuesto como respuesta Peja Tao viernes, 12 de agosto de 2011 3:01
    • Marcado como respuesta Peja Tao lunes, 22 de agosto de 2011 8:50
    miércoles, 10 de agosto de 2011 10:27
  • Hi ,

    Please try the solution in the link provided by Ram, you need also verify all instances can access every other partner and make sure endpoints are running properly.

    Hope this helps.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marcado como respuesta Peja Tao lunes, 22 de agosto de 2011 8:50
    viernes, 12 de agosto de 2011 3:15

Todas las respuestas

  • You can try connecting SQL Server profiler to the mirror server to help see what is going on and capture any errors. What account are you using? Can you confirm that your permissions are sufficient on both the principle and mirror. Are you using a domain user?

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    miércoles, 10 de agosto de 2011 1:09
  • 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
    • Propuesto como respuesta Peja Tao viernes, 12 de agosto de 2011 3:01
    • Marcado como respuesta Peja Tao lunes, 22 de agosto de 2011 8:50
    miércoles, 10 de agosto de 2011 10:27
  • Hi ,

    Please try the solution in the link provided by Ram, you need also verify all instances can access every other partner and make sure endpoints are running properly.

    Hope this helps.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marcado como respuesta Peja Tao lunes, 22 de agosto de 2011 8:50
    viernes, 12 de agosto de 2011 3:15
  • Hi Reggie,

    You seemed to have follow all the steps correctly. Check the status of database on mirror server. IT should be restoring otherwise apply a new backup in No recovery Mode. 

    Also make sure the account being used in both servers should be a trusted or same domain account.

     

     

    sábado, 13 de agosto de 2011 9:54
  • I was experiencing the exact issue.  I didn't notice until reading in another post that the Principle connection was encrypted and the Mirror was not.

    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.

    martes, 16 de agosto de 2011 6:16