SQL Azure unexpected database deletion/recreation

Question

I've been scratching my head on this for hours, but can't seem to figure out what's wrong.


Here's our project basic setup:

• MVC 3.0 Project with ASP.NET Membership
• Entity Framework 4.3, Code First approach
• Local environment: local SQL Server with 2 MDF database files attached (aspnet.mdf + entities.mdf)
• Server environment: Windows Azure + 2 SQL Azure databases (aspnet and entities)

Here's what we did:

• Created local and remote databases, modified web.config to use SQLEXPRESS connection strings in debug mode and SQL Azure connection strings in release mode
  
• Created a SampleData class extending DropCreateDatabaseAlways<Entities> with a Seed method to seed data.
  
• Used System.Data.Entity.Database.SetInitializer(new Models.SampleData()); in Application_Start to seed data to our databases.
  
• Ran app locally - tables were created and seeded, all OK.
  
• Deployed, ran remote app - tables were created and seeded, all OK.
  
• Added pre-processor directives to stop destroying the Entity database at each application start on our remote Azure environment:
  
  

#if DEBUG    
    System.Data.Entity.Database.SetInitializer(new 
    Models.SampleData());
#else    
    System.Data.Entity.Database.SetInitializer<Entities>(null);
#endif

Here's where it got ugly

• We enabled Migrations using NuGet, with AutomaticMigrationsEnabled = true;
  
• Everything was running smooth and nice. We left it cooking for a couple days
  
• Today, we noticed an unknown bug on the Azure environment:
  
• we have several classes deriving from a superclass SuperClass
• the corresponding Entity table stores all of these objects in the same SuperClass table, using a discriminator to know which column to feed from when loading the various classes
• While the loading went just fine before today, it doesn't anymore. We get the following error message:
  
  

The 'Foo' property on 'SubClass1' could not be set to a 'null' value. You must set this property to a non-null value of type 'Int32'.

• After a quick check, our SuperClass table has columns Foo and Foo1. Logical enough, since SuperClass has 2 subclasses SubClass1 and SubClass2, each with a Foo property. In our case, Foo is NULL but Foo1 has an int32 value. So the problem is not with the database - rather, it would seem that the link between our Model and Database has been lost. The discriminator logic was corrupted.
• Trying to find indications on what could've gone wrong, we noticed several things:
• Even though we never performed any migration on the SQL Azure Entity database, the database now has a _MigrationHistory table
• The _MigrationHistory table has one record:

MigrationID: 201204102350574_InitialCreate
CreatedOn: 4/10/2012 11:50:57 PM
Model: <Binary data>
ProductVersion: 4.3.1

• Looking at other tables, most of them were emptied when this migration happened. Only the tables that were initially seeded with SampleData remained untouched.
• Checking in with the SQL Azure Management portal, our Entity database shows the following creation date: 4/10/2012 23:50:55.

Here's our understanding

• For some reason, SQL Azure deleted and recreated our database
  
• The _MigrationHistory table was created in the process, registering a starting point to test the model against for future migrations
  
  

Here are our Questions

• Who / What triggered the database deletion / recreation?
  
• How could EF re-seed our sample data since Application_Start has

System.Data.Entity.Database.SetInitializer<Entities>(null);

?

EDIT: Looking at what could've gone wrong, we noticed one thing we didn't respect in this SQL Azure tutorial: we didn't remove PersistSecurityInfo from our SQL Azure Entity database connection string after the database was created. Can't see why on earth this could have caused the problem, but still worth mentioning...

Answer 1

Nevermind, found the cause of our problem. In case anybody wonders: we hadn't made any Azure deployment since the addition of the pre-processor directives. MS must have restarted the machine our VM resided on, and the new VM recreated the database using see data.

Lesson learned: always do Azure deployments for stable releases.