locked
Unable to connect if mirror down RRS feed

  • Question

  • We have an application running with a principal, mirror, and witness. If we bring the principal server down, it looks like failover occurs successfully, but the application is not able to access the database. If we bring the server back (so it is now functioning as the mirror), the application is now able to connect to the database. Why isn’t the application able to connect without the mirror up? Any suggestions?

     

    Friday, December 1, 2006 7:58 PM

All replies

  • What was the error at that time?

    Any errors on SQL server error log?

    Sunday, December 3, 2006 8:44 PM
  • What was the error at that time?

    Error:
    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

    Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Tandberg.TMS.Framework.Db.DbHelperTransaction.ExecuteReader(String connectionString, CommandType commandType, String spName, SqlParameter[] commandParameters) at Tandberg.TMS.Framework.Db.DbHelper.ExecuteReader(String spName, SqlParameter[] parameterValues)

    MachineName:XXX3A01
    TimeStamp:12/4/2006 10:10:27 AM
    AssemblyName:App_Web_defaulterror.aspx.cdcab7d2, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null
    AppDomainName:/LM/X3SVC/1/Root/TMS-1-128097178938193390
    ThreadIdentity:NT AUTHORITY\NETWORK SERVICE

    Any errors on SQL server error log?

    2006-12-04 10:05:31.18 spid18s     Error: 1479, Severity: 16, State: 1.

    2006-12-04 10:05:31.18 spid18s     The mirroring connection to "TCP://xxx3d01.xxx.net:5022" has timed out for database "tmsng" after 10 seconds without a response.  Check the service and network connections.

    2006-12-04 10:05:31.24 spid18s     Database mirroring is inactive for database 'tmsng'. This is an informational message only. No user action is required.

    2006-12-04 10:05:31.24 spid18s     The mirrored database "tmsng" is changing roles from "MIRROR" to "PRINCIPAL" due to Auto Failover.

    2006-12-04 10:05:31.73 spid18s     Recovery is writing a checkpoint in database 'tmsng' (5). This is an informational message only. No user action is required.

    2006-12-04 10:06:04.17 Server      Error: 1474, Severity: 16, State: 1.

    2006-12-04 10:06:04.17 Server      Database mirroring connection error 4 '64(The specified network name is no longer available.)' for 'TCP://xxx3d01.xxx.net:5022'.

    2006-12-04 10:06:25.60 spid23s     Error: 1474, Severity: 16, State: 1.

    2006-12-04 10:06:25.60 spid23s     Database mirroring connection error 2 'Connection attempt failed with error: '10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)'.' for 'TCP://xxx3d01.xxx.net:5022'.

     

    Monday, December 4, 2006 5:12 PM
  • We solved the problem by setting the timeout on the connection string to 60.  When the Primary DB is taken off line, the Mirror becomes the Principal.

    The web application must use the fail over partner (specified in the connection string) to connect.  The initial connection takes 40+ seconds.  The subsequent requests seem to have a normal response time.   Is this delay expected behavior or do we have something configured incorrectly? 

     

    • Proposed as answer by XtendedLearner Wednesday, April 6, 2016 3:36 PM
    Thursday, December 7, 2006 12:14 PM