Tuesday, December 7, 2010

Questions on Default, Named and Multiple instances

SQL Server 2005 supports to have multiple instances on a single server. Each instance within a computer runs independently of other instances. You can have only one default instance and others are named instances.

When can I use multiple instance?

  • When testing multiple versions of SQL Server on same computer.
  • When testing service packs and development databases and applications.
What is meant by a Default Instance?

During SQL Server installation, the setup acquires the hostname of the server. So you can have only one default instance on a computer. All clients can connect to the instance using the hostname of the computer.

What are Named Instances?
Named instances are the names that we provide during installation. You can have many number of named instances on a single computer.

For example, if your computer hostname is MCITP and you are providing a name "certification" during installation, then all clients will connect to this instance with MCITP\certification as server name.

Following points are to be considered when creating a named instance.
  • Instance names are not case sensitive.
  • Instance names are limited to 16 characters.
  • Instance names cannot contain the terms "Default," "MSSQLServer," or other reserved keywords.
  • The first character in the instance name must be a letter or an underscore (_).
  • Subsequent characters can be letters defined by the Unicode Standard 2.0, decimal numbers from Basic Latin or other national scripts, the dollar sign ($), or an underscore (_).
  • Embedded spaces or other special characters are not allowed in instance names; nor are the backslash (\), comma (,), colon (:), semi-colon (;), single quote ('), ampersand (&), or at sign (@).

By default, the first time you install SQL Server 2005 on a computer, Setup specifies the installation as the default instance; however, you can choose to install SQL Server as a named instance without installing the default instance first.

The following table shows the number of instances supported for each instance in the different editions of SQL Server 2005.

SQL Server 2005 edition
Database Engine instances
Analysis Services instances
Reporting Services instances
Enterprise Edition
Developer Edition
Standard Edition
Workgroup Edition
Express Edition

It is important point to consider that all components (SSAS/SSIS) of SQL Server that share the same instance name must have same version, edition, platform (16 bit/32 bit) and language.


1. How many default instances you can have on a single SQL Server?

A. 1
B. 2
C. 3
D. 4

Show Answers:

1 comment:

  1. Informative article. I was looking for the number of instances supported in SQL 2005 which you gave in a tabular format. Thanks for the effort.