SQL Server TCP/IP Static and Dynamic Ports

SQL Server TCP/IP Static and Dynamic Ports must be configured/known by a DBA.

If Windows Firewall is active in a environment then all the ports closed except the ones which are given exceptions.
In some environments only the inbound connections are restricted,the outbound connections are not.
Exceptions to Firewall can either be given per Binaries or by Port.

To avoid connectivity issues with SQL Server, DBA should configure the Ports to Static where possible.
When a new SQL Server instance is installed if its Default instance then its given static port 1433.
But if the SQL Server instance is a named instance then it will give a Dynamic port to the SQL Server which may change when SQL Services are restarted.

In a Environment with Firewall active one should change the Dynamic Port for SQL Server to Static Port. After changing this exceptions can be added to Firewall to allow connections to SQL Server.

To check the SQL Server Ports Go To- SQL Configuration manager->Protocols for [InstanceName]->TCP/IP -> Properties.

Now scroll down to very last bit and see if a value other than “Zero” is given in Dynamics Ports section then your server is configured with Dynamic Port.
If you like to change this Static then change the Dynamic Ports value to “Zero”. Chose a port for Static port just below Dynamic port. Normally ports above 50000 are advised.

Any change of Ports/Properties on SQL Server Services needs a Restart of Services.
The Port SQL Server is listening On can also be checked in Errorlog of SQL Server as shown below: