Thursday, September 8, 2011

How to change Dynamic Port of the SQL Server Named Instance to Static port on stand alone or SQL 2005 cluster?



SQL server the default instance has fixed TCP/IP port 1433 while for the named instances it has random dynamic selected port assigned. This is the default selection while you are installing SQL server. You might require changing the SQL server dynamic port to static port, especially when clients connect to the SQL server thru firewall. In that case particular port requires opening on firewall to allow connection to SQL Server.

Standalone Instance:

StartàProgram FilesàMicrosoft SQL 2005àConfiguration toolsàSQL Server Configuration manager.
Selection SQL Server 2005 network configuration, click on the “Protocols for ” and on right hand side right click  and go to properties of TCP/IP as shown in below screenshot.

Click on the IP Addresses tab on the top and keep the Dynamic port row Blank and write the require port number on TCP Ports row in the IP ALL section as shown in below screenshot.

It require the restart the SQL server services to change the take effects.
You can verify the setting by below registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ \MSSQLServer\SuperSocketNetLib\Tcp

Clustered Instance:

It requires following the same above steps but sometimes when you restart the services / node port change back to dynamic port. This is because while you restart the services local copy of registry not change and checkpoint get the old port number.
So you need to follow below steps.
Disable the check pointing to the quorum
Cluster res "SQL Server (Instance Name)" /removecheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.3\MSSQLSERVER"

Change the Dynamic port of the SQL server to static on all nodes as above mentioned steps.
Enable the check pointing to the quorum
Cluster res "SQL Server (Instance Name)" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.3\MSSQLSERVER"

Make sure to give appropriate path for MSSQLSERVER. In above example I have MSSQL.3 because it is third instance I have installed. You may have different values.

Bring the SQL Server online after change.
Make sure port change after SQL services online.

1 comment:

  1. I have no words for this great post such a awe-some information i got gathered. Thanks to Author.

    ReplyDelete