SQL Server instance

A computer (or generally an SQL Server installation) can have zero, one or more instances of SQL Server installed.

An instance consists of one or more databases.

A database contains one or more logical file groups.

Each file group has one or more data files.

The data files physically store the data that is in entrusted to SQL Server.

Components

An instance consists of one or more components:

Database Engine Server
Analysis Services server
Reporting Services server

It seems that with SQL Server 2017, Reporting Services has become an SQL Server independent component.

Database Engine Server

A Database Engine Server consists of one or more databases where the actual data is stored.

System level information about an instance is recorded in the master system database.

Each instance is a Windows service that runs (a separate copy) of sqlservr.exe (See also SQL Server services).

Instance names

At most one of the installed instances is a default instance. The other instances are identified by an instance name.

When a client tries to connect to a computer without specifying an instance name, it will be connected to the default instance.

The default instance is sometimes also referred to by a simple dot (for example in the -S option of sqlcmd.exe).

Instance IDs

Instance IDs are required to locate instance-dependent data in the registry and the file system. These Instance IDs are created for each component (Database Engine Server, Analysis Services, …) when a new instance is created with SQL Server Setup (setup.exe).

The (default) instance ID consists of

ID Prefix (that identifies the component),
a two digit version code,
an optional underscore and minor version,
a dot (.)
an instance name (which is MSSQLSERVER for default instances).

SQL Server 2016, for example, comes with the following three component prefixes:

Component	ID Prefix
Database Engine	`MSSQL`
Analysis Services	`MSAS`
Reporting Services	`MSRS`

Examples IDs:

MSSQL14.MSSQLSERVER is the default instance id of unnamed SQL Server 2017 instance
MSSQL14.TQ84 is the instance id of SQL Server 2017 Database Engine whose instance name is TQ84
MSAS14.TQ84 is the instance id of Analysis Services 2017 whose instance name is TQ84

The name of an instance id appears in an SQL Server's directory structure under %programfiles%\Microsoft SQL Server\instance-id.

This name also appears in the registry under the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\INSTANCE_ID.

Instance names are also recorded in the value of InstalledInstances under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server.

The sub keys of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names maintain a mapping from instance names to instance IDs.

Setttings that belong to an instance are stored in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance name.

The instance name of a named instance is returned by serverproperty('instancename').

TODO

exec sp_helpserver;
select @@servername;
select * from sys.sysservers;
select * from sys.servers;

Service names

An SQL server instance is tied to a service.

An unnamed instance's service name is MSSQLSERVER.

A named instance's service name is MSSQL*instancename.

A service name seems to be returned by @@servicename.

Connecting to another instance with sqlcmd

The -S flag of sqlcmd allows to specify the instance to which one wants to connect

c:\> sqlcmd -S Hostname/InstanceName

Within a running sqlcmd session, the :connect command can be used.

Identifying process of an instance

The process (sqlservr.exe) that is related to an instance can be queried with serverproperty('ProcessID').

Account under which the instance (process) is running

By default, the process sqlservr.exe runs under the account NT Service\MSSQLServer.

This can be verified, for example, by having the instance execute whoami.exe:

exec xp_cmdshell 'whoami'
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
nt service\mssqlserver
NULL

(2 rows affected)