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

Leo Ng 20 Reputation points
2024-01-12T05:58:16.49+00:00

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

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
776 questions
.NET
.NET
Microsoft Technologies based on the .NET software framework.
4,090 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,432 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bruce (SqlWork.com) 70,776 Reputation points
    2024-01-16T17:26:14.04+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. Olaf Helper 45,811 Reputation points
    2024-01-12T07:06:37.4266667+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.