How to connect SQL server instance (Always on) with EF6.4 (.NET framework 4.8 Application in C#)

Question

How to connect SQL server instance (Always on) with EF6.4 (.NET framework 4.8 Application in C#)

Leo Ng 20

I am using Entity framework 6.4 (C# application) to connect SQL server database(always on) via availability group listener. I found that my application will throw exception when SQL server proceed failover from primary SQL instant to secondary instant. My questions are: Q1. Any connection string setting is needed to add for always on SQL server instance? Q2. Any best practice for Entity framework to connect SQL Server with always on? Thanks

0 comments

Answer accepted by question author

Bruce (SqlWork.com) 84,086

while the underlaying ado library can auto reconnect, it will still throw an error. your error handling code should do the proper cleanup and retry. the complexity of this depends on the transaction logic. the recovery logic should be similar to handling a deadlock which your code can always get.

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2024-01-16T21:59:24.4633333+00:00
the recovery logic should be similar to handling a deadlock which your code can always get.

Good point. I have a talk about deadlocks where I tell be people to be cautions about implementing deadlock retry, and many of my warnings to apply to reconnect after an error due to a failover.

There are two important key rules:

Never redo only part of a transaction. From this follows that you can't to retry if you were called inside a transaction, but you must just pass the error/exception to the caller.

Never redo a committed transaction. Say that you call a stored procedure that accepts a TVP with actions to perform, and the SP runs one transaction per row in the TVP. (Because some actions could fail.) If you retry the procedure call, some actions may be re-performed.

The latter is something you can avoid with good coding guidelines and senior programmers.

But it may be better to let all updating operations actually crash and error out on a deadlock or a failover for a manual retry. Readonly operations are usually safe to retry. And for readonly operations, SqlClient may save you, as there is something known as connection resilliency, which does what Leo asks for - but only when it's safe.
Leo Ng 20 Reputation points

2024-01-23T08:57:33.8066667+00:00

I agreed that connection retry should be handled by code. I have tried "failover partner" keyword in connection string cannot handle failover issue when proceeding transaction. Retry logic should not be applied to all situation. Thanks

1 additional answer

Your answer

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2024-01-16T21:59:24.4633333+00:00

the recovery logic should be similar to handling a deadlock which your code can always get.

Good point. I have a talk about deadlocks where I tell be people to be cautions about implementing deadlock retry, and many of my warnings to apply to reconnect after an error due to a failover.

There are two important key rules:

Never redo only part of a transaction. From this follows that you can't to retry if you were called inside a transaction, but you must just pass the error/exception to the caller.

Never redo a committed transaction. Say that you call a stored procedure that accepts a TVP with actions to perform, and the SP runs one transaction per row in the TVP. (Because some actions could fail.) If you retry the procedure call, some actions may be re-performed.

The latter is something you can avoid with good coding guidelines and senior programmers.

But it may be better to let all updating operations actually crash and error out on a deadlock or a failover for a manual retry. Readonly operations are usually safe to retry. And for readonly operations, SqlClient may save you, as there is something known as connection resilliency, which does what Leo asks for - but only when it's safe.
Leo Ng 20 Reputation points

2024-01-23T08:57:33.8066667+00:00

I agreed that connection retry should be handled by code. I have tried "failover partner" keyword in connection string cannot handle failover issue when proceeding transaction. Retry logic should not be applied to all situation. Thanks

Answer 1

Olaf Helper 47,621

I found that my application will throw exception when SQL server proceed failover

Physical client connections can't be moved to secondary on a failover, the application have to re-connect.

Leo Ng 20 Reputation points

2024-01-15T01:15:27.1333333+00:00

Does it mean that application need to implement re-connection in code? Any connection string keyword can implement similar re-connection without code change? Thanks
Wenbin Geng 746 Reputation points Microsoft External Staff

2024-01-15T07:51:25.2433333+00:00

@Leo Ng If you are using EF Core you don't have to worry about this issue. For example, you will only get an exception when the connection is interrupted during saving changes. The entire save is done within a transaction, is not committed due to an exception, and EF does not mark its current changeset as completed. Just try saving the changes again - EF will try to open a new connection and perform the same transaction.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2024-01-15T21:47:01.84+00:00

Does it mean that application need to implement re-connection in code? Any connection string keyword can implement similar re-connection without code change? Thanks

I hope not. The client API does not know the context of the call. If you are running a mult-batch transaction, and the during the fourth call there is a failover, you need to restart the transaction and redo all four calls. The client API does not have the knowledge to do that.

Share via

How to connect SQL server instance (Always on) with EF6.4 (.NET framework 4.8 Application in C#)

1 additional answer

Your answer