asp.net mvc - Using a LocalDb MDF file on Azure

Question

I am developing an ASP.NET MVC website, which I want to host on Azure Websites. While in development, I have been using an MDF file in my App_Data directory with a connection string looking like this:

<add name="DefaultConnection" connectionString="Data Source=(LocalDb)v11.0;Initial Catalog=MyApp;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|MyApp.mdf;MultipleActiveResultSets=true" providerName="System.Data.SqlClient"  />

To try it out on Azure, I was hoping I could leave this connection string as is, and simply FTP my MyApp.mdf into the App_Data folder on Azure, since it is all set up with the example data I want to use. However, when I tried to access my site, I ran into the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 52 - Unable to locate a LocalDB installation. Verify that SQL Server Express is properly installed and that the LocalDB feature is enabled.)

My question is, is there a way I can run my Azure website connecting to an MDF file in my App_Data folder, or am I forced to use an Azure SQL database?

Answer 1

You can't use an .mdf file in App_Data, but you aren't forced to SQL Azure -- you can use SQL Server Compact. Deployment from LocalDB to Compact is easy if you are using Code First Migrations; otherwise you will have to migrate to SQL Server Compact before you deploy. If you decide to go with Compact you'll have to make sure the database engine gets deployed, and you can find instructions for that in this tutorial:

http://www.asp.net/mvc/tutorials/deployment/deployment-to-a-hosting-provider/deployment-to-a-hosting-provider-deploying-sql-server-compact-databases-2-of-12