SQL Server Port(Default 1433)
SQL Server Port! Well... some times most of developers Asking "What is SQL Server Port?" If you have multiple instances in same serve, some of developers saying I can't connect this database server(database server connection problems) And some of saying how can I get SQL Server port?(or How to find SQL Server port?). If you are Database Administrator in Software development company, then you will face above these questions most of the time.
First How can I view(or Find) SQL Server port in given Server?
You can used several method to view this, first I'm going to explain, How to find this in graphical view as follows,
Programs -> Microsoft SQL Server (2005,2008,2008 R2 or 2012) -> Configuration Tools -> SQL Server Configuration Manager -> SQL Server Network Configuration -> And then Select your SQL Server instance -> TCP/IP
Programs -> Microsoft SQL Server (2005,2008,2008 R2 or 2012) -> Configuration Tools -> SQL Server Configuration Manager -> SQL Server Network Configuration -> And then Select your SQL Server instance -> TCP/IP
Hope now you can view or find SQL Server port, which is given SQL Server instances.
Any other Methods - Yes :)
Method 01,
Open Task Manager and then Go to the Services Tab as following Picture,
Inside the services tab you will notice PID according to SQL Server service. With using above PID you can view SQL Server port as follows,
If you are not happy with above methods, then you can go with following T-SQL command,
DECLARE @tcp_port nvarchar(5)
EXEC xp_regread@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT
SELECT @tcp_port
And if you already know the SQL Server port of given server then you can use SSMS to connect that as follows,
<Server or instance name>,<port> Ex : MyServer,1433
Hope above Article will help you.
Cheers...
and Most welcome your comments and ideas...
most of them asking about, if some physical server has many sql server instance then how can we find the sql server port, so i have made small modification to above sql statement,
ReplyDelete----------------------------------------------------------------------------------------------------------
DECLARE @portNumber NVARCHAR(10);
DECLARE @Server NVARCHAR(128);
DECLARE @path NVARCHAR(500);
SELECT @Server=REVERSE(LEFT(REVERSE(REPLACE(SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1),
'\MSSQL\DATA\','')),
CHARINDEX('\',REVERSE(REPLACE(SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1),'\MSSQL\DATA\','')))-1))
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1;
SELECT @path ='SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\'+@Server+'\MSSQLSERVER\SUPERSOCKETNETLIB\TCP\IPALL';
EXEC xp_regread @rootkey = 'HKEY_LOCAL_MACHINE',@key =@path,@value_name = 'TcpPort',@value = @portNumber OUTPUT
SELECT [Port Number] = @portNumber
GO
--------------------------------------------------------------------------------------
Hope this will help you all.