Azure Mobile Service and manual database migrations

In my previous blog post I wrote about my experience with Azure Mobile Service and Database migrations. In this blog post I will explain step by step how to get manual migration in place.

I like to have my migrations close to the domain model which I often create as a separate class library. Then I enable database migration for the Domain project using Package Manager in Visual Studio (VS) (Tools -> NuGet Package Manager -> Package Manager Control):

Enable-Migrations

By default Azure Mobile Services uses automatic migration with code first. It’s also configured to no support data loss during migrations so we need to turn this off (if you don’t care about data loss).

When database migration is enabled a Configuration.cs file will be added to the Migrations folder. To add the following to its ctor (constructor) we will disable automatic migrations and data loss is allowed during a migration:

  public Configuration()
  {
      AutomaticMigrationsEnabled = false;
      AutomaticMigrationDataLossAllowed = true;
      MigrationsAssembly = this.GetType().Assembly;
   }

Note: The MigrationsAssembly is where the assembly where the migrations are added. In my case the Migrations are added to the Domain project.

Next step is to make sure the correct database schema is used. By default Entity Framework (EF) database migration will use “dbo” as the default database schema. When creating an Azure Mobile Service from the Visual Studio project template, mobile service will create a DbContext and use the name of the service as the default database schema for EF (so “dbo” will not be the default schema).

If you locate the class that inherits the DbContext in your Mobile Service project you will see the following code:

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
      var schema = ServiceSettingsDictionary.GetSchemaName();
   
      if (!string.IsNullOrEmpty(schema)
         modelBuilder.HasDefaultSchema(schema);
   }

 
The code will make sure the EF will use the name of the mobile service as the default schema.

Note: Something to have in mind is that the name of the mobile service will be the name of the project you have created, but in Azure the name will be the name of the azure mobile service you have specified when you created the service in the Azure portal.

This key (MS_MobileServiceName) is added to appSettings in the web.config to set the name of the mobile service:

    <add key="MS_MobileServiceName" value="MySampleMobileSerivceProjectName " />

If you created your project in VS with a different name than the name you gave the Azure Mobile Service, you will get some problems when running your migrations. Because of the code in the OnModelCreating method that sets the EF default schema to mobile service name, all migrations you are added with the “Add-Migration” command will use the value of the key MS_MobileServiceName as the schema. That will cause a mismatch when you publish the code to Azure. If you have more than one Mobile Service environment in Azure, like Test and Prod, you can remove the code from the OnModelCreating and let EF use the default schema “dbo”. If you only have one Mobile Service environment, just make sure the MS_MobileServiceName is set to the name you gave the Azure Mobile service in Azure and skip down to where you see the code Database.SetInitializer in this blog post.

When creating a Mobile Service in Azure, it will add a database schema to the associated database. The schema will have the name of the Mobile Service. Permissions will also be granted to this schema. The database schema that is created need to be added as part of EF database migrations, if not migrations will fail.

Note: I will recommend you to have two Azure Mobile Service environment in Azure, one for test and one for production.

If you have two environments, just remove the code that sets the default schema in the OnModelCreating.

   var schema = ServiceSettingsDictionary.GetSchemaName();

   if (!string.IsNullOrEmpty(schema)
      modelBuilder.HasDefaultSchema(schema);

Next step is to give the Azure Mobile Service database user permission to perform actions against the “dbo” schema. When a Mobile Service is created in the Azure portal, a random database use will be created and added to the associated database. This user will have its default schema set to the name of the Mobile Service. We need to make sure that user will have its default schema change to “dbo” and some other permission against the “dbo” schema, like ALTER, SELECT, UPDATE and DELETE etc.

To make sure the database user created by Azure Mobile service gets the correct permission against the “dbo” schema, connect to the database (for example through the Microsoft SQL Server Management) and go to Users under Security of your database. You will see a user with a lot of random character and end with “LoginUser”. That is the user created by the Mobile Service. Create a new Query and write (replace <UserName> with the created Azure Mobile service user):

   ALTER USER <UserName> WITH DEFAULT_SCHEMA = dbo; 

Then the following to give the user permissions to perform action against the “dbo” schema:

   GRANT INSERT ON SCHEMA :: dbo TO <UserName>
   GRANT SELECT ON SCHEMA :: dbo TO <UserName>
   GRANT ALTER ON SCHEMA :: dbo TO <UserName>
   GRANT DELETE ON SCHEMA :: dbo TO <UserName>
   GRANT UPDATE ON SCHEMA :: dbo TO <UserName>
   GRANT CONTROL ON SCHEMA :: dbo TO <UserName>
   GRANT EXECUTE ON SCHEMA :: dbo TO <UserName>

The last step is to make sure the Database Migration will be added while the Azure Mobile service starts. By doing that, add the following code to the WebApiConfig.cs in the  App_Start folder of your Mobile Service project:

   Database.SetInitializer<YourEntityContext>(null);

   var migrator = new DbMigrator(new Configuration());
   migrator.Update();

Note: There is probably already a Database.SetInitializer in you code, just replace it with the code above, just make sure you specify your name of the EntityContext.

The SetInializer method will null as an argument will make sure no migrations will take place while there are some changes to your model the first time your code touches the EF DbContext. The code will disable that. Instead the DbMigrator will be used to update the database while the Azure Mobile Service is started.

If everything is done correctly, you can now use manual database migrations, the key thing to get it working is to give the correct user the right permissions to a database schema.

If you want to know when I will post a new blog post, please follow me on twitter @fredrikn