SQL Server 2000 – Changing from Active/Active to Active/Passive Clustering

Consider using Sql Client Aliases to abstract the data source name. The aliases are set on client computers by either adding keys to registry or by running c:\windows\system32\cliconfg.exe (for x86, x64 systems) and c:\windows\syswow64\cliconfg.exe (for 32-bit applications in x64 system). A Sql Client Alias is quite a bit like the additional DNS record you described. What's more, aliases support instance names too, a feature that DNS record won't do.

After an alias has been created, configure the client to use it instead of real Sql Server instance. Should you ever need to change the server, just modify the alias.

As a caveat, there seem to be some applications that won't work well with aliases. These often are legacy ones, like those using ODBC or proprietary database drivers. For modern applications, aliases will work just fine.

Yes you can do that with Failover Clustering. But you will need to license this second instance.

If one node fails and can't be brought up quickly, you need to consider whether or not the hardware can support both instances for a period of time. In a 2-instance scenario, it is generally advised to have 3 nodes: active/active/passive.