2

Why SQL server has only one default instance? This question was asked to me in an interview.

2 Answers 2

5

When SQL Server is installed with the default settings, the instance has no instance name set (although when execute select @@servicename, it will return "MSSQLSERVER" because it has to have an internal value), and the SQL Server Service will be waiting for connections where no instance name is specified.

If there was more than one instance with no name, there would be no way to determine which unnamed instance to connect to without some sort of additional identifier.

Hence, you can only have one unnamed (default) instance, but there is no requirement to have a default instance at all.

You can have 10 named instances on a server if you wish.

Interestingly, even though MSSQLSERVER is the default instance name, you cannot connect to it using servername\MSSQLSERVER.

More information about SQL Server instances here:

3
  • Thanks Stephen for answering my question. Jul 11, 2023 at 3:19
  • @SunilYaragoppa instead of saying thank you, go ahead and accept the answer and upvote it. Jul 11, 2023 at 17:43
  • I did upvote but getting this message- You need at least 15 reputation to cast a vote, but your feedback has been recorded. Jul 12, 2023 at 8:03
3

It is because before SQL Server 2000 only a single instance was allowed. The installer SQL Server created a Windows service named MSSQLSERVER and that hosted the database engine.

When multiple instances were added in SQL 2000 the design worked around the existing behavior with the MSSQLSERVER instance going from the "only" instance to the "default" instance, and allowing additional "named" instances that run as Windows services named MSSQL$<InstanceName>.

So

Why SQL server has only one default instance? This question was asked to me in an interview.

And while the above is the real reason, the reason they are probably fishing for is that only one instance can listen on the default port 1433 on the server. Additional instances must be reached on non-default ports or by using the <ServerName>/<InstanceName> format and going through the SQL Browser to find the port number.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.