There is no magical number of servers that encompasses "high availablity." If your needs are modest, you can do it with a single box, though (obviously) as soon as you have to reboot it, there go your 4 9's.
As long as your site isn't being heavily utilized, I'd say that two (decent) machines with a mirrored database set up (which is supported by MSSQL web, as you know), and NLB should be fine. Again though, this is a very general question. I run about 50 HUGE sites off of two "servers" but the servers are big honking Sun blade servers.
My worry wouldn't be about the machines (I'm assuming you'd be running both IIS and MSSQL on each machine, because there is no other way you could go about attempting high availablity otherwise) but about what you have between them and the internet. Having IIS exposed to the internet isn't the problem it once was, but IIS and MSSQL? I wouldn't do it if I had to be PCI or HIPPA compliant.
@Tom: This is your standard "defense in depth" build. Almost everyone will suggest separation of web and database servers, and on top of that, you should put heavy restrictions between them as well...Ideally there should be nothing on your webserver that you can't just restore from backup...Your code is all mirrored on your development machine, so just dump it back up to the website and you're fine. But database servers change all the time, and your setup needs to reflect that.
If you're restricted to two machines, you're going to have to install web and database on both, and do your best to harden them. I'd completely lock the databases down so that they can only be accessed by each other and the local webserver.
NLB takes the place of a hardware load balancer, and should automatically failover. Obviously YMMV: the hardware solutions have a lot to offer, but the price point is much higher. If you're not expecting a huge amount of traffic, NLB should be okay, but the hardware load balancers tend to have superiour protection against things like DDoS attacks. Again, it's all about what you're going to need.
@Tom: 1 IP is fine. NLB uses a "virtual" ip address that resolves to all the local machines. When you turn on NLB, go to the connection properties, and you'll see a "Network Load Balancing" tab. That tab needs the address that applies to the whole cluster (your static ip). Then you go in to the regular TCP/IP tab, and set up the local ip, which is the address that is specific to the individual machine.