Here is one of the very common interview questions I often ask people –
Q: What is the default Port SQL Server running on?
A: 1433 for TCP/IP and 1434 for USD connection.
I always get the correct answer for this question. However, when I ask a follow up question, so far I have yet not got a single correct answer.
Q: Great, now assumes that there is more than one instance of SQL Server running on the same server. What will be the port of the second SQL Server running on the server as the default port is already used by default instant?
A: (I have yet to get the correct answer for this one in interview).
Because of the same reason, I have decided to blog about this.
Here are different ways one can figure out on which port the second instance of SQL Server is running.
Method 1: using xp_readerrorlog
Execute following stored procedure on the instance where you want to find out port on which SQL Server is running.
USE MASTER
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO
The above query will return results something very similar to the following:
Method 2: Configuration Properties
Go to Windows >> Start >> SQL Server Program Folder >> SQL Server Configuration Manager
Now go to SQL Server Network Configurations >> Protocols for Your Server and go to TCP/IP and right click over it.
Now over here when you scroll down you will notice Port Details.
It is that easy to find the port of the second instance of SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
