locked
SQL Database Mirroring Issue RRS feed

  • Question

  • While attempting to mirror two sql servers without a witness server, the following error kept coming through right at the end:

    The server network address "TCP://MOSSSQL-B.mka.compute-1.internal: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)

    I checked that both endpoints were created and on/started.  I can ping both servers from the other server.  Both servers are running SQL Standard 2005 ver 9.0.3077.  All servers are in Amazon's cloud and apart of the "local" domain: MKA (mka.compute-1.internal). 

    In trying to fix this issue, a forum, http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22896060.html?sfQueryTermInfo=1+1418+2005+cannot+doe+error+exist+mirror+reach+sql, suggested to run the SQL processes under a domain admin account rather than the local admin account.  The first server switched over fine, but the second server now gives this error:

    Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log.  If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code -2146885628.

    1. Does the SQL server need to be run by a Domain admin for cloning to be successful?
    2. What else should be done to get these two machines cloning successfully?


    NC
    Tuesday, July 14, 2009 8:32 PM

All replies

  • The SQL Server does not need to be running under an account that has any admin authority.  As a matter of fact, your SQL Server service account should not be a local admin and certainly not a domain admin.

    BTW, while Database Mirroring is supported on SQL Server 2005 Standard, you can only run it in high availability operating mode, which means you have to have a witness.

    Can you telnet to the mirror's DBM port from the principal and vice versa?  Did you ping by both name as well as IP?
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Wednesday, July 15, 2009 2:27 AM
  • Have you tried to use the same domain account as servie account on both database instances (principal and mirror) and set the account as sysadmin?
    When you change service account, please read the documentation before, so that the rights are set correct on the Windows host.

    I agree that you should not let the service account be domain or local administrator.
    Sincerely Niels Grove-Rasmussen
    Wednesday, July 15, 2009 7:43 AM
  • If the service account isn't a member of the sysadmin role, your instance isn't even going to work.  So, that doesn't have anything to do with Database Mirroring not connecting.
    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Wednesday, July 15, 2009 10:36 AM
  • We were able to telnet to and from both servers as well as ping both servers using their domain name or IP.

    To clarify, we wanted the service to run as a domain user with admin rights on the local machine.  Right now, the SQL service is running under a local account with admin rights to the machine.  Does the SQL service need to be running under a domain user with admin rights to the local server?

    If we absolutely need a witness server when using SQL Server 2005 Standard, then that would probably be the problem.  We will be trying this once the new instance is up and functional.

    Wednesday, July 15, 2009 2:19 PM
  • The witness server was setup and added to the configuration, but still, the same error pops up saying that it can't contact the second server (mosssql-b).

    Interestingly, the database we were trying to mirror (Sharepoint_MySites) changed from "Restoring..." to "In Recovery" on the mirrored server (MOSSSQL-B).  But the database does not say it is being mirrored on the first server.

    Here is the full error report.

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

    An error occurred while starting mirroring.

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

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

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&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://MOSSSQL-B.mka.compute-1.internal: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=09.00.3077&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

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

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


    NC
    Wednesday, July 15, 2009 2:55 PM
  • The SQL services needed to be ran under a domain account, not a local account, for the databases to mirror successfully.  Once a new instance was created, the service was finally allowed to be ran by a local account.
    • Proposed as answer by Michael.Bruni Wednesday, September 23, 2009 8:31 PM
    Wednesday, September 23, 2009 8:31 PM
  • i think you are trying to access the sql server services from services.msc ,try to access then sql Server Configuration manager.if you are using 2   independent server , which are not in domain , ,then create 2 same user name with same passwords.

    at the end replace the server names with there IP,

    Tuesday, July 3, 2012 11:00 AM
  • Can you please check and verify if it resolves to FQDN ? In one of our setup it did not resolved to FQDN and we've used host name only which worked well.  On the same machine it failed to create Mirror when we setup using GUI however it works well if we use TSQL - tried using TSQL yet ?

    If DBM showing In Recovery, checked the mirroring status http://www.sql-server-citation.com/2011/09/checking-db-mirroring-status.html

     

    Hemantgiri S. Goswami | SQL Server Citation| Surat SQL Server User Group

    Wednesday, July 4, 2012 3:36 AM