Deploy Database Fails for Azure Mobile App

Question

I'm trying to build a webservice/backend for a new mobile app I'm building usuing Xamarin Forms and Azure Services. I've been following this tutorial.

Everything is fine until I get to the section for "Deploy the project to Azure App Service", where I have 2 related issues. I get this same issue with my own DataObjects AND with the standard "ToDoList" application that is given as an example.

1. When I go to publish the first time, I select "Update database" on the "Settings" tab in the Publish Wizard in order to push my new DataObjects/Schema through to my new database. Then, I hit publish, and I get an error back saying System.Data.SqlClient.SqlException (0x80131904): Cannot attach the file '{LocalFilePath}\App_Data\{databaseName}.mdf' as database '{databaseName}'. I notice that there is NOTHING in the specified "App_Data" folder, is it possible that I need to do something to build the .mdf prior to publishing to the app service?
2. After this error occurs, and I try to re-publish the database, and the Publish Wizard has 2 differences. First, I no longer have the "Update Database" option, I only have the "Execute Code First Migrations" option on the Settings Tab, and it is grayed out. Second, the Preview tab says "No databases are selected to publish"

I've tried to publish the database through the App Service three different ways, and each of them is giving me the exact same scenario described above, so I think I'm just missing something obvious since I'm new to Azure and Mobile App development.

1. Create Mobile App Backend + Service + Database through the Azure Portal, and then deploying the "QuickStart" project from the Azure Portal.
2. Following a tutorial similar to the one above, but that was specifically for publishing the ToDoList database schema that is inserted by default. This was the tutorial I followed
3. Following the tutorial above but utilizing my own custom DataObjects rather than the ones described in the tutorial.

I've read up on ways to get the "Execute Code First Migrations" to be available, but I haven't found anything that actually allows me to deploy the database yet, as it keeps on saying "No databases are selected to publish", and I can't get the "Update Database" option to show up after that initial failure. Is there anything obvious that I'm missing?

Answer 1

When I go to publish the first time, I select "Update database" on the "Settings" tab in the Publish Wizard in order to push my new DataObjects/Schema through to my new database. Then, I hit publish, and I get an error back saying System.Data.SqlClient.SqlException (0x80131904): Cannot attach the file '{LocalFilePath}\App_Data{databaseName}.mdf' as database '{databaseName}'. I notice that there is NOTHING in the specified "App_Data" folder, is it possible that I need to do something to build the .mdf prior to publishing to the app service?

As official document mentioned about Update database in the Publish Web wizard as follows:

you select the check box labeled Update database in the Publish Web wizard. This check box is shown for SQL Server databases that the application accesses without using an Entity Framework Code First context class.

If your source local db is not created before you deploying your app, you would get the error as follows:

For more details, you could refer to the dbDacFx Web Deploy Provider section in this document.

After this error occurs, and I try to re-publish the database, and the Publish Wizard has 2 differences. First, I no longer have the "Update Database" option, I only have the "Execute Code First Migrations" option on the Settings Tab, and it is grayed out. Second, the Preview tab says "No databases are selected to publish"

Execute Code First Migrations (runs on application start) is shown for databases that the application accesses by using an Entity Framework Code First context class.

When you select this option, the deployment process automatically configures the application Web.config file on the destination server so that Code First uses the MigrateDatabaseToLatestVersion initializer class.

If you run the Enable-Migrations command in Package Manager Console, you could select this check box. For more details, you could refer to the Entity Framework Code First Migrations section in this document.

For a simple way, you could leverage the "QuickStart" project from the Azure Portal to migrate your db via EF code first. When deploying your project, update your local connectionstring with your target db (e.g. Azure db) connectionstring under "Settings > Databases" in the VS publish web wizard as follows: