File Locations for Default and Named Instances of SQL Server 2005
An installation of Microsoft SQL Server 2005 consists of one or more separate instances. An instance, whether default or named, has its own set of program and data files, as well as a set of common files shared between all instances on the computer.
For an instance of SQL Server that includes the Database Engine, Analysis Services, and Reporting Services, each component has a full set of data and executable files, and common files shared by all components.
To isolate install locations for each component, unique instance IDs are generated for each component within a given SQL Server instance. This allows upgrade of a single component to future SQL Server versions without dependencies on other SQL Server components.
Important
Do not delete any of the following directories or their contents: Binn, Data, Ftdata, HTML, or 1033. You may delete other directories, if necessary; however, you may not be able to retrieve any lost functionality or data without uninstalling and then reinstalling SQL Server 2005.
Note
Do not delete or modify any of the .htm files in the HTML directory. They are required for SQL Server tools to function properly.
Shared Files for All Instances of SQL Server 2005
Common files used by all instances on a single computer are installed in the folder systemdrive:\Program Files\Microsoft SQL Server\90, where systemdrive is the drive letter where components are installed. Normally this is drive C.
Note
Program files and data files cannot be installed on a removable disk drive, cannot be installed on a file system that uses compression, and cannot be installed on shared drives on a failover cluster instance.
File Locations and Registry Mapping
During SQL Server Setup, an instance ID is generated for each server component. The server components in this SQL Server release are the Database Engine, Analysis Services, and Reporting Services. The instance ID is in the format MSSQL.n, where n is the ordinal number of the component being installed. The instance ID is used in the file directory and the registry root.
The first instance ID generated is MSSQL.1; ID numbers are incremented for additional instances as MSSQL.2, MSSQL.3, and so on. If gaps occur in the ID sequence due to uninstalls, ID numbers are generated to fill them. The most recently installed instance may not always have the highest instance ID number.
Server components are installed in directories with the format <instanceID>\<component name>. For example, a default or named instance with the Database Engine, Analysis Services, and Reporting Services would have the following default directories:
- <Program Files>\Microsoft SQL Server\MSSQL.1\MSSQL\ for the Database Engine
- <Program Files>\Microsoft SQL Server\MSSQL.2\OLAP\ for Analysis Services
- <Program Files>\Microsoft SQL Server\MSSQL.3\RS\ for Reporting Services
Instead of <Program Files>\Microsoft SQL Server, a <custom path> is used if the user chooses to change the default installation directory.
Note
SQL Server 2005 Integration Services, Notification Services, and client components are not instance aware and, therefore, are not assigned an instance ID. Non-instance-aware components are installed to the same directory by default: <system drive>:\Program Files\Microsoft SQL Server\90. Changing the installation path for one shared component also changes it for the other shared components. Subsequent installations install non-instance-aware components to the same directory as the original installation.
The registry hive is created under HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.n for instance-aware components. For example,
- HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1
- HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.2
- HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.3
The registry also maintains a mapping of instance ID to instance name. Instance ID to instance name mapping is maintained as follows:
- [HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL] "InstanceName"="MSSQL.1"
- [HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\OLAP] "InstanceName"="MSSQL.2"
- [HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\RS] "InstanceName"="MSSQL.3"
AdventureWorks Sample Databases (new)
AdventureWorks samples are installed in the Tools\Samples directory. For unattended installations, if you want the sample databases to be attached to a specific SQL Server instance, use the SAMPLEDATABASESERVER=\<instancename> switch. The instance to which the sample database is attached must be a local instance.
For more information on unattended installations, see Remote Setup Information for SQL Server 2005, How to: Install SQL Server 2005 from the Command Prompt, or instructions included with the Template.ini file on the SQL Server DVD.
Specifying File Paths
During Setup, you can change the installation path for the following features:
The installation path is displayed in Setup only for features with a user-configurable destination folder:
| Component | Default path1, 2 | Configurable3 or Fixed Path |
|---|---|---|
Database Engine server components |
\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Binn\ |
Configurable |
Database Engine data files |
\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\ |
Configurable |
Analysis Services server |
\Program Files\Microsoft SQL Server\MSSQL.n\OLAP\Bin\ |
Configurable |
Analysis Services data files |
\Program Files\Microsoft SQL Server\MSSQL.n\OLAP\Data\ |
Configurable |
Reporting Services report server |
\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportServer\Bin\ |
Configurable |
Reporting Services report manager |
\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportManager\Bin\ |
Fixed path |
SQL Server Integration Services |
<Install Directory>\90\DTS\ |
Configurable4 |
Notification Services |
<Install Directory>\90\Notification Services\ |
Configurable4 |
Client Components |
<Install Directory>\90\Tools\ |
Configurable4 |
Replication and server-side COM objects |
Program Files\Microsoft SQL Server\90\COM\5 |
Fixed path |
SQL Server Integration Services component DLL's for the Data Transformation Run-time engine, the Data Transformation Pipeline engine, and the dtexec command prompt utility |
Program Files\Microsoft SQL Server\90\DTS\Binn |
Fixed path |
DLL's that provide managed connection support for SQL Server Integration Services |
Program Files\Microsoft SQL Server\90\DTS\Connections |
Fixed path |
DLL's for each type of enumerator that SQL Server integration Services supports |
Program Files\Microsoft SQL Server\90\DTS\ForEachEnumerators |
Fixed path |
SQL Server Browser Service, WMI providers |
\Program Files\Microsoft SQL Server\90\Shared\ |
Fixed path |
Components that are shared between all instances of SQL Server 2005 |
\Program Files\Microsoft SQL Server\90\Shared\ |
Fixed path |
1Make sure that the \Program Files\Microsoft SQL Server\ folder is protected with limited permissions.
2The default drive for these locations is systemdrive, normally drive C.
3Installation paths for child features are determined by the installation path of the parent feature.
4A single installation path is shared between SQL Server Integration Services, Notification Services, and Client Components. Changing the installation path for one component also changes it for other components. Subsequent installations install components to the same location as the original installation.
5This directory is used by all instances of SQL Server on a computer. If you apply an update, like a service pack, to any of the instances on the computer, any changes to files in this folder will affect all instances on the computer.
Note
For clustered configurations, you must select a local drive that is available on every node of the cluster.
Note
When adding features to an existing installation, you cannot change the location of a previously installed feature, nor can you specify the location for a new feature. You must either install additional features to the directories already established by Setup, or uninstall and reinstall the product.
When you specify an installation path during Setup for the server components or data files, the Setup program uses the instance name in addition to the specified location for program and data files. Setup does not use the instance name for tools and other shared files. Setup also does not use any instance name for the Analysis Services program and data files, although it does use the instance name for the Analysis Services repository.
If you set an installation path for the Database Engine feature, SQL Server Setup uses that path as the root directory for all instance-specific folders for that installation, including SQL Data Files. In this case, if you set the root to "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\," instance-specific directories are added to the end of that path.
Customers choosing to use the USESYSDB upgrade functionality in the SQL Server Installation Wizard (Setup UI mode) can easily lead themselves into a situation where the product gets installed into a recursive folder structure - for example, <SQLProgramFiles>\MSSQL.1\MSSQL\MSSQL.1\MSSQL\Data\. Instead, to use the USESYSDB feature, set an installation path for the SQL Data Files feature instead of the Database Engine feature.
Note
Data files are always expected to be found in a child directory named Data. For example, specify C:\Program Files\Microsoft SQL Server\MSSQL.1\ to specify the root path to the data directory of the system databases during upgrade when data files are found under C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.