Tuesday, 6 October 2009

SQL Server : Non-standard ports

When you connect to a server using a default instance configuration (e.g. "myserver"), the SQL client calls the server on the standard port: 1433.
When you call a server using a named instance but lacking a port (e.g. "myserver\instancename"), the SQL client calls SQL Server's management service on port 1434.
The management service replies with a dynamically assigned port (chosen at sql engine service startup) for the instance and the client connection proceeds using that new port.
If the network firewall doesn't have either the management service port nor the dynamic port(s) open the connections fail.

Using dynamic ports for SQL instances means that the instance names are like a SQL DNS system. They are simply there to help the server resolve a dynamic port. Once the port is resolved, the name is superfluous.
You can use additional, and thus non-standard, ports for the additional SQL instances so as to know the ports so the firewall could be configured to allow them.
Once you have a predefined port assigned to a named instance you no longer need the actual instance name for the connection but can simply connect using the server name (or IP) and port number, for example: "myserver,1432".
Also you no longer need to run the SQL browser service on the server that provides the instance name lookup.