Since SQL Server 2005 has been released, there has been a lot of discussion about upgrading to this release. I was thinking it is similar to Windows 95.
I can remember reading articles about upgrading your system to Windows 95 and being able to take advantage of a bunch of neat features in the OS. This sounds very similar to SQL 2005 - doesn't it. I can remember the one biggest tip before upgrading your 3.1 machine to 95 was to get it running as well as it can in 3.1 before upgrading to 95. I think this little tip is probably still valid today. Get your SQL 7/2000 servers running as well as they can before you upgrade. Odds are if your server isn't performing well under SQL 7/2000, upgrading to 2005 won't buy you much. Even though 2005 performs better in most scenarios, you can't expect it to solve all of your performance problems.
I upgraded Windows 3.1 to 95 on a 386sx/33 (overclocked to 66!). I took the time to get Windows 3.1 running well before the upgrade. I can't remember any huge problems that I encountered. At the time, I was also assigned to assist some other non-technical folks with their upgrades. I had given these folks a checklist of things to do in preparation of the upgrade (Defrag, CheckDisk, uninstall non-critical programs, etc.). The ones that followed the checklist had a much better experience than the ones who "said" the followed the checklist. Of course, when we all got better machines (486/Pentiums), Windows 95 ran much better, but applications had to be reinstalled and personal configurations reset. Things ran much smoother on new hardware and without those old 16-bit apps on it.
The point here is to get your SQL 7/2000 DBs running as well as they can before upgrading. Use tools like the Best Practices Analyzer and Upgrade Advisor. This will surely help your upgrade avoid the bumpy road.