SQL Server 2012 notes Sharing-4: Understanding SQL Server Instances

Each individual instance of SQL Server has a Windows process: sqlservr.exe, multiple instances can be installed under one windows, and multiple instances will have multiple sqlservr.exe processes.

A SQL instance corresponds to a service in the background, and if multiple applications are placed in an instance, a problem with a program developed by an application, such as a dead loop, can cause the service to stop, which causes all databases to fail to work. You can use multi-instance separation methods.

Multiple instances can be installed on a server, and the number of instances supported by the Standard Edition (16) and Enterprise Edition (50) is different.

Types of SQL Server instances

(i) Default instance and named instance

1. Difference of service name in service

Default instance: MSSQLSERVER;

Named instance: The real column is named: SQL01, and the name in the service is: Mssql$sql01. If you have multiple instances, multiple service names will appear in the service.

2. The difference between when connecting to SSMs

The default instance can be connected directly using the dot, (local), computer name

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard "Src=" Http://img1.51cto.com/attachment/201406/8/639838_1402212050hSAF.png "height=" 312 "/>

A named instance is connected using the computer name \ Instance name. The name used is: COMPUTERNAME\SQL01

3. File locations for default and named instances of SQL Server 2005

Both the default and named instances have their own set of program files and data files, as well as a set of common files that are shared between all instances on the computer.

For instances of SQL Server that contain the database engine, Analysis Services, and Reporting services, each component has a complete set of data files and executables, as well as common files shared by all components.

To isolate the installation location for each component, a unique instance ID is generated for each component in the given instance of SQL Server. This allows a single component to be upgraded to a future version of SQL Server, regardless of other SQL Server components.

Please do not * * * any of the following directories or contents: Binn, Data, Ftdata, HTML, or 1033. If necessary, you can * * * other directories; however, if you do not uninstall and reinstall SQL Server 2005, you may not be able to retrieve lost features or data.

Do not * * * or modify any. htm files in the HTML directory. They are required for the proper operation of SQL Server tools.

Common files used by all instances on a single computer are installed in folder systemdrive: \program Files\Microsoft SQL Server\90, where systemdrive is the drive letter of the installation component. Typically drive C.

Reference: http://technet.microsoft.com/zh-cn/library/ms143547 (sql.90). aspx

4. Instance ID

During SQL Server Setup, an instance ID is generated for each server component. The server components in this version of SQL Server are the database engine, Analysis Services, and Reporting services. The format of the instance ID is MSSQL.N, where n is the ordinal number of the installation component. The instance ID is used in the file directory and in the registry root directory.

The first generated instance ID is MSSQL.1, and the ID number of the other instance is incremented sequentially, such as MSSQL.2,MSSQL.3. If the ID sequence is interrupted due to uninstallation, an ID number is generated to populate the interrupt. The most recently installed instance may not always have the highest instance ID number.

5. SQL Express

In SQL Server Express, named instances are always used by default. You can specify the instance name when you install SQL Server Express, otherwise the default named instance SQLExpress will be used. If you use a default named instance, you can refer to the instance as ComputerName\sqlexpress.

If no other version of SQL Server is installed on the server that has the default instance installed, you can install SQL Server Express as the default SQL Server instance. To do this, use the instance name MSSQLSERVER to install SQL Server Express in a named instance. This forces Setup to install SQL Server Express as the default, unnamed instance.

is an example of a default instance being installed.

650) this.width=650; "title=" "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" "src=" http:// Img1.51cto.com/attachment/201406/8/639838_1402212062js7t.png "height=" 484 "/>

(ii) native instances and cluster instances

In general, if SQL Server does not have a failover cluster deployment, the instance of SQL Server is a native instance.

for a better understanding of cluster instances, you can refer to the blog post: http://543925535.blog.51cto.com/639838/1179319

This article from "Zeng Hung Xin Technical column" blog, declined to reprint!