Database Engine Instances (SQL Server)
An instance of the Database Engine is a copy of the sqlservr.exe executable that runs as an operating system service. Each instance manages several system databases and one or more user databases. Each computer can run multiple instances of the Database Engine. Applications connect to the instance in order to perform work in a database managed by the instance.
Instances
An instance of the Database Engine operates as a service that handles all application requests to work with the data in any of the databases managed by that instance. It is the target of the connection requests (logins) from applications. The connection runs through a network connection if the application and instance are on separate computers. If the application and instance are on the same computer, the SQL Server connection can run as either a network connection or an in-memory connection. When a connection has been completed, an application sends Transact-SQL statements across the connection to the instance. The instance resolves the Transact-SQL statements into operations against the data and objects in the databases, and if the required permissions have been granted to the login credentials, performs the work. Any data retrieved is returned to the application, along with any messages such as errors.
You can run multiple instances of the Database Engine on a computer. One instance can be the default instance. The default instance has no name. If a connection request specifies only the name of the computer, the connection is made to the default instance. A named instance is one where you specify an instance name when installing the instance. A connection request must specify both the computer name and instance name in order to connect to the instance. There is no requirement to install a default instance; all of the instances running on a computer can be named instances.
Related Tasks
Task Description |
Topic |
|---|---|
Describes how to configure the properties of an instance. Configure defaults such as file locations and date formats, or how the instance uses operating system resources, such as memory or threads. |
|
Describes how to manage the collation for an instance of the Database Engine. Collations define the bit patterns used to represent characters, and associated behaviors such as sorting, and case or accent sensitivity in comparison operations. |
|
Describes how to configure linked server definitions, which allow Transact-SQL statements run in an instance to work with data stored in separate OLE DB data sources. |
|
Describes how to create a logon trigger, which specifies actions to be taken after a logon attempt has been validated, but before it starts working with resources in the instance. Logon triggers support actions such as logging connection activity, or restricting logins based on logic in addition to the credential authentication performed by Windows and SQL Server. |
|
Describes how to manage the service associated with an instance of the Database Engine. This includes actions such as starting and stopping the service, or configuring startup options. |
|
Describes how to perform server network configuration tasks such as enabling protocols, modifying the port or pipe used by a protocol, configuring encryption, configuring the SQL Server Browser service, exposing or hiding the SQL Server Database Engine on the network, and registering the Server Principal Name. |
|
Describes how to perform client network configuration tasks such as configuring client protocols and creating or deleting a Server Alias. |
|
Describes the SQL Server Management Studio editors that can be used to design, debug, and run scripts such as Transact-SQL scripts. Also describes how to code Windows PowerShell scripts to work with SQL Server components. |
|
Describes how to use maintenance plans to specify a workflow of common administration tasks for an instance. Workflows include tasks such as backing up databases and updating statistics to improve performance. |
|
Describes how to use the resource governor to manage resource consumption and workloads by specifying limits to the amount of CPU and memory that application requests can use. |
|
Describes how database applications can use database mail to send e-mail messages from the Database Engine. |
|
Describes how to use extended events to capture performance data can be used to build performance baselines or to diagnose performance problems. Extended events are a light-weight, highly scalable system for gathering performance data. |
|
Describes how to use SQL Trace to build a customized system for capturing and recording events in the Database Engine. |
|
Describes how to use SQL Server Profiler to capture traces of application requests coming in to an instance of the Database Engine. These traces can later be replayed for activities such as performance testing or problem diagnosis. |
|
Describes the Change Data Capture (CDC) and Change Tracking features and describes how to use these features to track changes to data in a database. |
|
Describes how to use the Log File viewer to find and view SQL Server errors and messages in various logs, such as the SQL Server job history, the SQL Server logs, and Windows event logs. |
|
Describes how to use the Database Engine Tuning Advisor to analyze databases and make recommendations for addressing potential performance problems. |
|
Describes how the production database administrators can make a diagnostic connection to instances when standard connections are not being accepted. |
|
Describes how to use the deprecated remote servers feature to enable access from one instance of the Database Engine to another. The preferred mechanism for this functionality is a linked server. |
|
Describes the capabilities of Service Broker for messaging and queueing applications and provides pointers to the Service Broker documentation. |