none
.Net Native Client behavior RetryCount during Always On failover switch

    Question

  • Hi All,

    I'm running sql2016 SP1 in Always on configuration for two room connected via infiniband.

    When both node are on the lag in replica is quite invisible  (insert delete update are as fast as standalone instance)

    we have used the property: RetryCount = 5  (Connection Delay should be 10 seconds).

    During failover test with a simulation on 5 processes 100 sql threads each with a mixture of insert update delete got a

    black hole of 5 seconds before returning to the promoted node

    SqLException:

    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

    This exception is clear but the expected behavior was retry 5 times in next 5 * 10 seconds.

    Maybe I've missed some other configuration step.

    Ciao

    Diego


    Diego scaravaggi (TXT e-Solutions)


    Wednesday, May 16, 2018 4:06 PM

All replies

  • Hi dscaravaggi,

    According to your description, you have set the value 5 for RetryCount, it will try 5 reconnection attempts if there is a connection failure.

    Have you configured the Connect retry interval? The connect retry interval specifies the number of seconds between each connection retry attempt. The default value is 10 seconds.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 17, 2018 7:00 AM
  • Yes the interval is 10 seconds (default values) the exact point where we got sqlexception is EF6 SaveChanges(), the expected behavior in a cluster always on shoould be "transparent" to application code.

    As further information I read this interesting article abount connection reties:

    Connection Resiliency and Command Interception with the Entity Framework in an ASP.NET MVC Application

    but this requires a little bit code rewriting

    Hi dscaravaggi,

    According to your description, you have set the value 5 for RetryCount, it will try 5 reconnection attempts if there is a connection failure.

    Have you configured the Connect retry interval? The connect retry interval specifies the number of seconds between each connection retry attempt. The default value is 10 seconds.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Diego scaravaggi (TXT e-Solutions)

    Thursday, May 17, 2018 9:01 AM
  • Hi All,

    while waiting a better solutions I overrided SaveChanges

    public override int SaveChanges()
            {      
    int r = 0;
                try
                {
                    r = base.SaveChanges();
                }
                catch (Exception e)
                {
                    //Try the operation again later
                    if (e.Message.ToLower().Contains("network") || (e.InnerException!= null && e.InnerException.Message.ToLower().Contains("network"))
                        || e.Message.ToLower().Contains("try the operation again later")
                        || (e.InnerException != null && e.InnerException.Message.ToLower().Contains("try the operation again later"))) 
                    {
                        // second chance
                        System.Threading.Thread.Sleep(millisecondsTimeout: RndMillisec());
                        r = base.SaveChanges();
                    } else
                    throw;
                }
                return r;
            }
    



    Diego scaravaggi (TXT e-Solutions)

    Friday, May 18, 2018 2:23 PM
  • I tried to make this work under Azure SQL which presumably uses some flavor of Always On to do service level changes, and found there were a dozen different error codes to map, the Entity Framework would support a retry automagically in newer releases, but EVERY call had to be carefully wrapped like this, and there always seemed to be "just one more use case", and I gave it up

    Josh

    Friday, May 18, 2018 11:05 PM