Initialization of the Entity Framework database: timeout while initializing a new Azure SqlDatabase

Question

I have an ASP.NET MVC application. When a new customer is created through the CustomerController, I start a new background job (using HostingEnvironment.QueueBackgroundWorkItem) to create a new Azure SqlDatabase for this customer.

I use Entity Framework Code First to create / initialize a new database. Here is 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 40 seconds. I think this is because Azure cannot initialize a new database in this short amount of time. Do not get me wrong: the database will be created well by Azure, but I want to wait for this point / get rid of a TimeoutException.

Edit1: I use Connection Timeout = 300 in my ConnectionString, but my application really doesn't care about that; after about 40 seconds I always encounter a SqlError.

Edit2: An exception is a SqlException . Message: Timed out. The wait period expires before the operation is completed or the server is not responding. Source: .Net SqlClient Data Provider

Edit3: Now I can say that this has nothing to do with ASP.NET/IIS. Even in the simple UnitTest method, the code above does not work.

Answer 1

There seems to be another CommandTimeout parameter that is involved in the database initialization process when using First First Migrations. I want this to share my solution here, in case anyone encounters this problem.

Thanks to Rowan Miller for the tip indicating the solution.

Here is 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 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; } } 

Answer 2

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