SQL Server – How to Identify Default Instance in Configuration Manager with Changed Port Number

instancesql server

In MS SQL Server Configuration Manager I can see all the instances. I recognize the default one (by saying a default instance I mean the one that listens for 1433) by looking at TCP/IP's IP Addresses property which shows IP addresses and used ports from Network Configuration settings. But named instances can use 1433 too. If the port number of the default instance is changed how can I identify which one is the default in Configuration Manager(I know that other options are available without Configuration Manager).

Thanks for your support.

Best Answer

I recognize the default one (by saying a default instance I mean the one that listens for 1433) by looking at TCP/IP's IP Addresses property which shows IP addresses and used ports from Network Configuration settings

That is not the correct way to identify default instance in SQL Server. A default instance can be identified by going to SQL Server configuration manager (SCCM). On SSCM page click on SQL Server Services on the left pane. Now on the right you can see all the instances. Default instance is one which is like SQL Server(MSSQLSERVER. For named instances it would be like SQL Server (Instance_Name) where instance_name is name of the instance. Something like below. Source is this SO Thread.

enter image description here

If you cannot open SCCM go to run and type services.msc. Look for SQl Server service. Again on services pages as well the SQL Server(MSSQLSERVER) will be service of default instance.

If you want to get the information using TSQL you have Serverproperty T-SQL command to help you

select serverporperty(InstanceName)

InstanceName: Name of the instance to which the user is connected.

Returns NULL if the instance name is the default instance, if the input is not valid, or error.

NULL = Input is not valid, an error, or not applicable.

Base data type: nvarchar(128)

So you can see this is not absolute way, but if the TSQL syntax is correct and you get NULL means you are connected to default instance. Other disadvantage of this method is you have to run this query on each instance to get the information.

Port number hardly has anything to do with default or named instance of SQL Server, it has more to do with securing SQL Server connection. A default instance can listen on port apart from 1433. When you install SQL Server by default it will listen on port 1433, as a good practice it is advised to change it to listen on other port.

Related Question