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.
Table of Contents
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
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.
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
