Using ASP.NET SignalR 2.4.1 (not ASP.NET Core SignalR), we often experience errors such as the following being logged in the SignalR transport and bus trace logs when testing SQL failover (failing over the SQL Server that hosts the scaled-out SignalR database) (Microsoft SQL Server 2016 (SP2-CU8) hosted on Windows; SignalR hosted in IIS 10 on Windows Server 2016):
SignalR.ScaleoutMessageBus Error: 0 : Stream(2) - Error has happened with the following exception: System.Data.SqlClient.SqlException (0x80131904): Unable to access availability database 'SignalR' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later. at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) 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, TaskCompletionSource1 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(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource
1 retry) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.AspNet.SignalR.SqlServer.DbOperation.Execute[T](Func2 commandFunc) at Microsoft.AspNet.SignalR.SqlServer.ObservableDbOperation.ExecuteReaderWithUpdates(Action`2 processRecord) ClientConnectionId:e71eb6d2-a6e6-405c-a9d4-e3c1cf8b07b8 Error Number:983,State:1,Class:14.
Sometimes other conditions are shown such as:
SignalR.ScaleoutMessageBus Error: 0 : Stream(2) - Error has happened with the following exception: Microsoft.AspNet.SignalR.SqlServer.SqlMessageBusException: An unexpected SqlNotificationType was received. Details: Type=Change, Source=Client, Info=Error. DateTime=2020-06-05T12:48:31.9873595Z
The DBA is confident that SQL failover is configured correctly and therefore we do not understand why SignalR logs that the database replica is in the wrong role. Typically after a failover, SignalR stops working, although recycling the IIS application pool usually (not always) fixes it. Our project managers would like to know whether we can be confident that ASP.NET SignalR is SQL-failover-compatible and whether a different "driver" can be swapped in. The driver used by SignalR to connect to SQL is the Microsoft SqlClient DLL and our information is that this certainly should be compatible (.NET 4.6.2 is installed). SignalR's connection string for the scaleout DB is as follows:
Server=OURSERVERNAME,14481;MultiSubnetFailover=True;Database=SignalR;Integrated Security=SSPI;Connect Timeout=45;Max Pool Size=400;
Does anyone have any ideas about this?
User contributions licensed under CC BY-SA 3.0