What are named and default instances?

Solution 1:

According to Microsoft regarding named vs default

Client applications connect to an instance of Microsoft SQL Server 2005 to work with a SQL Server database. Each SQL Server instance is made up of a distinct set of services that can have unique settings. The directory structure, registry structure, and service name all reflect the specific instance name you identify during setup.

An instance is either the default, unnamed instance, or it is a named instance. When SQL Server 2005 is in installed in the default instance, it does not require a client to specify the name of the instance to make a connection. The client only has to know the server name.

A named instance is identified by the network name of the computer plus the instance name that you specify during installation. The client must specify both the server name and the instance name when connecting.

By default, SQL Server installs in the default instance unless you specify an instance name. SQL Server Express, however, always installs in a named instance unless you force a default installation during setup.

Solution 2:

Also you can install only one default (unnamed instance) but you can install many named instances.

Many third party software will usually use a default named instance although they may not mention it. The reason is clear from the above answers, standard versions installs an unnamed instance by default while the Express version installs a named instance by default.

It is important to know about the difference from this perspective because if you have 2 or 3 DB Servers running, you might never connect to the right version. Because the third party software is looking for the default instance while you are thinking it is the SQLEXPRESS instance it is trying to connect to. It can significantly increase your troubleshoot time, if you don't know how to connect to a named vs unnamed instance.

So if you want to connect to Named or Unnamed Version, use the following guidelines.

MY-MACHINE-NAME\SQLEXPRESS  /* named version - correct */
MY-MACHINE-NAME             /* unnamed version (default instance) - correct */
MY-MACHINE-NAME\MSSQLSERVER /* unnamed version (default instance) - Wrong */

Note that even though a default instance has a name, it can not be referenced by its name!

Solution 3:

Yet another practical difference: SQL2005 and up allow you to install 16 or more instances per system. Since licensing is per physical CPU, per installation of SQL server (and not per instance!), this means you can run up to 16 instances of SQL Server 2005 without paying a cent more than you did already.

Given that CPU licenses can run to $15000 (!) per socket, this is a must-have for large installations with, say, 16 cores and 256GB memory.

Solution 4:

In addition to the explanation Brett G posted, here are some reasons on why to use them:

  • You can have different instances using different versions of SQL (ie- default using SQL 2008, named instance using SQL 2005)
  • Separation of concerns, be it something with your app or security or whatever
  • Different development environments
  • Different app environments (ie- homegrown vs. third party)

All kinds of reasons to use them. Doesn't mean it's always a good idea, though :P

Tags:

Sql Server

Recent Posts