SQL Server 2014 Cluster Failover Instance, how to reconnect on failover?

Question

I've setup an Windows Server Failover Cluster in a test environmnet, and installed a clustered SQL Server 2014 instance on it. The cluster has two nodes, and the network name is SQLINSTANCE.

Now from small console application i have a simple loop (which runs fine) that queries a test database every 100ms:

while (true)
{
   using (var dbContext = new TestDBContext())
   {
       foreach (var person in dbContext.People)
       {
            Console.WriteLine(person.FirstName + " " + person.LastName);
            System.Threading.Thread.Sleep(100);
       }
    } 
 }

Now i force a failover on the cluster (either by shutting down one node, or moving the SQL server role to the other node from Cluster Manager). The failover process takes about 50 seconds. When failover is comlete, my test-loop throws an EntityException, saying "the underlying provider failed on open". The InnerException is a SqlClient.SqlException, saying "Cannot open database "TestDB" requested by the login. The login failed. Login failed for user 'Domain\User'."

I've setup my connection string like this:

 <add name="TestDBEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;
         provider connection string=&quot;data source=sqlinstance;initial catalog=TestDB;integrated security=True;
         MultiSubnetFailover=true;
         connect timeout=150;
         ConnectRetryCount=15;
         ConnectRetryInterval=10;
         MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

What i was expecting is that the SqlClient tries to reconnect to my clusterd instance for 15 times, with 10 secondes interval, and then successfull reconnects when the failover is complete. But it's still keeps throwing exceptions.

I tried to change the "Connect Timeout", "ConnectRetryCount" and "ConnectRetryInterval" values, but still there's no success full reconnect after failover...

I'm very confused, what am i missing here, or can i not rely on the underlying SqlClient and do i have to provide my owm retry logic in the DAL for example???

Answer 1

I am not an expert on all of this, especially, I am not sure how C# handles the db connection. What I have seen on one of our applications was that you open a connection with the db in the AlwaysOn cluster and when you fail over the application the connection is still using the same underlying TCP connection that is actually going to the node which by now has changed. For us the solution was to properly reconnect on catching an exception.