Let me pose the scenario and see if anyone has a solution:
Most of our applications are client/server, in other words they have no middle tier. My management would like to be able to create warm standby servers for our SQL 2000 instances. In the case that we have to "fail-over" to one of these warm standbys, it would be ineffective to update all of our client applications with the name of the new server. Consequently, management would like to place aliases in DNS and have clients connect to the server using the alias. Then, in cases of failover, we simply need to update the DNS with the IP address for the new server; clients don't need to be changed.
We do this now for some of our 6.5 and 7.0 servers, and it works fine, but named instances in SQL 2000 throw a new wrench into the works. Since a named instance specifies a port on a server, the clients must have the ability to resolve an alias name not only to specific server IP, but to a specific port as well. Of course we can do this through the Client Network Utility or through and ODBC DSN, but this defeats the purpose, for if we set this alias up in the client, then we'd have to change the information on every client in cases of a failover. Placing the alias in DNS won't work, since that will only resolve the IP, but when the client then queries port 1434 on the server in order to resolve the named instance to a specific port, the client will be looking for a name that doesn't exist on the server, since the client will be using the DNS alias name instead of the computer's actual name when doing the lookup.
The question, then, is how can we "failover" a named instance of SQL 2000 to a warm standby server without having to update all of the clients with the new server's name?
So, there's the scenario and the conundrum. I have some ideas for workarounds, such as a central file DSN that is available to all clients for connection. Obviously a multi-tier enterprise architecture would solve the problem as well. Does anyone else have some ideas?
Edited by - mdburr on 09/05/2001 2:45:58 PM
Edited by - mdburr on 09/05/2001 2:46:24 PM