SQL Server Initial database connection timeout in 30 seconds

Question

I'm using C# Web API on .Net6 (not core) to access a SQL Server database on Azure. Sometimes the database connection gets a timeout in 30 seconds.

In the Main I do this...

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContextPool<MyContext>(options => options.UseSqlServer(ConnStr));
var app = builder.Build();

I tried to change the timeout in MyContext constructor...

public MyContext(DbContextOptions<MyContext> options) : base(options)
{
    Database.SetCommandTimeout(230);    // 230 is the maximum allowed by azure
}

Sometimes this helps, but sometimes I still see timeout in 30 sec. Perhaps it is because the base(options) is taking too long.

My workaround right now is to create a "ping" API that returns hello world, and then the client must call "ping" to "wakeup" the server before attempting anything else. Obviously not an ideal solution.

Any advice how to fix better? Is it possible to set the timeout to 230 earlier, i.e. before calling the base(options) ?

Answer 1

There is a CommandTimeout and a ConnectionTimeout, and they are two distinct things. The code in the question is setting the CommandTimeout, but it's likely the ConnectionTimeout is what's actually trippig you up.

The ConnectionTimeout can be set as part of the connection string, or by accessing the DBContext's underlying ADO.Net DbConnection (DbContext.ObjectContext.Connection)