What is a SQL Server Instance

In this article let us learn what is SQL Server Instance is. SQL Server allows us to install and run multiple instances of SQL Server or install SQL Server on a computer, where another version of SQL Server is already installed. You can refer to the article on How to download and install SQL Server.

What is SQL Server Instance

The SQL Server allows us to install multiple instances of the SQL Server database engines. These engines run side by side and completely isolated from each other. Each database engine installation is known as the SQL Server instance.

Every instance is a complete SQL Server Installation along with its own copy of server files, database engine, databases, Users and credentials.

The instances can be of the same SQL server version or of different versions.

Running multiple instances will use considerably more resources since you are running multiple SQL Servers on the same box.

Instance Name

We give a unique name to each instance so as to uniquely identify them.

We connect to the SQL Server using the <computerName>\<InstanceName> as server name

For Example, if the computer name is HOME and the instance name is SQL2017 then SQL server name is HOME\SQL2017

We assign the instance name when we install the SQL Server database engine.

Instance Configuration

The SQL Server Instance configuration window asks for us to choose between two options. One is the default instance and the other one is named instance.

The default instance name is MSSQLSERVER. You do not have to specify the instance name when you want to connect to the default instance. You can have only one default instance installed on the PC. This is regardless of the SQL Server version

Instance Configuration Default Instance
Example of Default Instance

A Unique name must be given to a named instance. You can install SQL Server as a named instance without installing the default instance first.

Instance Configuration Named Instance
Example of Named Instance

You can create multiple instances by running the setup again. Refer to the article on How to download and install SQL Server

Naming the instance

The following rules must be followed when you name an instance of SQL Server:

  • Instance names are not case sensitive.
  • Names cannot start or end with an underscore ().
  • Reserved Keywords are not allowed in the instance name. Here is the list of Reserved Keywords
  • If you specify MSSQLSERVER as the instance name, the default instance will be created
  • Name cannot be more than 16 characters
  • The first character of the instance name must be a letter. a-z, A-Z Subsequent characters can be decimal numbers, the dollar sign ($), or an underscore ().
  • Do not use the Spaces, backslash (), comma (,), colon (:), semi-colon (;), single quote (‘), ampersand (&), hyphen (-), and (@), etc

Summary

In this article, we learned what is SQL Server Instance is. In the next tutorial, we will learn how to connect to an Instance using the SSMS

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top