When to use .mdf and when .sdf?
SQL Server Compact (sdf files) popular in Windows phone for storing some data (small count of data), also it could be used in web sites, but it has poor performance, even SQLite is better.
SQL Server (mdf files) used in big sites with a lot of data. Its more powerful, but cant be used in phones and must be installed on servers.
SQL Server Compact Edition is indeed quite different from "regular" SQL Server.
SQL Server CE is a stand-alone, one-user database that gets loaded by your application on the machine where your code runs; it has all data in the one single
.sdf file and support only a small subset of the "regular" SQL Server (and its features). SQL Server CE is great for simple, stand-alone, one-user apps (like phone apps).
"Real" SQL Server is a server product - you install it on a server, your clients only connect to it (typically, the client machines don't have SQL Server itself installed). It's a full-blown RDBMS with everything - stored proc, triggers etc. and thus best optimized to handle structured, large-scale data - think enterprise applications, such as order handling, invoicing, accounting, large-scale databases for multiple users of any kind.
First, the sql server database file is not a full sql server database, if you're creating it in this fashion it's a sql server express. Sql Server Express databases aren't terribly fantastic to use since most hosts don't run sql server express and publishing them is tricky if you then try to get them running on a sql server database. Better to start with sql server first then express and migrate.
the local db is actually sql server compact edition. http://en.wikipedia.org/wiki/SQL_Server_Compact
For connection strings, go to http://www.connectionstrings.com/sql-server/ There is pretty much every connection string available there. To work with SQL Server Express you will need to have it installed, and to manage it you will need to download the sql server express management tools. Generally, most sql express instances are designed to be used with local accounts but if I remember correctly you can use the management tools to create a sql server login.