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?

Solution 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

Solution 2

You'll have to use SQL Azure to use the Websites/Cloud Service features.

If you haven't already you'll probably want to have a look at web.config transformations with web deploy to ease the publishing experience.

http://msdn.microsoft.com/en-us/library/dd465318(v=vs.100).aspx

http://www.hanselman.com/blog/TinyHappyFeatures3PublishingImprovementsChainedConfigTransformsAndDeployingASPNETAppsFromTheCommandLine.aspx

You can import your data into the SQL Azure DB via the management tools or if you're using SQL Server 2012 you can import/export data via the portal.

Comments

[Yoda]

What connectionString should he use? I am publishing to myasp.net and I don't know what to use. My connectionString is: <connectionStrings> <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\AppD‌​b.mdf;Initial Catalog=AppDb;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" /> </connectionStrings>?

[Gabrielius]

@Yoda, your connection string is not for the required provider, check MSDN, you will find SqlServerCompact connection string example.

[twamley]

Is this still true in 2015? I just saw a default connection string in Kudu named LocalSqlServer with ...AttachDBFilename=|DataDirectory|aspnetdb.mdf... and I perked up. I'm using SQLCE already but would prefer to attach an SQLEXPRESS mdf in my Azure Website.