Dynamic port is used by named instance only. By default the named instance listens on Dynamic port. When you start the SQL server for the first time, it requests for port from O/S and it is allocated to SQL Server. This port information is written into the windows registry.
Every time you start the named SQLServer, it uses the port that is allocated. In case if the port is used by another program then SQL Server chooses another port at the time of restart.
You will receive “Error ID 10048” If the port is used by another program.
Static Port, If you configure an SQLServer instance to use a static port then you should restart the instance to take effect of changes and all clients must send their request to static port
By default, the default instance listens on fixed TCP Port 1433. The default instance of SQLServer does not support dynamic port allocation, but can also make the default instance to listen on multiple static ports. The named instances of SQL Server support allocation of both static and dynamic ports
Follow these steps to configure Static TCP Port
1. Click Start à All Programsà Microsoft SQLServer à Configuration Tools à Launch “SQLServer Configuration Manager”
2. Under “SQLServer Network Configuration” à Select “Protocols for <Instance Name” à Got to TCP/IP Properties
3. Select “IP Addresses Tab” à Go to “IPAll” section
4. Remove TCP Dynamic ports and update TCP Port: <Static port>
For ex: - 5000
Restart the SQLServer to take effect of new Static port.
You can configure the SQLServer to listen on multiple Static TCP Ports. Adding Additional ports to SQL Server will increase the performance of the SQL Server.
Follow these steps to configure additional TCP ports:
1. Click Start à All Programsà Microsoft SQLServer à Configuration Tools à Launch “SQLServer Configuration Manager”
2. Under “SQLServer Network Configuration” à Select “Protocols for <Instance Name” à Got to TCP/IP Properties
3. Select “IP Addresses Tab” à Go to “IPAll” section
4. Enter the additional port separated by a comm for TCP ports
For ex:- TCP Port: 1234,5000
Verify the port configuration
1. You should be able to see the new port information in error.log file
Server is listening on [ <IP Address> <Port Number>].
For ex: - Server is listening on [ <10.10.10.10> <ipv4> <5000>].
If you have configured multiple static TCP ports then you should see below information in error.log file
Server is listening on [ <IP Address> <Port Number>].
Server is listening on [ <IP Address> <Port Number>].
For ex: - Server is listening on [ 10.10.10.10 <ipv4> 1234].
Server is listening on [ 10.10.10.10 <ipv4> 5000].
2. You can also check the updated port information in “regedit”
Start Registery (regedt32.exe) and locate the following registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer\SuperSocketNetLib\Tcp
In case of 2005,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<MSSQL.x>\MSSQLServer\SuperSocketNetLib\Tcp\IPAll
If the allocated port is Static then you should see below values
TCPDynamicPorts = Blank
TCPPort = New Static port
If the allocated port is Dynamic then you should see below values
TCPDynamicPorts = Current used port
TCPPort = Current used port
To configure a static port for the specialized Dedicated Administrator Connection (DAC), you must update the registry key that corresponds to your instance. For ex:- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\ MSSQL.X\MSSQL Server\SuperSocketNetLib \AdminConnection\Tcp
Where X indicates the number
Please note that it is not recommended that named instance listen on port 1433
Regards,
Satishbabu Gunukula
Posts
