MS SQL 2000, 2005 multiple instances not broadcasted to other subnets

We currently have multiple instances of MS Sql 2000 and MS SQL 2005 installed on servers. When at other locations that uses different subnets only the default instance is available, published, broadcasted, selectable.

We have TCPIP and name pipes enabled for all instances. This seems to be a common problem for all locations.

As I understand your situation, you have the set of SQL server instances installed on some subnet. From a different subnet, your client application is trying to enumerate the list of SQL instances available. This is an inherit behavior of the SQL instance discovery mechanism. The client application uses User Datagram Protocol (UDP) to send a message on the broadcast address. Most of the hardware network routers available are designed to prevent routing of UDP messages on the broadcast address beyond the subnet of the sender. The end result is what you are seeing where SQL instances on a different subnet can not be discovered. You can still connect to them if you know the machine name and the instance name.

Jimmy

|||Jimmy thanks, that makes sense.