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?

Etsukoetta

You will have better luck if you include the Provider= into your connection string – Boigie

Answer 1

"Data Source" and "Server" are synonyms, so no difference.

localhost specifies the default SQL Server instance on your machine.

(LocalDb)\MSSQLLocalDB is the default LocalDB instance on your machine

Kashakashden

what do you mean by sql server instance please? we can have many instance with different name like localhost , localhost1, localhost2 for the same sql server? – Doura

No the instances name consists of hostname\instancename - like localhost\MSSQLSERVER or localhost\SQLEXPRESS – Kashakashden

Thanks .. I needed (LocalDb)\MSSQLLocalDB too .... What is the one that connects to when I just type myMachineName in there ? – Leith

Answer 2

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.

Peacemaker