locked
Best way to configure SQL failover, and connection string for SignalR RRS feed

  • Question

  • User-807406647 posted

    Expected behavior
    When SQL backplane configured for failover using 2 sql servers, connection string configured as

    <add name="SignalR" connectionString="Data Source=DEVS1;Failover Partner=DEVS2;Initial Catalog=SignalR;Application Name=SigTest;user id=SignalR;password=*****;"/>

    it is expected that if one server goes down SignalR should continue to work, switching to the failover partner

    Actual behavior
    The following error is returned:

    When using SqlDependency without providing an options value, SqlDependency.Start() must be called for each server that is being executed against.
    EXCEPTION: System.InvalidOperationException: When using SqlDependency without providing an options value, SqlDependency.Start() must be called for each server that is being executed against. at Microsoft.AspNet.SignalR.Messaging.ScaleoutStream.Send(Func2 send, Object state)
    at Microsoft.AspNet.SignalR.Infrastructure.Connection.Send(ConnectionMessage message)
    at Microsoft.AspNet.SignalR.Transports.TransportConnectionExtensions.SendCommand(ITransportConnection connection, String connectionId, CommandType commandType)
    at Microsoft.AspNet.SignalR.Transports.ForeverTransport.ProcessRequestCore(ITransportConnection connection)
    at Microsoft.AspNet.SignalR.PersistentConnection.ProcessRequestPostGroupRead(HostContext context, String groupsToken)
    at Microsoft.AspNet.SignalR.TaskAsyncHelper.FromMethod[T1,T2,T3,TResult](Func4 func, T1 arg1, T2 arg2, T3 arg3)

    Steps to reproduce

    • Configure two sql 2016 servers with an empty database for signalr, configured with mirroring/failover.
    • configure connection string as above
    • configure SignalR as:
    var connectionSettings = ConfigurationManager.ConnectionStrings["SignalR"];
    GlobalHost.DependencyResolver.UseSqlServer(new SqlScaleoutConfiguration(connectionSettings.ConnectionString));
    • Execute application functionality that uses signalR - witness notification in another browser window logged in as another user
    • Trigger failover from primary database - ensure failover is successful
    • trigger similar action in application that uses signalR
    • error is logged/displayed.

    When I fail back, the application still won't work. Things won't start working again until I reset IIS and then refresh the page.

    What is the recommended way to use failover in SQL server when configuring SignalR? Should this work?

    Wednesday, April 19, 2017 6:51 AM

All replies

  • User-1838255255 posted

    Hi moodog,

    According to your description and error message, this error may be caused by SqlDependency.Start Method lack parameters.

    Please refer to tutorial about how to use this method:

    SqlDependency.Start Method (String, String):

    https://msdn.microsoft.com/en-us/library/ms224871%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396 

    Here are someone meet the similar problem as you, please check:

    SqlDependency.Start(connectionString) return every time false:

    http://stackoverflow.com/questions/27411100/sqldependency-startconnectionstring-return-every-time-false  

    Best Regards,

    Eric Du

    Thursday, April 20, 2017 9:19 AM
  • User-807406647 posted

    According to your description and error message, this error may be caused by SqlDependency.Start Method lack parameters.

    Please refer to tutorial about how to use this method:

    I'm not actually using SqlDependency at all - it is being used by the SignalR infrastructure, i'm assuming. I'm simply configuring SignalR, and then using it through my hub. Therefore I have no option to provide parameters to SqlDependency.Start().

    I'm in a position where I believe I'm unable to use the old SQL Failover method, as it's being deprecated, and probably not supported by SignalR. I'm also unable to use Availability Groups, because the servers where this sql install is running are not on a domain, and I'm also unable to migrate to SQL 2016 in this environment, because it requires too much regression testing (although SQL2016 supports availability groups outside of a domain).

    I think I'm getting to the point where I'm unable to provide failover with SQL as a backplane for my implementation of SignalR in this specific environment...

    Wednesday, April 26, 2017 1:34 AM