in what situations the dynamic port number in sql server is used?

1 vote

1 answer

1590 views

sql-server connections tcpip dynamic-ports

                          I have the following script to find out  what is the Static and dynamic port numbers:


    --===============================
    -- sys.dm_exec_connections
    --===============================
    SELECT 
    [user]=suser_sname()
    ,local_tcp_port
    ,session_id
    ,connect_time
    ,net_transport
    ,protocol_type
    ,encrypt_option
    ,auth_scheme
    ,last_read
    ,last_write
    
    FROM   sys.dm_exec_connections
    WHERE  session_id = @@SPID
    GO
    
    --===============================
    -- Execute below script if SQL Server is configured with dynamic port number
    --===============================
    DECLARE       @portNo   NVARCHAR(10)
      
    EXEC   xp_instance_regread
    @rootkey    = 'HKEY_LOCAL_MACHINE',
    @key        =
    'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
    @value_name = 'TcpDynamicPorts',
    @value      = @portNo OUTPUT
      
    SELECT [PortNumber] = @portNo
    GO
    
    --===============================
    -- Execute below script if SQL Server is configured with static port number
    --===============================
    DECLARE       @portNo   NVARCHAR(10)
      
    EXEC   xp_instance_regread
    @rootkey    = 'HKEY_LOCAL_MACHINE',
    @key        =
    'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
    @value_name = 'TcpPort',
    @value      = @portNo OUTPUT
      
    SELECT [PortNumber] = @portNo
    GO




The following link mentions netstat -ano to check usage from the cmdline.

When is a Dynamic Port “dynamic”? 


when is the dynamic port used?


                        

Asked by Marcello Miorelli (17274 rep)

Jun 13, 2018, 12:09 PM
Last activity: Jun 13, 2018, 01:08 PM

in what situations the dynamic port number in sql server is used?

Related Questions