Static and Dynamic port in sql server

STATIC PORT ALLOCATION

If you configure an instance of SQL Server to use a static port, and you restart the instance of SQL Server, the instance of SQL Server listens only on the specified static port. The SQL Server clients must send all the requests only to the static port where the instance of SQL Server is listening.

However, if an instance of SQL Server is configured to listen on a static port, and another program that is running on the computer is already using the specified static port when SQL Server is started, SQL Server does not listen on the specified static port.

By default, the default instance of SQL Server listens for requests from SQL Server clients on static port 1433. Therefore, the client network libraries assume that either port 1433 or the global default port that is defined for that client computer is used to connect to the default instance of SQL Server.

The default instance of SQL Server does not support dynamic port allocation. However, the named instances of SQL Server support allocation of both static and dynamic ports. By default, a named instance of SQL Server listens on a dynamic port.

DYNAMIC PORT ALLOCATION

Only named instances of SQL Server can use the dynamic port allocation process. In the dynamic port allocation process, when you start the instance of SQL Server for the first time, the port is set to zero (0). Therefore, SQL Server requests a free port number from the operating system. As soon as a port number is allocated to SQL Server, SQL Server starts listening on the allocated port.

The allocated port number is written to the Windows registry. Every time that you start that named instance of SQL Server, it uses that allocated port number. However, in the unlikely case that another program that is already running on the computer is using that previously allocated (but not static) port number when you start SQL Server, SQL Server chooses another port.

When an instance of SQL Server uses dynamic port allocation, the connection string that is built at the SQL Server client does not specify the destination TCP/IP port unless the user or the programmer explicitly specifies the port. Therefore, the SQL Server client library queries the server on UDP port 1434 to collect the information about the destination instance of SQL Server. When SQL Server returns the information, the SQL Server client library sends the data to the appropriate instance of SQL Server.

If UDP port 1434 is disabled, the SQL Server client cannot dynamically determine the port of the named instance of SQL Server. Therefore, the SQL Server client may be unable to connect to the named instance of SQL Server. In this situation, the SQL Server client must specify the dynamically allocated port where the named instance of SQL Server 2000, SQL Server 2005, or SQL Server 2008 is listening.

DETERMINE THE TCP/IP PORT NUMBER OF THE INSTANCE OF SQL SERVER.

SQL SERVER 2005

1. Open SQL Server Configuration Manager, and then expand SQL Server 2005 Network

Configuration.

2. Click Protocols for InstanceName, and then double-click TCP/IP in the right panel.

3. On the Protocol tab, notice the value of the Listen All item.

4. Click the IP Addresses tab:

• If the value of Listen All is yes, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item under IPAll.

• If the value of Listen All is no, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item for a specific IP address.

TROUBLESHOOTING

If the SQL Server clients cannot access an instance of SQL Server after you have configured, the following causes may exist:

A firewall may be blocking the specified TCP/IP port.

If the port that the SQL Server instance is currently listening on is blocked by your firewall, the connections will fail.

Another program may already be using the specified TCP/IP port.

If another program is already using the specified TCP/IP port, the port is not available to the instance of SQL Server and SQL Server clients may be unable to connect to the instance of SQL Server.

This problem is specific to an instance of SQL Server that is configured to use a static TCP/IP port. This problem does not occur for an instance of SQL Server that is configured to use dynamic port allocation. In dynamic port allocation, if another program is already using the specified TCP/IP port when you start the instance of SQL Server, the instance of SQL Server selects a new port.