none
sql server mirroring error RRS feed

  • Question

  • Hello All,

    Its quite surprizing that I am getting error while establishing mirroring. I am using 2008 R2, 64 BITwith SQL SERVER 2008 SP1. I am not using witness server. When I am trhyihng to establish mirror and create me the following error.

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

    An error occurred while starting mirroring.

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

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

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&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://DDC01SRV111.a-bwater.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. (.Net SqlClient Data Provider)

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

    ------------------------------
    Server Name: PDC01SRV009GIS\GIS
    Error Number: 1418
    Severity: 16
    State: 1
    Line Number: 1


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

       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

    Any help would be appreciated.

    Monday, July 4, 2011 5:37 AM

All replies

  • Have you tried to make your endpoints with IP in stead of server name?

     

    If your servers have a separate network segment for mirroring, but the default gateway points to another subnet, they cannot find each other. You can make permanent routes or make the endpoint with IPaddress. "TCP://10.0.1.111:5022"

     

    Also a DNS issue can be solved this way: if the same server has to be known to your LAN clients, your DNS servers will probably resolve every request for this name to its LAN interface. You can make an entry in the local hosts file on each mirror server, but I prefer to work with IP endpoints.

    Monday, July 4, 2011 7:36 AM
  • Can you able to telneting bi-directional from principal to mirror on endpoint port?

    Server Name: PDC01SRV009GIS\GIS
    Error Number: 1418
    Severity: 16
    State: 1
    Line Number: 1

    From MSDN :-

    Error - 1418 indicates that server network endpoint did not respond because the specified server network address cannot be reached or does not exist.

    User Action


    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.

    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:

      SELECT SERVERPROPERTY('ProcessID') 
    2. Match the process ID with the output of the following netstat command:

      netstat -ano

    For more please visit here http://msdn.microsoft.com/en-us/library/aa337361.aspx

    Please let us know if you need any more help

     

     


    http://uk.linkedin.com/in/ramjaddu
    Monday, July 4, 2011 8:46 AM
  • Hi, I have checked and endpoints are started. I am bit confused because I have two different instance on both "principal and mirror" servers ( 2 instances on principal and 2 on mirror). I can established the mirror from one instance and It is working fine when I am trying to establish the mirroring from the other instance it throws me the 1418 (mention as above) error. Both instance services and running on the same domain account. Any idea what I need to check ? Any help would be appreciate. Regarsd,
    Tuesday, July 5, 2011 2:40 AM
  • have you got different endpoint ports using for each SQL instances. let say on principal first instance mirroring endpoint configured on 5022 then you should use different port for second instance say 5023.

    Please check you can telnet on these ports bi-directional from principal to mirror

    as said before you have to check principal SQL processid connecting to correct port on mirroring for that you have to check below

    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:

      SELECT SERVERPROPERTY('ProcessID') 
    2. Match the process ID with the output of the following netstat command:

      netstat -ano



    http://uk.linkedin.com/in/ramjaddu
    Tuesday, July 5, 2011 8:44 AM
  •  

     

    Principal Server:

    TCP    10.11.1.98:7023        0.0.0.0:0              LISTENING       3688

    Mirror Server:

    TCP    10.221.1.111:7024      10.11.1.91:49830       TIME_WAIT       0

     

    Is there any specific way that I determine which port is available for mirroring ?

    Regards,

     

     

     

    Yes I have got different endpoints with different ports on my principal server and they are talked to each other. But when I am trying to establish mirroring on my mirror server I am facing the same error. I checked and Process id and I am pasting as follows:
    • Edited by hellozishan Wednesday, July 6, 2011 4:37 AM
    Wednesday, July 6, 2011 3:20 AM
  • Is it port 7022 or 5022? Your script says 7022. RamJaddu has already recommended to use a different port number as well as test network connectivity using either IP or FQDN

    Note that even if you have different instances on your server, you would need to use a different port number for database mirroring to work. For example, let's say you have SERVERA and SERVERA\INSTANCEA. If SERVERA is already configured to use port 5022, SERVERA\INSTANCEA will not use this port number for database mirroring as it has already been allocated for. You can use port 5023 for SERVERA\INSTANCEA.


    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    Wednesday, July 6, 2011 3:51 AM
    Moderator
  • Thanks for prompt reply I am using port 7023 for principal server and port 7024 for mirror server but still getting the same error message. While The Instance which is running mirroring fine is using 5022 for principal and 5023 for mirror server.

     

    Any help would be appreciate.

    Wednesday, July 6, 2011 5:36 AM
  • Can you able to telnet on port 7023 and 7024 and are they open in firewall?

    and account under endpoints created does it have enough permissions ?

     


    http://uk.linkedin.com/in/ramjaddu
    Wednesday, July 6, 2011 9:22 AM
  • There is only  configure Database services with Domain A/C , which has all privileges in Netwrok as IN SQL 2k8 very much specific to Domain Controller privileges .

    Your problem will be  solve.


    Kuldeep
    Wednesday, July 6, 2011 12:06 PM
  • Hi Kuldeep,

    Could you please give me more explanation ? How can I compare the permission between two instances. I am saying this because I am alos unable to establish mirroring from instances on the same machine running 2008 R2 same 1418 message arise. Any help...

     

    Regards,


    Wednesday, July 6, 2011 11:25 PM
  • The domain account that you are using for the database mirroring configuration should have CONNECT permissions on the mirroring endpoint on both the principal and the mirror instance. If you are getting a 1418 error message when configuring database mirroring between instances on the same server, I`m starting to think that there is something wrong with the network stack. Try rebooting the mirror server and see if you can telnet to port 7024 from the principal.

    Plus, can you check the Windows Event Log for any event pertaining to network issues?


    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn
    Thursday, July 7, 2011 2:44 AM
    Moderator
  • I have change my database service to "Local System" and now mirroring working fine on the local system (both instances are on local system). But When I am using the domain account then receive error 1418. I cant understan where is the permission problem because on another instance with the same domain account mirroring is working fine. Its strange thing, is there any permission related option on instance level where I can check that both of these instances talked to each other ?
    Thursday, July 7, 2011 6:52 AM
  • I think you should grant permissions on endpoints to domain accounts. This needs to be done on both partners....I believe this permssion already assigned to witness as one instance mirroring was working

    here is command.

    GRANT CONNECT ON ENDPOINT::Mirroring TO [Mydomain\dbousername];


    http://uk.linkedin.com/in/ramjaddu
    Thursday, July 7, 2011 8:03 AM
  • Hi,

    Now I am facing two different errors on the same server with different logic.

    Database mirroring cannot be enabled because the "gisprod" database may have bulk logged changes that have not been backed up. The last log backup on the principal must be restored on the mirror. Error 1475

    After restore the database backup with norecovery I get the following error.

    The server network address "TCP://SDC01SRV111.g-mwater.com.au: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. Error 1418

    does it make any sence ?

    Wednesday, July 13, 2011 2:59 AM
  • first error could be -Mirroring only work on under full recovery model so please change recovery model of gisprod to full

    Second error could be the account under end points created might not have enough permissions on principal, mirror and witness

    1. check telnet working bi-directional on endpoint ports between principal and mirror

    2. any firewall restrictions

    3. check with netstats are they ports are listening on both ends

    for resolving mirroring network issues please visit here http://msdn.microsoft.com/en-us/library/ms189127.aspx

    any help please let us know

     

     


    http://uk.linkedin.com/in/ramjaddu
    Wednesday, July 13, 2011 8:29 AM