What is a SQL Server Instance

SQL Server: What are Named and Default Instances?


Reply:


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 consists of a number of services for which unique settings can be specified. The directory structure, registry structure, and service name all reflect the specific instance name you specified during setup.

An instance is either the default unnamed instance or a named instance. If SQL Server 2005 is installed in the default instance, no client needs to provide the instance name to connect. The client only needs to know the server name.

A named instance is identified by the computer's network name and the instance name you specified during installation. The client must provide both the server name and the instance name when connecting.

By default, SQL Server is installed in the default instance unless you specify an instance name. However, SQL Server Express is always installed on a named instance unless you force a typical installation during setup.


You can install just one default (unnamed) instance, but you can install many named instances.

Many third-party software usually use a default named instance, although they may not mention it. The reason can be seen from the answers above. Standard versions install an unnamed instance by default, while the Express version installs a named instance by default.

It is important to know the difference from this perspective because if you are running two or three DB servers you may never be able to connect to the correct version. Because the third party software is looking for the default instance while you believe it is the SQLEXPRESS instance to connect to. This can significantly increase your troubleshooting time if you don't know how to connect to a named or unnamed instance.

So if you want to connect to the named or the unnamed version, follow these guidelines.

Note that a standard instance, although it has a name, cannot be referenced by its name!


In addition to the explanation Brett G posted, here are a few reasons why you should use it:

  • You can use different instances with different SQL versions (ie by default with SQL 2008, named instance with SQL 2005).
  • Separation of concerns, be it something with your app or security or whatever
  • Different development environments
  • Different app environments (i.e. home or third party)

All sorts of reasons to use them. But that doesn't mean it's always a good idea: P


Another practical difference: With SQL2005 and higher, you can install 16 or more instances per system. As the licensing per physical CPU and per installation of SQL Server (and Not per instance!) If done, you can run up to 16 instances of SQL Server 2005 without paying a cent more than before.

Given that CPU licenses can cost up to $ 15,000 (!) Per socket, this is a must for large installations with, for example, 16 cores and 256 GB of RAM.


You can also limit the memory and limit the processor usage per instance. You can also create an instance for vendor-written apps that require system administrator privileges so that your other applications are not compromised.


One of the best reasons is to keep databases apart. If you develop your own application and use it to package SQL Express, it makes sense to install it in its own instance. In addition, the user can specify an existing SQL installation if he so wishes.

If you have a database that you don't want anyone to have access to, it can be moved to its own instance with very limited security permissions. Suppose this is your product database, which is then replicated on the same server ** to the reporting database, which is only human readable. Users (administrators) are less likely to accidentally access the production database because few users can update the security groups on the production instance.

** Good practice is for the report server to be a separate computer, but I'm only doing this as an example.

We use cookies and other tracking technologies to improve your browsing experience on our website, to show you personalized content and targeted ads, to analyze our website traffic, and to understand where our visitors are coming from.

By continuing, you consent to our use of cookies and other tracking technologies and affirm you're at least 16 years old or have consent from a parent or guardian.

You can read details in our Cookie policy and Privacy policy.