DBA Posts - What is SQL Server Instance?

SQL Server installation provides you two options while installing on any machine.

1. SQL Server Default instance (MSSQLSERVE )
2. SQL Server Named instance (MachineName\sqlserverinstanceName)

SQL Server installation will accept default instances as MSSQLSERVE and there can only be ONE default instance on one machine. If you have to install more than one instance of SQL server on a single server, you will have to install named instance, there is no such limit on named instances, however you want to consider how each instance will consume the resources on your machine.

You can connect to SQLSERVER default instance using just the machine name that sql server default instance is installed in using multiple client tools such as management studio or sql connect client etc. OR you can use “.” A dot which means, point me to the default SQL Server instance.



Method 1 - Connecting to Default SQL Server instance



Using "." Operator

Method 2 - Connecting to Default SQL Server instance Using "."

How to connect to Name instance?

You can connect to named instance using machine name\SQLServerInstanceName i.e. SQLDEV\Aamir


Method 1- Connecting to SQL Server Named Instance

You can also connect to SQL Server Named instance using Machine's IP address instead of hostname along with SQL Server Instance Name as show below.

Method 2 - Connecting to SQL Server Named Instance using IP address and InstanceName