I can't offer too much advise except to say,... a few of the projects I have been involved in have used it. I have never had to set it up or admin it or anything. I have heard that setup can be a pain in the butt, but once it is going things tend to run okie.
As a developer I have never had any problems with what others have set up in the way of clusters.
Notes from the Field
I sent out an email to a few people that I know run a cluster in production and have done so for a while. Here's the information I got back from JohnDeere:
I have found the biggest shortfall to be the lack of monitoring tools for Microsoft Cluster Services. There is no built in notification if a node fails, but at least on Win2K you can install the cluster admin tool on your local workstation so you don't have to go to the server room. Terminal services does not work in the cluster.
Be sure your backup strategy will work in a cluster and be sure it will work for Sql server. Our original strategy was Compaq's virtual replicator. It does not work in a cluster and I was never sure how these snapshots would work for SQL server. We switched to a Spectra 10000 4 drive AIT backup unit.
If your cluster is not fully redundant make sure you have the spare parts in stock or on a quick delivery time frame. Our cluster is not redundant but Compaq has all the parts on a 4 hour delivery time. (SAN controllers, SAN Switch, fiber cable, disk drives We have one hot spare but none on the shelf) The basic idea is to survive a CPU fault but if any of the shared disk pieces fail and you have no redundancy the whole cluster is down. (Management really does not like this)
Be sure to mention only start and stop services from the cluster administrator. Never start or stop SQL or its related services from enterprise manager, Sql service control or the services applet from control panel. The only way I have been able to recover from this is a reboot of both nodes on the cluster.
One of the biggest issues on our cluster is disk contention. You have 2 servers fighting for time on a single physical disk cabinet. In my cluster if I start 2 backups at the same time. The time to backup doubles or If we start a major import operation on node1 the performance of node2 suffers. (graz: I'm lucky. We have two physical disk cabinets.)
I would also mention hardware sizing (# of CPU's & RAM). Make sure one of your servers has enough horsepower to run both instances of SQL Server in the event of a fail over. In the 7 world this is a little different but in 2000 you are running 2 separate instances of mssql.exe and its associated programs.
If you cluster 2000 you must name the instances and change the port from 1433 on one of the instances. If you don't have MDAC 2.6 then you must alias the non-1433 server via client network utility
The company that I'm working for has the SQL cluster servers with active to active. Like rokslide said, the setup is very tough, especially for active to active. We had Microsoft consultants to set up and cost much time and money. After it is up, that is really good.