[Database Mirror] Client cannot be redirected to new principal db.

Question

[Database Mirror] Client cannot be redirected to new principal db.

walter.shiu

SSC Veteran

Points: 212
More actions
November 22, 2017 at 4:03 am

#329629

Hi Experts
I have 3 sql servers in the architecture of database mirror(with a witness server). When I test failover, sql server works fine but
client (c#) cannot be redirected to new principal server after executing (Manual) Failover.
The detail information as below
Sql Server is Sql server 2014 sp1 (with hotfix to cum13). The database architecture is mirroring failover with a witness server that can perform auto failover when principal is offline.
There are three sql servers and all with the same version (sql server sp1, cum13)
a.Principal (synchronized)
b.Mirror (synchronized/Restoring)
c.Witness
Client is web application (c sharp) .net running on .net framework (4.5, 4.6, 4.7).
The connection string is as belows:
provider=System.Data.SqlClient;

provider connection string

data source=Principal;

failover partner=Mirror;

MultipleActiveResultSets=True;

App=EntityFramework

Connection timeout=15 (default value as I know)
Executing manual failover by SSMS during client is W/Reading principal db. The exception(client side) shows “The underlying provider failed on Open”.(detail log is appended) In this time, I can see principal db become mirror db and the client still only tries to connect to old principal db. But client can connect to new principal db after recycling ApplicationPool.
Secondly, if I set the parameter “connection timeout” to 60 seconds and try again. I can see failure in minutes (<= 5 mins), but client finally can connect to new principal db.

Except adjusting "connection timeout", anyone has other suggestion?
Detail of Sql server version
Microsoft SQL Server 2014 (SP1-CU13) (KB4019099) - 12.0.4522.0 (X64)
Jun 28 2017 17:36:31
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Detail of .net framework on client side
4.7.02558
4.6
4.5.51209
Client-side Exception Log
Echo exception, Message: The underlying provider failed on Open., StackTrace:    at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
  at System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)
  at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
  at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
  at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
  at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
  at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
  at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
  at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
  at System.Linq.Queryable.Count[TSource](IQueryable`1 source)
System.Data.SqlClient.SqlException (0x80131904): 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 (0x80004005): The network path was not found
  at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
  at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
  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.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
  at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
  at System.Data.SqlClient.SqlConnection.Open()
  at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
  at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext)
  at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0()
  at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
  at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
ClientConnectionId:b066c399-dc81-4ba6-a0f5-9c60c5ba469a
Error Number:53,State:0,Class:20
System.ComponentModel.Win32Exception (0x80004005): The network path was not found

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

andreas.kreuzberg SSCertifiable Points: 6183 More actions · Answer 1

Hi,
are there any entries in ERRORLOG?
"Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
Named Pipes are only local protocolls, are the mirrors on diffferent hosts, ore on the same host in different instaneces?
Who is the owner of the mirroring endpoints?
Are you able to telnet the mirroring ports?
Are you using firewalls?

Are there some errros in the mirroring monitor?

Kind regards,
Andreas

walter.shiu SSC Veteran Points: 212 More actions · Answer 2

andreas.kreuzberg - Thursday, November 23, 2017 3:25 AM
Hi,
are there any entries in ERRORLOG?
"Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
Named Pipes are only local protocolls, are the mirrors on diffferent hosts, ore on the same host in different instaneces?
Who is the owner of the mirroring endpoints?
Are you able to telnet the mirroring ports?
Are you using firewalls?
Are there some errros in the mirroring monitor?
Kind regards,
Andreas

You are right. I didn't enable named piped.

Now I added "Network=dbmssocn" and set IP to "Server" and "Failover Partner" as below"

connection string="data source=xxx.xxx.23.5;failover partner=xxx.xxx.25.56;Network=dbmssocn;user id=username;password=password;Initial Catalog=dbname;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />

Firewall is set off.
Telnet 1433 to Prin,Miror can work.

Got another exception when executed manual failover.

Echo exception, Message: The underlying provider failed on Open., StackTrace: at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
at System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
at System.Linq.Queryable.Count[TSource](IQueryable`1 source)
65|System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> 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: TCP Provider, error: 0 - No such host is known.) ---> System.ComponentModel.Win32Exception: No such host is known
--- End of inner exception stack trace ---
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
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.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext)
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
--- End of inner exception stack trace ---
at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
at System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
at System.Linq.Queryable.Count[TSource](IQueryable`1 source)

walter.shiu SSC Veteran Points: 212 More actions · Answer 3

Another observation is when I set "connection timeout" to 60 seconds, then client can be redirected to new principal db in few times failure.

connection string="data source=xxx.xxx.23.5;failover partner=xxx.xxx.25.56;Network=dbmssocn;user id=username;password=password;connection timeout=60;Initial Catalog=dbname;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />