On a basic level, high availability is the reduction of single points of failure. You can build a cluster of machines having active/standby load-balancers, multiple web/app-tier, active/active database replication, but, if those machines only have a single connection to the network, there's still a massive point of failure. If those machines are dual-homed, but, your ISP only has one transit carrier to the meet-me room, there's another point of failure. What about power, you may have two power supplies in your machines, but, what about the power supplied by the ISP, is it fully redundant to your rack?
What level of availability are you looking for?
I've read good reviews of http://www.amazon.com/Blueprints-High-Availability-Evan-Marcus/dp/0471430269/ref=sr_1_1?ie=UTF8&s=books&qid=1267782128&sr=8-1 but I'm from *Nix background so couldn't comment on it's appropriateness for IIS + MSSQL