Connectivity with SQL Server Express 2008 r2 and SQL Server 2000 on same machine

Question

At first glance this may same a duplicate of Installing both SQL Server 2000 and SQL Server 2008 on the same machine, but it is not.

I have SQL Server 2000 and SQL Server 2008 R2 installed on the same machine and working fine.

My problem lies with connecting to the 2008 R2 server from a remote machine. My connectivity needs to be TCP. The legacy installation or SQL 2000 uses the default port of 1433. The named instance is by default configured to use 'Shared Memory' and is working fine. When I configured the 2008 R2 server to use 1433 (I did not think that thru) the service refused to start becasue 1433 was already in use by the legacy SQL 2000 default instance. Doh!

What I want to do is have both servers available simultaneously via TCP. both servers need not be on the same port, put if I cannot run them on the same port, then how do I configure the clients?

Is there not some kind of proxy available that can monitor the 1433 port and pass the request thru to the correct SQL instance by name? Is this capability built into SQL server already?

Thanks,

Jim

Answer 1

It is normally possible to specify your desired port in the connection string on your clients.

After sqlservername add :port (e.g. sqlserver:2468).

You'd then need to modify one of your SQL Server's listening port via the Configuration Manager tool (SQL Server 2008), or SQL Server Client Network Utility (SQL 2000) and restart the SQL Server service for it to take effect.

Hope this helps.

Simon

Answer 2

As the OP hypothesizes, the SQL Browser service will assist in connecting incoming clients to specific named instances.

Ensure that the SQL Browser service is running and operating properly by testing it locally. If connecting locally works and connecting from a remote computer does not, the problem is usually a firewall, either the local software firewall or a firewall running on a piece of networking hardware like a switch.