what are named and default instance?

Question

what are named and default instance? what is the difference between them? why they are used?

Answer 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.

Answer 2

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

Answer 3

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 Express version install a named instance be 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 dont know how to connect to named vs unnamed instance.

So if you want to connect to Named Version, the connection string would be

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 that name!

Answer 4

One of the best reasons is seperation of databases. If you are developing your own application and packaging SQL Express with it, then it makes sense to install it into it's own instance. Plus giving the user the option to specify an existing SQL installation if they perfer that.

If you have a database that is critical that you want to ensure no one else has access to, it can go into it's own instance with a very limited security authorization. Say that's your prodcution DB, and it then replicates over to the reporting DB on the same server** which is read only for people. It is less likely though user (admin) error the users would accidently get access to the production DB, by limiting who is actually able to update the security groups on the production instance to only a few people.

**good practice says to make the reporting server a seperate machine, but I'm just doing this as an example.

Answer 5

You can also cap memory and assign limit processor usage per instance. You could also create an instance for vendor written apps that 'need' system admin privileges, so you don't put your other applications at risk.

Answer 6

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.