January 24, 2011 at 7:58 pm
Hi,
I wanted to change the SQL Server Port Number for one my sql instances but if I really want to change how can I choose which port number to use for sql server as some port as already been used for some other purposes?
After the sql server portnumber is changed and restarted then the sql server service that is effected comes in right away? If I restarting the machine does restarting of machine (operating system) bring back the sql server portnumber to Default portnumber 1433 If I had changed the sql portnumber to any port number other than 1433.
Considering that if we are changing the portnumber of sql server to dynamic port and restarting of machine will produce the different sql server portnumber for every time of restart of SQL Server or operating system?
Can any one please clear with this issue?
Thanks,
Ravi.
January 25, 2011 at 12:40 am
Hi Ravi,
In terms of port number, there are two elements to this. The lower port numbers (like 1433 for SQL) are assigned to various things, so it's generally recommended not to use those to avoid conflict. You can find a complete list of them at http://www.iana.org/assignments/port-numbers. When you're picking a port it's recommended you pick one from between 49152 and 65535, as these are not pre-assigned. There's nothing stopping you using a lower port number, just be careful to ensure it's not already in use. Running 'netstat -a' in a command prompt, will display all the ports that your server is currently listening on, so you can ensure you don't use one that will conflict.
Once you have restarted the SQL Server service the port change will take effect, and should remain in effect even after you reboot the machine.
You can choose to use a dynamic port, but only on a named instance, and in fact dynamic is the default for named instances. Dynamic ports are not supported on the default instance. If you use a dynamic port, your clients connect to the server via the default instance I think, and then from there the SQL Server Browser service directs the client to the correct port for the named instance you are requesting. The allocated dynamic port number doesn't change by default when you restart SQL or the machine, it will continue to use the port that was allocated when the server first started. The only time the port will change is if something else starts using that port number, in which case SQL will be allocated a new port which will be used from then on.
For more detail about how SQL handles port allocations, and how to change the ports used within SQL have a look at http://support.microsoft.com/kb/823938
Hope that makes sense.
Keith
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply