Ports

Hi,

Today Iam Explaining about Ports.

What is a port? Why it is required?

A port represents a communication channel or endpoint. It is required to interact between Server and Client.A port number is part of the addressing information used to identify the senders and receivers of messages.

Port numbers are most commonly used with TCP/IP connections.Port numbers work like telephone extensions. Just as a business telephone switchboard can use a main phone number and assign each 

employee an extension number (like x100, x101, etc.), so a computer has a main address and a set of port numbers to handle incoming and outgoing connections.

The port numbers are divided into three ranges: the Well Known Ports,the Registered Ports, and the Dynamic and/or Private Ports.

Well Known Ports are also called System Ports for Example FTP port -25 and HTTP Port -80.

The System ports are reserved and ranged from 0-1024

The Registered Ports are those from 1024 through 49151

The Dynamic and/or Private Ports are those from 49152 through 65535.

SQL SERVER PORTS :

By default SQL SERVER listens on port number 1433 for a default Instance. If it is a Named Instance it listens of port (0) means it is dynamic.

Port Allocations : SQL SERVER Supports STATIC and DYNAMIC ports.

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.

If a default instance of SQL Server is listening on a port other than port 1433, you must either define a server alias name or change the global default port by using the Client Network Utility. However, you can also make the default instance of SQL Server listen on multiple static ports.

Note : Default Instance of SQL SERVER wont support Dynamic ports.But It can Support Multiple Static ports.

How to set up SQL Server to listen on multiple static  ports:

Here I will Explain you how to assign multiple static ports for a default Instance.Before you can think a little bit why SQL Server need multiple static ports.For each server-side network library, SQL Server sets up a separate "listen on" thread. If the server becomes overwhelmed with connection requests, the client connections are reset and are forced to reestablish a new session. To increase the performance of the SQL Server to simultaneously process more connection requests, additional TCP ports to listen on can be added. To add additional TCP ports, follow these steps.

Open SQL Server Configuration Manager, when you open configuration manager here you can see services,protocols for sql server and client protocols.

Go to Server protocols and right click on TCP/IP and select properties and then enter the additional port separated by a comma; for example: 1433,5000.

Stop and then restart the SQL Server, and review the errorlog.Your errorlog should resemble the following:

SQL server listening on TCP, Shared Memory, Named Pipes.<BR/>
SQL server listening on 157.54.178.42:1433, 157.54.178.42:5000, 127.0.0.1:1433, 127.0.0.1:5000.

Once you changed in Server Protocols then you need to change in client protocols 

by using SSCM

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 you start the named instances of SQL Server the second time, SQL Server opens 

the listening port number that was started the first time, as follows:

• If the port is opened without errors, SQL Server listens on the port
• If the port is not opened, and errors occur, You will get the error message.Error ID 10048

How to Find the Port Number for a particular SQL Server Instance:
 There are a couple of ways of finding this information
USING SSCM,Checking Errorlog,Using REGEDIT,Using Netstat -an
I will explain you clearly by using above methods.
SSCM : Open SSCM in that select protocols for mssqlserver then right click on TCP/IP and select properties.In properties window you will see two tabs protocols and IP addresses, Go to IP addresses and you will find port number.
Errorlog : Check the error log.  You will see an entry like: “Server is listening on [ 'any' <ipv4> 1433].”  The last 4 numbers denote the TCP/IP port number that is being used by that particular instance of SQL Server.
REGEDIT : Registry entry: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP
and you will see TCPPort as one of the entries and it’s value represents the port number for that instance.  In case you are using a named instance, then the registry entry will be: HKLM\Software\Microsoft\Microsoft SQL Server\<name of the instance>\MSSQLServer\SuperSocketNetLib\TCP
NETSTAT :
You can also use the TCP/IP netstat utility.  Example: Using “netstat -an” on the database server, I get:

In this case, the IP Address “192.168.20.196" is the IP address of the SQL Server box where the 

instance is running and the other IP addresses are the IP addresses of the client machines from where

the connections are being established.