We are planning on setting a static port for SQL Server 2005/2008 failover clustering. Any guide on what port to choose/select for 4 nodes of each cluster?(Active/Active) I am also thinking some applications need to know about the static port. What is the best way of knowing which application is setup to use default port from the SQL instance? In general, what is the best way of implementing port number change on sql server failover clustering?
The port number you select doesn't matter. Static port numbers should only be needed if you need to specify holes through a firewall which isn't on the local server. All SQL Server client drivers can convert the instance name to the TCP port number automatically using the SQL Server Browser.
The nice thing about using dynamic port numbers is that the TCP port will always be available. If you specify a static port number, there is a risk that something else will grab that port before SQL Starts up (I've seen this happen before) which will cause SQL to start without grabbing the port.
- Sequential port numbers for each instance. Just pick some.
- Port numbers that match instance name (eg xxx1 = InstanceA, xxx2 = InstanceB)
- No app this century should require "default" port 1433. They should resolve instance name. Only the default instance (which won't apply here) should use 1433.
- If you change the port, some clients may cache the value but it should just work. If this is a new cluster then you shouldn't need to change anything.
I remember when working with clusters that the port change from dynamic to a static one doesn't take effect if you don't disable the checkpointing of the quorum.
Also, applications managers should be notified about the port change because there are places where this can be used. As well, the port of SQL Server Instance is used in Firewall settings in many types of network configurations.