Entity Framework Database Initialization: Timeout when initializing new Azure SqlDatabase

Question

I have an ASP.NET MVC application. When a new customer is created via CustomerController I run a new background task (using HostingEnvironment.QueueBackgroundWorkItem) to create a new Azure SqlDatabase for that customer.

I use Entity Framework Code First to create/initialize the new database. Here's the code:

// My ConnectionString
var con = "...";

// Initialization strategy: create db and execute all Migrations
// MyConfiguration is just a DbMigrationsConfiguration with AutomaticMigrationsEnabled = true
Database.SetInitializer(strategy: new MigrateDatabaseToLatestVersion<CustomerDataContext, MyConfiguration>(useSuppliedContext: true));

using (var context = new CustomerDataContext(con))
{
    // Neither 'Connection Timeout=300' in ConnectionString nor this line helps -> TimeoutException will rise after 30-40s
    context.Database.CommandTimeout = 300;

    // create the db - this lines throws the exception after ~40s
    context.Database.Initialize(true);
}

My Problem is that I always get a TimeoutException after about 40secs. I think that happens because Azure cannot initialize the new database within this short period of time. Don't get me wrong: The database will be created well by Azure but I want to wait for that point / get rid of the TimeoutException.

Edit1: I'm using Connection Timeout=300 in my ConnectionString but my app doesn't really care about that; after about 40s I'm always running into an SqlError.

Edit2: The exception that raises is an SqlException. Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Source: .Net SqlClient Data Provider

Edit3: I can confim now that this has nothing to do with ASP.NET/IIS. Even in a simple UnitTest method the code above fails.

Can you share the actual exception that you are seeing, with the full error message? Thx! — Torsten Grabs
– Torsten Grabs, Commented Mar 3, 2016 at 16:19
There is also a CommandTimeout property in your migrations configuration class, can you try setting that one. msdn.microsoft.com/en-us/library/… — Rowan Miller
– Rowan Miller, Commented Mar 7, 2016 at 22:18
OMG that dit it! Thank you so much for that hint Mr. Miller! It's very sad that the Azure Developer Support couldn't point me to that solution. Again, thank you so much. — mmmato
– mmmato, Commented Mar 11, 2016 at 10:34

Community · Accepted Answer · 2017-05-23 12:17:06Z

It seems that there is another CommandTimeout setting that is involved in database initialization process when using Code First Migrations. I want so share my solution here just in case anybody encounters this problem too.

Thanks to Rowan Miller for his hint pointing me to the solution.

Here's my code:

// Initialisation strategy
Database.SetInitializer(strategy: new CreateDatabaseIfNotExists<MyDataContext>());

// Use DbContext
using (var context = new MyDataContext(myConnectionString))
{
    // Setting the CommandTimeout here does not prevent the database
    // initialization process from raising a TimeoutException when using
    // Code First Migrations so I think it's not needed here.
    //context.Database.CommandTimeout = 300;

    // this will create the database if it does not exist
    context.Database.Initialize(force: false);
}

And my Configuration.cs class:

public sealed class Configuration : DbMigrationsConfiguration<MyDataContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        AutomaticMigrationDataLossAllowed = false;

        // Very important! Gives me enough time to wait for Azure
        // to initialize (Create -> Migrate -> Seed) the database.
        // Usually Azure needs 1-2 minutes so the default value of
        // 30 seconds is not big enough!
        CommandTimeout = 300;
    }
}

Peter · Accepted Answer · 2016-03-03 09:31:54Z

1

The command timeout and the connection timeout are two different settings. In this case you only increase the commandtimeout. You can increase the connection timeout in the web.config: Connection Timeout=120. The only time you want to increase the connection timeout is when you are creating the database.

answered Mar 3, 2016 at 9:31

Peter

28k8 gold badges69 silver badges85 bronze badges

1 Comment

mmmato Over a year ago

Thanks for your hint but unfortunately that does not change anything. My ConnectionString is already using Connection Timeout: Server=XXX.database.windows.net;Database=DB_Customer_34;User Id=XXX;Password=XXX;Connection Timeout=240;MultipleActiveResultSets=True;Encrypt=True;App=EntityFramework

Collectives™ on Stack Overflow

Entity Framework Database Initialization: Timeout when initializing new Azure SqlDatabase

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related