CODEWITHSUNDEEP
c#.netsql-serversql-server-2012sql-server-2016
Gattaka
Gattaka

Reputation: 147

SQLServer AlwaysOn and .net SqlConnection reset

In our enviroment we use SQLServer Always on cluster with two servers.

One of them is for write, second for reading. In application services SqlConnection opens and closes every second for short query execution. But after switching or turning off one of the servers in Always On cluster I began get an exceptions from my application services.

This tells that it can not insert any data in read only database. I suppose that main reason for this is connection pool inside SqlConnection implementation.

So the question is how to reset that connection pool manually. Or if there another kind of problem - let me know what do you think about this behavior.

Upvotes: 2

Views: 1007

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300789

You need to set MultiSubnetFailover = True in connection string and implement retry logic:

If a SqlClient application is connected to an AlwaysOn database that fails over, the original connection is broken and the application must open a new connection to continue work after the failover.

SqlClient Support for High Availability, Disaster Recovery

Also:

Setting MultiSubnetFailover to true isn't required with .NET Framework 4.6.1 or later versions.

Upvotes: 2

Related Questions