Can we assign port 1433 to a named instance

Question

Post reply

Can we assign port 1433 to a named instance

mohan.bndr

SSCrazy

Points: 2360
More actions
August 14, 2013 at 2:09 am

#278602

Can we assign port 1433 to a named instance ? I know that these 1433 as port number to default instance. Please clarify, Can we allocate port 1433 to named instance..

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

you can assign any unused port you want but default is 1433.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 2

You can, but best practices state your shouldn't use the default port

Nadrek SSC-Insane Points: 20039 More actions · Answer 3

Yes.

SQL Server Configuration manager, SQL Server Network Configuration, Protocols for <instance>, TCP/IP, Properties, at the bottom is IPAll - remove the TCP Dynamic Ports string and put 1433 in TCP Ports.

I do this regularly, following the general rule of thumb "Keep track of all your instances and the port they're on".

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 4

Nadrek (8/15/2013)
Yes.
SQL Server Configuration manager, SQL Server Network Configuration, Protocols for <instance>, TCP/IP, Properties, at the bottom is IPAll - remove the TCP Dynamic Ports string and put 1433 in TCP Ports.
I do this regularly, following the general rule of thumb "Keep track of all your instances and the port they're on".

Or you could run ---

DECLARE @portNumber NVARCHAR(10)

EXEC xp_instance_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key =

'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',

@value_name = 'TcpPort',

@value = @portNumber OUTPUT

select @portNumber

shows you what port you are using 🙂

Nadrek SSC-Insane Points: 20039 More actions · Answer 5

SQLSteve (8/16/2013)
Or you could run ---
DECLARE @portNumber NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value = @portNumber OUTPUT
select @portNumber
shows you what port you are using 🙂

Only if you're using the same port on all interfaces - though your script does operate on both default and named instances for the IpAll set!

Here's a more primitive approach, that only looks at what's actually open for connections right now... but if the min and max aren't the same, it's time to do further investigation (if they are the same, you know nothing about ports that aren't in use now):

SELECT MIN(local_net_address), MAX(local_net_address), MIN(local_tcp_port), MAX(local_tcp_port) FROM sys.dm_exec_connections

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 6

But does your query show the ports that CAN be used? or the ports that people have used?

Nadrek SSC-Insane Points: 20039 More actions · Answer 7

As stated in my post, no, my dm_exec_connections query only shows the ports and local IP addresses that have currently open connections at the time the query is run, and so will not show IP Address bindings or ports not currently in use, including the DAC port.

Your registry query only shows the static port for IPAll whether it's in use or not for the current instance, and so will not show any IP Addresses, or any ports allocated dynamically, or (I suspect) any static ports bound only to a specific IP address, and requires a higher level of permissions.

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 8

mohan.bndr (8/14/2013)
Can we assign port 1433 to a named instance ? I know that these 1433 as port number to default instance. Please clarify, Can we allocate port 1433 to named instance..

Yes - this should not be an issue.

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

arnipetursson SSCertifiable Points: 6557 More actions · Answer 9

The SQL Server process writes info on port bindings to the errorlog at startup.