If use database in our Web application and publish to Azure platform free tier plan, the database has very limited size so we’d better use other way.
The following is our summary result after we researching solution:
1: I Compared SQL Server Express, LocalDB and SQL Server Compact Edition (CE), and I find that ONLY Compact Edition is the best solution because it can be a single database file and can be migrate to Azure. Even LocalDB is newer , but it needs to install and there are multiple files, not a single DB file;
2: In VS 2013, right click a project, and use NuGet package option , to install the following 2 libraries:
Microsoft SQL Server Compact Edition
3: If use code first, just run the application and do some operation to change database such as register a new user, the single SQL Server CE database file will be generated under the App-Data folder;
Or, you can manually generate a sql server database file and change its name from xxx.mdf to xxx.sdf;
4: in web.config file, add a new connection string like the following one:
add name="MyDBConnString" connectionString="Data Source=|DataDirectory|MySampleDB.sdf" providerName="System.Data.SqlServerCe.4.0"