:::: MENU ::::

Posts Categorized / Ports

  • Oct 25 / 2012
  • 0
DBA Interview questions, dbDigger, Monitoring and Analysis, Ports, System Administration, Windows Server

How to identify the active port/ports of SQL Server DB engine

In previous post i have discussed the ports configuration for SQL Server database engine. For any SQL Server instance what are the options to identify the active ports? For this purpose we have three major methods to identify the SQL Server port through GUI. We may use undocumented extended stored procedure master..xp_regread system  to read the registry value for this purpose.However in this post i would cover only the major GUI methods to discover the active TCP/IP port for any SQL Server instance.

  • Through SQL Server Logs
  • Through SQL Server Configuration Manager
  • Through Windows Application Event Viewer

 Through SQL Server Logs

We may use xp_readerrorlog SP in SSMS or SSMS GUI to determine the current port for SQL Server. Execute the SP in SSMS query pane and get the results

 USE master   
 xp_readerrorlog 0, 1, N'listening on', 'any', NULL, NULL, N'asc'   

Same may be determined through SSMS GUI

identify the active port of SQL Server through SSMS GUI

Through SQL Server Configuration Manager

Go to SQL server configuration manager and open TCP/IP properties in network configurations of specific instance. It would show the active TCP/IP port for SQL Server. Port may be noted or even changed to a specific static one here.

Identify SQL Server port through Configuration Manager

Through Windows Application Event Viewer

Another major option available for port discovery is through Windows event viewer. Find the event viewer in Windows Administrative Tools and go to application logs folder. There you may get the port information for your specific SQL Server instance.

Identify SQL Server port through Windows event log viewer

This is not the end, there would be some other ways to discover the same information. Also read the post and comments section on MSSQLTips for same issue.

  • Oct 23 / 2012
  • 0
dbDigger, Ports, System Administration

SQL Server ports configuration

Ports are a logical point of connection provided and managed at OS level. OS directs the data arrived at NIC for a specific port to its specified application. SQL Server ports are used at instance level and a port can not be shared among multiple instances on same machine. Before going in to detail of ports configuration for SQL Server level, we should have a look at categories of ports

IANA Reserved Ports

These ports range from 0 to 1023. The Internet Assigned Numbers Authority (IANA) allots these ports only to standard protocols. Vendors or users may not use any port within this range.

Vendor Reserved Ports

Ranges from 1024 to 49151. These ports are registered by IANA for applications of specific vendors. Other users are not able to use any port within this range. For SQL Server the default reserved port in 1433.

Open for Users Ports

Ranges from 49152 to 65535. Ports within this range may be used for end user purposes. If we want to use dynamic or static customized port number then it should fall in this range.

SQL Server Default Port

Default instances of SQL Server use port no 1433 by default. However named instances and express edition use dynamically allotted port by default. It is a good practice to change the default 1433 port no to user allotted static port.

Port Types in SQL Server

For SQL Server we may classify the ports in following heirarchy
  • Dynamic Port (fall in the range between 49152 to 65535)
  • Static Port
    • Default Port (1433)
    • User Allotted (Any port between 49152 to 65535)

Choosing Between Static and Dynamic Port

Dynamic ports serve the purpose of simplicity and to use the ports other than default 1433 but there are scenarios when you have to allot a specified static port to the SQL Server instance. Especially while working with firewalls we have to exclude the ports explicitly from firewall rules. In such cases it is required to use a static port rather than a dynamic new port each time with SQL Server service start.

Can we use more than one port for single instance?

The answer to this major question is “YES”. We may configure a SQL Server instance to listen on multiple ports at a time. To learn and implement  this functionality read this article by Jugal Shah.

Here is a very good post about the ports that a DBA should have essential knowledge of. It contains the ports for all SQL Server endpoints and services.
And also a very good point added by Yusuf Anis
“And for those who still have pre win 2k8 boxes with them the dynamic port range is limited to 1024-5000 on both TCP & UDP.”

Consult us to explore the Databases. Contact us