EF Code First Migrations Deployment to an Azure Cloud Service

EF Code First Migrations Deployment to an Azure Cloud Service

Rate This
  • Comments 4

To deploy a Code First database to an Azure Web Site, you can use the Execute Code First Migrations check box in the Publish Web wizard:

blog1

When you select that check box, Visual Studio configures the destination web site so that Entity Framework automatically deploys the database or updates it by running the MigrateDatabaseToLatestVersion initializer on application start, as explained in this VS web deployment tutorial.

But if you’re deploying to an Azure cloud service you don’t get to use the Publish Web wizard. What then?

In that case, you have two options:  

  • Write code that executes migrations when the application starts.
  • Write Web.config transforms to configure the MigrateDatabaseToLatestVersion initializer to run.

Write App_Start code to run Migrations

You can run migrations manually from the Application_Start method of Global.asax with the following code:

Code Snippet
  1. var configuration = new MvcWebRole.Migrations.Configuration();
  2. var migrator = new DbMigrator(configuration);
  3. migrator.Update();

In this snippet, Configuration is the migrations Configuration class from your Migrations folder. You can optionally specify a connection string to be used for the migration; for an example, see Running & Scripting Migrations from Code on Rowan Miller’s blog.

However, you probably don’t want to call migrations every time the app runs, so a better solution is to use a Web.config file setting or a role environment setting to control it. For example, add the following appSettings key to the Web.config file:

Code Snippet
  1. <appSettings>
  2.   <add key="MigrateDatabaseToLatestVersion" value="true"/>
  3. </appSettings>

And only run migrations if the setting is true:

Code Snippet
  1. if (bool.Parse(ConfigurationManager.AppSettings["MigrateDatabaseToLatestVersion"]))
  2. {
  3.     var configuration = new MvcWebRole.Migrations.Configuration();
  4.     var migrator = new DbMigrator(configuration);
  5.     migrator.Update();
  6. }

With this method you can enable or disable the update-to-latest functionality locally by editing the Web.config file, and in a deployed site by using a Web.config transform. For example, you could set the setting to false for development and use the following code in Web.Release.config to enable it in production:

Code Snippet
  1. <configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
  2.   <appSettings>
  3.     <add key="MigrateDatabaseToLatestVersion" value="true" xdt:Locator="Match(key)" xdt:Transform="SetAttributes"/>
  4.   </appSettings>
  5. </configuration>

The xdt:Locator attribute specifies that the add element with the same key value is the one to be updated in the deployed Web.config file, and the xdt:Transform element specifies that value=”true” from the transform file will be used in the destination web site, even if value=”false” is in the Web.config file.

(For an Azure cloud service an alternative is to use a setting in the .cscfg file and use RoleEnvironment.GetConfigurationSettingValue to retrieve the setting value.)

Write Web.config transforms to configure MigrateDatabaseToLatestVersion

Another option is to set up Web.config transforms to make the same changes to the Web.config file that Visual Studio does when you click Execute Code First Migrations. For example, the following sample Web.Release.config file will configure the MigrateDatabaseToLatestVersion initializer to run, and the initializer will use a connection string other than the application connection string to update the database schema.

Code Snippet
  1. <configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
  2.   <connectionStrings>
  3.     <add name="DefaultConnection"
  4.       connectionString="Server=tcp:server.database.windows.net,1433;Database=database;User ID=dbuser@server;Password=password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"
  5.       providerName="System.Data.SqlClient" xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
  6.       <add name="DefaultConnection_DatabasePublish" connectionString="Server=tcp:server.database.windows.net,1433;Database=database;User ID=dbuser@server;Password=password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" providerName="System.Data.SqlClient" xdt:Transform="Insert"/>
  7.     </connectionStrings>
  8.     <entityFramework>
  9.       <contexts xdt:Transform="Insert">
  10.         <context type="MvcWebRole.Models.ApplicationDbContext, MvcWebRole">
  11.           <databaseInitializer type="System.Data.Entity.MigrateDatabaseToLatestVersion`2[[MvcWebRole.Models.ApplicationDbContext, MvcWebRole], [MvcWebRole.Migrations.Configuration, MvcWebRole]], EntityFramework, PublicKeyToken=b77a5c561934e089">
  12.             <parameters>
  13.               <parameter value="DefaultConnection_DatabasePublish" />
  14.             </parameters>
  15.           </databaseInitializer>
  16.         </context>
  17.       </contexts>
  18.     </entityFramework>
  19. </configuration>

This sample transform code updates the existing DefaultConnection connection string and adds a new one named DefaultConnection_DatabasePublish for the initializer to use. The code also adds to the <entityFramework> element a <contexts> element that specifies the DbContext class and specifies that the MigrateDatabaseToLatestVersion initializer should be used with that class. Note that the new connection string and the <contexts> element have xdt:Transform=Insert attributes.

The code as shown works with a web project as created with the standard Visual Studio 2013 template for new web projects. If your project is different you can find the code you need as follows:

  1. Create a publish profile for publishing the web project to an Azure web site (not a cloud service).
  2. Select the Execute Code First Migrations check box and publish.
  3. In Server Explorer, expand the node for your web site under the Azure – Web Sites node, expand Files, and double-click the application Web.config file.
  4. You now see what Visual Studio did to enable migrations deployment, and you can copy the connection string element and the <contexts> element, paste them into a transform file, and add the xdt:Transform attributes.

If your application connection string has credentials for a user that has permissions to modify the database schema, you can simplify this code by not adding a new connection string, and by specifying the application connection string in the <parameter> element under entityFramework/context/context/databaseInitializer.

Choosing the right method

For most scenarios adding code to Application_Start is the best choice:

  • When you’re debugging, you can see and step through the code – there’s no magic happening behind the scenes due to Web.config settings.
  • The transform code for appSettings is much simpler than the transform code to configure an initializer. In addition, in Azure it can be even easier to change the setting because you can do it by changing the value of an environment variable.
  • If you ever change your context name, your code that runs migrations doesn’t change. Web.config transform code that configures an initializer would have to change.

An advantage of the Web.config transforms shown above is that they enable you to specify a connection string for the initializer to use for schema-modification work, different from the connection string used by the application itself. You could then implement the security best practice of using credentials that have only the minimum permissions required: CRUD permissions for the application, and database owner for migrations. The blog mentioned earlier shows how to do this for the code method, but of course if you do that you have more work to do in the Web.config and transform files as well as in code, so you lose some of the relative simplicity of the code method. See the discussion about multiple connection strings in ASP.NET Web Deployment using Visual Studio: Deploying to Test.

Summary

When you select the Execute Code First Migrations check box in the Publish Web wizard, Visual Studio automatically configures the destination Web.config file for migrations deployment. This post has shown some alternatives for accomplishing migrations deployment when Visual Studio doesn’t display the wizard or the check box.

Thanks to Rowan Miller, Sayed Hashimi, and Brady Gaster for providing some of the code and content for this post and reviewing it.

Leave a Comment
  • Please add 3 and 5 and type the answer here:
  • Post
  • And what if you have multiple instances, and they both fire Application_Start and try and update the database?

  • Good question, but it applies to all deployments using migrations, i.e., not just the two methods shown here but also the way Visual Studio sets it up when you use the Execute Code First Migrations check box. That said, you're right that with multiple instances you can potentially  end up with two migrators trying to upgrade the database. The migration process is atomic/transactional, so one will fail and the other pass, but you can still end up with exceptions from the instance(s) that fail. When you have multiple web servers you could write your own logic to ensure only one tries to migrate, or do migration during deployment instead of on application start. The latter is probably the best choice for most scenarios.

  • Tom,

    I seem to get the error:

    {"Automatic migrations that affect the location of the migrations history system table (such as default schema changes) are not supported. Please use code-based migrations for operations that affect the location of the migrations history system table."}

    I am using Azure and I think its related to named schemas vs dbo.  Any thoughts?

  • My recommendation would be to not use automatic migrations. I haven't used them and don't know what difficulties they can cause or how to resolve them.

Page 1 of 1 (4 items)