What is the difference between SQLEXPRESS and MSSQLSERVER?

Question

I am using SQL Server 2008 R2, when I looked into the services for SQL Server I saw there are two different services with different instances.

1. SQLEXPRESS
2. MSSQLSERVER

Can anyone help me to get clear the difference between #1 and #2?

Thanks in advance

Answer 1

You can install multiple copies of SQL Server onto the same machine. To keep them apart, all "copies" (called instances) except the first (default) one have to be named.

So by default, when you install your first instance of SQL Server, you won't give it any instance name - it'll be the default instance that you connect to use the server name of ., (local) or your machine name (or IP address).

Any further instance you want to install needs to get a separate, unique instance name.

Visual Studio will by default install SQL Server Express onto your machine (unless you explicitly tell it not to), and that will be called the SQLEXPRESS instance on your machine (hence the service name also is called SQLEXPRESS). You connect to that named instance using .\SQLEXPRESS or (local)\SQLEXRPESS.

So most likely, in your case, you installed a "full" version of SQL Server onto your machine (as the default instance - service is called MSSQLSERVER), and Visual Studio installed a second copy of SQL Server - an Express version - as SQLEXPRESS

Answer 2

MSSQLSERVER is the default instance (accessed via localhost) and SQLEXPRESS is a named instance (accessed by localhost/SQLEXPRESS)

Did you install twice?