Can Entity Framework Core bet setup to use a backup connection string?

Question

My company is using 2 Windows servers. 1 Server is running as a backup server and other than SQL replication, the backup server requires manual intervention to get it running as the primary. I have no control over this, but I do have control of the apps/services running on the servers.

What I have done is I got all the services to be running on both and added Rabbit MQ as a clustered message broker to kind of distribute the work between the servers. This is all working great and when I take a server down, nothing is affected.

Anyway, to the point of the question, the only issue I see is that the services are using the same SQL server and I have nothing in place to automatically switch server if the primary goes down.

So my question is, is there a way to get Entity Framework to use an alternative connection string should one fail?

I am using the module approach with autofac as dependency injection for my services. This is the database registration.

 public class AppsDbModule : Module
{
    protected override void Load(ContainerBuilder builder)
    {
        RegisterContext<AppsDbContext>(builder);
    }

    private void RegisterContext<TContext>(ContainerBuilder builder) where TContext : DbContext
    {
        builder.Register(componentContext =>
            {
                var serviceProvider = componentContext.Resolve<IServiceProvider>();
                var configuration = componentContext.Resolve<IConfiguration>();
                var dbContextOptions = new DbContextOptions<TContext>(new Dictionary<Type, IDbContextOptionsExtension>());
                var optionsBuilder = new DbContextOptionsBuilder<TContext>(dbContextOptions)
                    .UseApplicationServiceProvider(serviceProvider)
                    .UseSqlServer(configuration.GetConnectionString("AppsConnection"),
                        serverOptions => serverOptions.EnableRetryOnFailure(5, TimeSpan.FromSeconds(30), null));

                return optionsBuilder.Options;
            }).As<DbContextOptions<TContext>>()
            .InstancePerLifetimeScope();

        builder.Register(context => context.Resolve<DbContextOptions<TContext>>())
            .As<DbContextOptions>()
            .InstancePerLifetimeScope();

        builder.RegisterType<TContext>()
            .AsSelf()
            .InstancePerLifetimeScope();
    }
}

and my appsettings.json as this

"ConnectionStrings": {
"AppsConnection": "Data Source=primary;Initial Catalog=Apps;User Id=me;Password=topsecret"
}

Couldn't really find anything on the web, other than posts where you was in full control of creating the db connection, but I am being supplied the connection via DI.

Using .Net 5 and the applications are worker services.

Answer 1

You can define on custom retry strategy on implementing the interface IExecutionStrategy. If you want reuse the default SQL Server retry strategy, you can derive from SqlServerRetryingExecutionStrategy on override the method ShouldRetryOn :

public class SqlServerSwitchRetryingExecutionStrategy : SqlServerRetryingExecutionStrategy
{
    public string _switchConnectionString;

    public SqlServerSwitchRetryingExecutionStrategy(ExecutionStrategyDependencies dependencies, string switchConnectionString)
        : base(dependencies, 3)
    {
        _switchConnectionString = switchConnectionString;
    }

    protected override bool ShouldRetryOn(Exception exception)
    {
        if (exception is SqlException sqlException)
        {
            foreach (SqlError err in sqlException.Errors)
            {
                switch (err.Number)
                {
                    // For this type of error, switch the connection string and retry
                    case 1418: // The server can't be reached or does not exist
                    case 4060: // Cannot open database
                    case 4064: // Cannot open user default database database
                        var db = Dependencies.CurrentContext.Context.Database;
                        var current = db.GetConnectionString();
                        if(current != _switchConnectionString)
                            db.SetConnectionString(_switchConnectionString);
                        return true;
                }
            }
        }
        return base.ShouldRetryOn(exception);
    }
}

I am not sure which errors to catch for your scenario. You should test and find the errors to handle. The full list is available Database engine errors.

To inject the strategy :

new DbContextOptionsBuilder<TContext>(dbContextOptions)
    .UseSqlServer(
        configuration.GetConnectionString("AppsConnection"),
        serverOptions => serverOptions.ExecutionStrategy(dependencies =>
            new SqlServerSwitchRetryingExecutionStrategy(
            dependencies,
            configuration.GetConnectionString("AppsConnectionBackup"))
        )
    );

If you want a full custom strategy, you can get inspiration from SqlServerRetryingExecutionStrategy.