1

I have two instances in my server and I want to know which one of them is the default. Can you, please, point out ways I can recognize it. By saying a default instance I mean the one that listens for 1433.

Thanks for your support.

5

login in to your server and run this

 SELECT SERVERPROPERTY('InstanceName')

for sql server 2008 and above if it returns null then it's a default instance else named one

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)

Ref

Another one using SEVERPROPERTY(),

Run the below code

SELECT SERVERPROPERTY('ServerName')


SELECT SERVERPROPERTY('MachineName')

if both returns same value ie machine then it's your default instance else the first one return with your machinename\instance name

After your edit

if you want to check the SQL server service which listens to port 1433. Then you have to check that in SQL server configuration manager. Check-in SQL server network configuration in that protocols for SQL server and click properties for TCP/IP and see the port used under IP addresses

1

By saying a default instance I mean the one that listens for 1433

Even a named instance can be configured to listen on 1433 port, but it still be a named instance.

To your question about knowing the port:

You can see what port is used now by your instance looking at the first rows of SQL Server error log, using GUI (Management -> SQL Server Logs), xp_readerrorlog or just opening error log using any text editor, Notepad for example.

So here you see one of my named instances that listens on 8853 for user connections and on 58490 for DAC

enter image description here

Or you can use Configuration Manager/registry entry for the same thing, you find the port under Server Network Configuration -> Your Instance -> TCP/IP like this:

enter image description here

The difference is that when you are looking at the errorlog, you get the port currently in use, and when you are looking at Configuration Manager/registry, you see the last configured value that can be currently in use or that will take place only after server restart, if it was configured but server was not restarted.

  • Alternatively, you can issue the following T-SQL query: select distinct local_net_address, local_tcp_port from sys.dm_exec_connections where net_transport = 'TCP' – Thomas Rushton Aug 6 '18 at 9:49
  • @Thomas Rushton This cannot guarantee you with the info about configured port at any time, for example, all my connections just now use Shared Memory, so your query doesn't return anything, but the port is still configured – sepupic Aug 6 '18 at 9:55
  • Good point, should have added a disclaimer on that! – Thomas Rushton Aug 6 '18 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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