Load balancing and failover
Wehave noticed that when a sql server goes down and sql failover works correctly, everything seems to be just fine. However, when we take down a host that contains a sql server to do maintenance, then we end up with Sitefinity not working at all. Here's a relevant error message:
Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 10/21/2014 12:12:53 PM
Event time (UTC): 10/21/2014 5:12:53 PM
Event ID: 01a5d2950f3641e39bf4b8f462a5418d
Event sequence: 152213
Event occurrence: 153
Event detail code: 0
Application information:
Application domain: /LM/W3SVC/15/ROOT-1-130583520069914655
Trust level: Full
Application Virtual Path: /
Application Path: \\<shared filesystem>\sites\<site>\Standard\<site>Main\wwwroot\
Machine name: <name>1
Process information:
Process ID: 6096
Process name: w3wp.exe
Account name: WEBHOST\ws<name>
Exception information:
Exception type: DataStoreException
Exception message: Unable to connect to
Backend=mssql;Driver=genericADO2;ConnectionString=data
source=<fqdn>;failover
partner=<fqdn>;UID=<username>;PWD=<password>;initial
catalog=<productiondb>:
Telerik.OpenAccess.RT.sql.SQLException: A network-related or instance-specific
error occurred while establishing a connection to SQL Server. The server was
not found or was not accessible. Verify that the instance name is correct and
that SQL Server is configured to allow remote connections. (provider: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server) --->
System.Data.SqlClient.SqlException: A network-related or instance-specific error
occurred while establishing a connection to SQL Server. The server was not
found or was not accessible. Verify that the instance name is correct and that
SQL Server is configured to allow remote connections. (provider: Named Pipes
Provider, error: 40 - Could not open a connection to SQL Server) --->
System.ComponentModel.Win32Exception: The network path was not found
--- End of inner exception stack trace ---
at
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,
Boolean breakConnection, Action`1 wrapCloseInAction)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo,
SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64
timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
integratedSecurity, Boolean withFailover)
at
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, SecureString newSecurePassword, Boolean
ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at
System.Data.SqlClient.SqlInternalConnectionTds.LoginWithFailover(Boolean
useFailoverHost, ServerInfo primaryServerInfo, String failoverHost, String
newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance,
SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer
timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer
timeout, SqlConnectionString connectionOptions, SqlCredential credential,
String newPassword, SecureString newSecurePassword, Boolean
redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, SqlCredential credential,
Object providerInfo, String newPassword, SecureString newSecurePassword,
Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions,
SessionData reconnectSessionData)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo,
DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions
userOptions)
at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions
userOptions)
at
System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection
owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions
userOptions, DbConnectionInternal oldConnection, DbConnectionInternal&
connection)
at
System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection
outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1
retry, DbConnectionOptions userOptions)
at
System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1
retry, DbConnectionOptions userOptions)
at
System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at
System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at OpenAccessRuntime.Intellitrace.OpenConnection(DbConnection
connection, String connectionString, String id)
at Telerik.OpenAccess.Runtime.Logging.LoggingDbConnection.Open()
at Telerik.OpenAccess.RT.Adonet2Generic.Impl.DBDriver.connect(ConnectionString
connectionString, PropertySet driverProps, ConnectionPoolType poolType,
LogEventStore pes)
--- End of inner exception stack trace ---
at
Telerik.OpenAccess.RT.Adonet2Generic.Impl.DBDriver.connect(ConnectionString
connectionString, PropertySet driverProps, ConnectionPoolType poolType,
LogEventStore pes)
at
OpenAccessRuntime.Relational.conn.RelationalConnectionPool.createRealCon()
DBDriver: Telerik.OpenAccess.RT.Adonet2Generic.Impl.DBDriver
useUTCForReads=1
versant.prepareCommands=True Telerik.OpenAccess.RT.sql.SQLException: A
network-related or instance-specific error occurred while establishing a
connection to SQL Server. The server was not found or was not accessible.
Verify that the instance name is correct and that SQL Server is configured to
allow remote connections. (provider: Named Pipes Provider, error: 40 - Could
not open a connection to SQL Server) --->
System.Data.SqlClient.SqlException: A network-related or instance-specific
error occurred while establishing a connection to SQL Server. The server was
not found or was not accessible. Verify that the instance name is correct and
that SQL Server is configured to allow remote connections. (provider: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server) --->
System.ComponentModel.Win32Exception: The network path was not found
--- End of inner exception stack trace ---
at
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,
Boolean breakConnection, Action`1 wrapCloseInAction)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo,
SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64
timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
integratedSecurity, Boolean withFailover)
at
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, SecureString newSecurePassword, Boolean
ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at
System.Data.SqlClient.SqlInternalConnectionTds.LoginWithFailover(Boolean
useFailoverHost, ServerInfo primaryServerInfo, String failoverHost, String newPassword,
SecureString newSecurePassword, Boolean redirectedUserInstance,
SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer
timeout)
at
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer
timeout, SqlConnectionString connectionOptions, SqlCredential credential,
String newPassword, SecureString newSecurePassword, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, SqlCredential credential,
Object providerInfo, String newPassword, SecureString newSecurePassword,
Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions,
SessionData reconnectSessionData)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo,
DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions
userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions
userOptions)
at
System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection
owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions
userOptions, DbConnectionInternal oldConnection, DbConnectionInternal&
connection)
at
System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection
outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1
retry, DbConnectionOptions userOptions)
at
System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1
retry, DbConnectionOptions userOptions)
at
System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at
System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at OpenAccessRuntime.Intellitrace.OpenConnection(DbConnection
connection, String connectionString, String id)
at Telerik.OpenAccess.Runtime.Logging.LoggingDbConnection.Open()
at
Telerik.OpenAccess.RT.Adonet2Generic.Impl.DBDriver.connect(ConnectionString
connectionString, PropertySet driverProps, ConnectionPoolType poolType,
LogEventStore pes)
--- End of inner exception stack trace ---
at
Telerik.OpenAccess.RT.Adonet2Generic.Impl.DBDriver.connect(ConnectionString
connectionString, PropertySet driverProps, ConnectionPoolType poolType,
LogEventStore pes)
at
OpenAccessRuntime.Relational.conn.RelationalConnectionPool.createRealCon()
Request information:
Request URL: http://<domain>
Request path:
/<path of page requested>
User host address: 144.160.5.25
User:
Is authenticated: False
Authentication Type:
Thread account name: WEBHOST\ws<name>
Thread information:
Thread ID: 457
Thread account name: WEBHOST\ws<name>
Is impersonating: False
Stack trace: at
OpenAccessRuntime.ExceptionWrapper.Throw()
at
OpenAccessRuntime.Relational.conn.RelationalConnectionPool.createRealCon()
at OpenAccessRuntime.Relational.conn.RelationalConnectionPool.createRealConWithRetry(Int32
retryCountParam, Int32 millis, Int32 userId)
at
OpenAccessRuntime.Relational.conn.RelationalConnectionPool.createPooledConnection(Int32
retryCountParam, Int32 retryIntervalMsParam, Int32 userId)
at
OpenAccessRuntime.Relational.conn.RelationalConnectionPool.GetManagedConnection(Boolean
highPriority, Boolean autoCommit, Int32 smId)
at
OpenAccessRuntime.Relational.conn.RelationalConnectionPool.getConnection(Boolean
highPriority, Boolean autoCommit, Int32 smId)
at
OpenAccessRuntime.Relational.RelationalStorageManager.obtainConnection(Boolean
forWriting)
Custom event details:
Now, when we started up the host and started the sql server back up, Sitefinity
then works correctly.
I have a test environment that works correctly even when we shut down the second sql server. What is missing in production that when we shut down the mirrored sql server, sitefinity no longer works?
Hello Mac,
Actually this is outside Sitefinity as it uses DataAccess ORM, which should be configured properly to handle sql failovers. The exception you shared with us is simply indicating that a connection with the database cannot be established, in other words the failover connectionstring most likely is not added properly in your settings.
There are several forum threads where this has been discussed and could help you resolve this: