While working on a SQL 2008 install and problems encountered when a second instance was added, some questions came up about how SQL uses ports and allow remote access - especially when a second instance is added to a host computer. Here is the summary:
1) SQL Server 2005/2008 Express, Eval, Developer all install their instances (root OR follow-on) remote access turned OFF by default. References: http://support.microsoft.com/kb/914277 and http://www.databasejournal.com/features/mssql/article.php/3692831/SQL-Server-2005-Express-Edition---Part-4---Surface-Area-Configration.htm )
2) SQL Server instances supplemental to the initial (aka "root") instance will use Dynamic port assignment for its "listening" port.
3) The default listening port for the initial instance of SQL Server is 1433 EXCEPT for Express which uses dynamic port assignment for ALL its instances by default.
4) The default listening port for additional instances is "dynamic" for all versions.
5) Because of the differences in how SQL Server installs and seems to modify Windows Firewall for the machine SQL is installed on, DSD-Best-Practices is to follow these steps (assuming your goals want remote access to the SQL Server AND you are allowed to make these mods):
a) Install the SQL Instances you want.
b) Use the (2005) "SQL Server Surface Configuration" / (2008) "SQL Server Configuration" program to enable remote client access to the SQL Server. (For 2008 details see: http://msdn.microsoft.com/en-us/library/dd857537(VS.85).aspx note that for 2005 it's done a bit different using the Surface Config tool)
c) Use the (2005) "SQL Server Surface Configuration" / (2008) "SQL Server Configuration" program to specify a (different!) set/static port for each instance (e.g. 1433 for the root instance 2433 for the next instance). (Restart the changed instances to put this into effect).
d) Create a rule to open the machines (Windows) firewall for the ports used for all SQL instances.
e) Test access from a remote machine.
6) The SQL Server Browser Service acts as sort of a proxy server or DNS server on a machine with more than one SQL Server instance. It handles attempts of SQL client's trying to find/reach a SQL Server (instance) by listening on port 1433 (on the server the SQL Server instances are on) and bouncing the requests to the actual port that one of the instances is really answering on. Ergo, on some items (System data source name wizard) you shouldn't expect to see or enter the instance name for a SQL Server. For example, if you have a machine named MySQLServer and the root instance is "MySQLServer" and the second instance is "MySQLServer\IamNumTwo" then you will only see "MySQLServer" in the dropdown.
7) SQL Server Browser Service Reference: http://msdn.microsoft.com/en-us/library/ms181087.aspx
