SQL Server database port number configuration

when the SQL Server service fails to start, locate the error log and review the error message.

commands for viewing port numbersin SQL Server:exec sys.sp_readerrorlog 0, 1, ' Listening '

One Configuring The TCP/IP protocol for an instance of SQL Server
can useSQL Server Configuration Manager to configure the TCP/IP protocol for the instance. After the configuration is complete, the instance is restarted before it takes effect.

1. The Protocols tab
(1) Keep the activity status
Specifies the time interval (in milliseconds) for a transport-persisted packet to check whether the computer that is located at the far end of the connection is still available.

(2) Listen all
SpecifyDoes SQL Server listen on all IP addresses that are bound to the computer's computer card. If set to no, each IP address is configured using the respective Properties dialog box for each IP address. If set to Yes, the settings for the IPAll property box are applied to all IP addresses. The default value is yes.

(3) enabled
If set toYes, the TCP/IP protocol is enabled.


2. "IP Address" tab
ifThe "Listen to all" in the "Protocol" tab is "No", each IP address can have its own properties. If enabled is set to No, the NIC is blocked from listening for TCP/IP connection requests.
if"Listen All" is already set on the "Protocols" tab, the "IP Address" tab is only valid for the settings of the "IPALL" property box.

second, static ports and dynamic ports
(1) static port
　　The default instance of SQL Server listens for incoming connections to port 1433. The port can be changed for security reasons or as requested by the client application. To configure a static port, leave the TCP Dynamic port box blank and provide an available port number in the TCP Port box.

Note:The SQL Server database engine can listen on multiple ports of the same IP address, and the ports are listed in a comma-delimited format: 1433,1500,1501. This field allows a maximum of 2047 characters.
to configure a singleIP address to listen on multiple ports, you must also set the listen all parameter on the Protocols tab of the TCP/IP Properties dialog box to No.

Note:SQL Server Configuration Manager displays the IP addresses that are available when you install SQL Server. If the IP address changes (add or remove network cards, DHCP IP address expires, and so on), edit the IP address box, and then restart SQL Server.


(2) dynamic port
by default, named instances (for example,\SQLExpress) is configured to listen on dynamic ports. To configure a dynamic port, enter 0 in the TCP dynamic port box.
If aWhen the instance of SQL Server is configured to listen on a dynamic port, at startup, the instance checks the available ports in the operating system and opens an endpoint for that port. The configured ports are logged in the current SQL Server startup log file.
The client connection must specify the port number to connect to. Using dynamic ports increases connection through the firewallThe complexity of SQL Server, because the port number may change when you restart SQL Server, and you need to change your firewall settings. To avoid problems connecting through the firewall, configure SQL Server to use a static port.

SQL Server database port number configuration