0

I have two installations of SQL on two seperate boxes (I have "Inherited" these).

A SQL 2005 Installation, with instance called SQL2005 - I connect to this via SQL Studio Manager and when I connect to Servername I put: SERVERNAME\SQL2005

A SQL 2008 Installation, now, when I go into Studio Manager, it connects straight through to the server just typing in the SERVERNAME without specifying \INSTANCE. However, looking through services.msc it appears the instance is MSSQLSERVER.

How exactly does this work? I always thought you had to connect directly to an instance. For example, on the 2005 box I can't connect to just SERVERNAME I have to specify an instance, but on the 2008 box, I can connect with just SERVERNAME, and if I specify \MSSQLSERVER as the instance, it fails to connect.

Any ideas?

PnP
  • 1,684
  • 8
  • 39
  • 65

3 Answers3

4

What you're seeing is that SQL Server 2008 has been installed as the "default instance". The default instance gets the name "MSSQLSERVER" but cannot be accessed using that instance name (that's by design and can be a bit confusing-- I believe it's an artifact of pre-2000 versions of SQL Server which did not support instance installation).

Your SQL Server 2005 installation was done as a "named instance" called "SQL2005". As you've seen it is necessary to specify the instance name when connecting to it.

You can get some more background from Microsoft at: Instance Configuration

Evan Anderson
  • 141,881
  • 20
  • 196
  • 331
3

MSSQLSERVER is the default instance name, so you don't have to specify a name in that case. It's also treated slightly differently: MSSQL Server differentiates between "Named Instances" (like SQL2005) and the "Default instance". Chances are you're having connect issues because "MSSQLSERVER" is not a named instance, but you're trying to connect as if it were.

Source

Satanicpuppy
  • 5,946
  • 1
  • 17
  • 18
0

Both the default instance and the named instances have an Instance ID. The Instance ID is found in the folder names, the registry key names and the service display names. For the default instance the Instance ID is always "MSSQLSERVER"; for a named instance the Instance ID is the name of the named instance.

Thus the default instance is probably in C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER and your named instance is probably in C:\Program Files\Microsoft SQL Server\MSSQL.SQL2005.

Greenstone Walker
  • 779
  • 1
  • 5
  • 16