Sql difference between localhost and (LocalDb)/MSSQLLocalDB

Question

I noticed I used a connection string to migrate an Identity Database. No matter what I did I looked and looked but I couldn't find the database. So I re-evaluated my connection strings and noticed they're not that similar:

var connectionString = @"Server=localhost;Database=MyDatabase;Trusted_Connection=True;"
var connectionString = @"Data Source=(LocalDb)\MSSQLLocalDB;database=gritzy.IdentityServer4.dbo;trusted_connection=yes;";

One just specified the server as localhost, and the other doesn't even specify a server at all.

I was under the impression that localhost would just use the default MSSQLSERVER Instance name.

What is the difference between the Data Source, and server?

Answer 1

If you install both SQLExpress(SqlLocalDB) and SqlServer you are in for a big confusion.

You may have seen connection string like this:

SqlServer:

var connectionString = @"Server=localhost;Database=MyDatabase;Trusted_Connection=True;"

SqlExpress:

var connectionString = @"Data Source=(LocalDb)\MSSQLLocalDB;database=gritzy.IdentityServer4.dbo;trusted_connection=yes;";

The first is the connection string you use to connect to the SQLServer, which asked you to specify the root folder when you installed SqlServer. The default is C:\Program Files\Microsoft SqlServer but you can put it in wherever you want. For example, if I specify the root to be C:\Source\DB then it is C:\source\DB\MSSQL15.MSSQLLOCALDB\MSSQL\DATA. The point here is a SQLServer instance is machine-scoped. One instance per server/machine.

The second is the connection string for SQLExpress, and the DB files are usually stored in the AppData folder for each user, such as C:\Users\<user>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB. Note that each user logged in to the same machine can have his own data file because each user has his own appData folder.

If you have both SQLExpress/SqlLocalDB and SqlServer installed, bring up SSMS (management studio) you can connect to both at the same time and you will notice they have different databases.