Telnet to Dynamic port of named instance of SQL Server 2005

Question

Telnet to Dynamic port of named instance of SQL Server 2005

rambilla4

SSCrazy Eights

Points: 8715
More actions
January 8, 2010 at 11:20 am

#136356

Hi,
We have SQL Server 2005 named instance INS1. I have verified the TCP Dynamic ports in SQL Server configuration manager->SQL Server 2005 Network Configuration->Protocols for INS1
and the port is 3232. So I need to provide this port number to our Network admin to open that port in order to connect the application server to SQL Server. Here I have a question:
Does this port number 3232 is fixed for that SQL instance Ins1 or it changes dynamically on his own?
please clarify me
thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

The port will be dynamically assigned each time the service restarts. If you want a specific port, you will need to change the Port to a static one.

http://msdn.microsoft.com/en-us/library/ms177440.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 2

The port will be dynamically assigned each time the service restarts. If you want a specific port, you will need to change the Port to a static one.

In Our production, we have all named instances and I documented the ports the named instances are running 6 months ago. After that the SQL Services are restarted many times. Now, today I have verified the port numbers for all the named instances and they are exactly same as 6 months ago. That means, the port numbers NOT changed even after the SQL Service restarted many times and we did NOT make the ports static.

please clarify me

thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

rambilla4 (1/8/2010)
The port will be dynamically assigned each time the service restarts. If you want a specific port, you will need to change the Port to a static one.
In Our production, we have all named instances and I documented the ports the named instances are running 6 months ago. After that the SQL Services are restarted many times. Now, today I have verified the port numbers for all the named instances and they are exactly same as 6 months ago. That means, the port numbers NOT changed even after the SQL Service restarted many times and we did NOT make the ports static.
please clarify me
thanks

The port will change dynamically. Follow the doc I provided and verify that somebody did not setup a static port for you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 4

Thanks,

I went through the document and it says "Named instances of the Database Engine and SQL Server Compact 3.5 SP1 are configured for dynamic ports. This means they select an available port when the SQL Server service is started"

But In our case, I'm the only person installed all the named instances and I did not change any ports after the installation and I'm sure nobody has set a static port as no one else has access to these servers.

where can I see if the ports for named instances are set to STATIC or not?

thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Server Configuration Manager --> TCP/IP properties --> IP Addresses -->TCP port should have something and dynamic should not.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Even when the ports are setup as dynamic - SQL Server will attempt to use the port assigned the first time. If it can - it will re-use the same port assignment all the time for that instance.

However, if something changes and that port is no longer available - SQL Server can change the port assignment.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 7

Thank you, I got it. But I want to know which one is the Best practice

1. Dynamic ports for all named instances

2. Set a static port for all named instance

Question: Does Every named instance should have a different static port, or we can assign the same port for all named instances, if we go for Static port assignment ?

thanks

lazy writer Ten Centuries Points: 1126 More actions · Answer 8

http://www.sqlmag.com/Article/ArticleID/38444/sql_server_38444.html

Hope this will help you 🙂

lazy writer Ten Centuries Points: 1126 More actions · Answer 9

I think you must give different port numbers for all the named instances to get server access from the different application servers with different sql instances. Please correct me if I am wrong.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

rambilla4 (1/8/2010)
Thank you, I got it. But I want to know which one is the Best practice
1. Dynamic ports for all named instances
2. Set a static port for all named instance
Question: Does Every named instance should have a different static port, or we can assign the same port for all named instances, if we go for Static port assignment ?
thanks

IMO - static ports. This will help to prevent connection issues due to port changes.

Every Instance must be on a different port - otherwise SQL server will have connection issues.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

BTW - you're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 12

CirquedeSQLeil (1/8/2010)IMO - static ports. This will help to prevent connection issues due to port changes.
Every Instance must be on a different port - otherwise SQL server will have connection issues.

This really shouldn't matter - as long as the SQL Browser service is running. If you are using the port number to connect, then that would be a problem if/when the port assignment changes.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Jeffrey Williams-493691 (1/8/2010)
CirquedeSQLeil (1/8/2010)IMO - static ports. This will help to prevent connection issues due to port changes.
Every Instance must be on a different port - otherwise SQL server will have connection issues.
This really shouldn't matter - as long as the SQL Browser service is running. If you are using the port number to connect, then that would be a problem if/when the port assignment changes.

If you are running the SQL Browser

and

If you connect by InstanceName

and

If there are no firewall rules that are Specific to an Instance of SQL Server (we had some rules setup where users could not connect to certain instances on the same server due to security needs).

Now, if each named instance is on a different server - then it makes no difference for port. I was going with the single server multi instance scenario since that was a popular topic today.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events