1

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.

Improve this question
6
  • 2
    As far as I know, the default instance doesn't have anything to do with the IP address. If you have changed the port then you have to mention that in your connection string when you connect to that sql instance. A default instance is just an sql server instance with no name.
    – Biju jose
    Commented Aug 6, 2018 at 9:01
  • Also Check this one by my marc_s here
    – Biju jose
    Commented Aug 6, 2018 at 9:04
  • 1
    Upvoted Biju jose comment. You just misunderstood the termin "default" instance. It's NOT an instance that uses 1433, it's an instance that has no name
    – sepupic
    Commented Aug 6, 2018 at 9:04
  • @eleonora grigoryan, What you are really trying to solve , if you give more details we can help you out.
    – Biju jose
    Commented Aug 6, 2018 at 9:05
  • @Biju jose Thanks you, your answers helped me already
    – igelr
    Commented Aug 6, 2018 at 10:28

1 Answer 1

Reset to default
2

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.

Improve this answer

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.