In default instance of SQL Server there is a listener, listing to port 1433. When we create named instance the port is randomly dynamic as default behaviour of instance when it's not configured.In default instance, connection goes to TCP port 1433 and in named instance, connection goes through service browser and it goes to UDP port 1434 and then SQL browser responds by port number.The default instance of SQL Server regularly just support static port but named instance can support dynamic and also static. For named instance you might need to change dynamic port to static. For example, in SharePoint secure farm environment, the recommendation is to block UDP port and use named instance with a port different from 1433.
In order to change SQL Server port you can go to the SQL Server Configuration Manager:
Start=>Program Files=> Microsoft sql server =>Configuration Tools=> SQL server configuration manager
In Configuration Manager,on the left panel side, expand Sql Server Network configuration.Click on protocol for instance name then in right hand panel, right click and go to properties on the TCP/IP.
As you see below, click on IP Address tab then scroll down in IPALL section blank for TCP Dynamic Port row and your desired port in TCP Port row.
After this change you have to restart SQL Server service to make this change happens.
Note1:Remember if you doing this change in SharePoint instance you have to create alias for each application server.
SQL Server client aliases are necessary on all computers that communicate with the computer running SQL Server
Note 2:For changing port in clustered named instance do following:
Bring the SQL Server service offline from cluster administrator.
Disable the checkpointing to the quorum.
Change the Dynamic port of the sql server to static in all the nodes .
Enable the checkpointing to the quorum.
Bring SQL Server online.
Amin, what if when you go to disable the checkpointing to the quorom for the SQL Server service, the clus res for checkpoints states the following ---> SQL Server (XXX) None
ReplyDeleteSince it says "None" do you still remove the checkpoint? I had one instance that had the listed values and this one just has None?
Sorry for late respond.Can you explain more details? you mean changing port number in SQL Server cluster instance? I know if you do this in cluster environment based on my experience you have to use alias to connect to that instance.But still your question is not clear.
ReplyDeleteThanks,
Amin