OK, let me summarize a few things:
A single address CAN have multipe IP's, per the example with Yahoo. When this occurs, a DNS server hands them out one at a time, in a round-robin mode. This is great for simple load balancing, but if you have one IP fail, you're going to have problems - Every time that IP comes up in the round robin, it will fail.
All the load balancers listed are primarily focused on outbound traffic. When you have inbound traffic, each of your ISP's will assign a range (or use one that you have) for your network. All your requests must come from this range and will get routed BACK to the IP via that ISP. Two ISP's mean two address ranges, and the balancers will simply pick and choose which network to send the request out based on load factors, availability, etc.
If you want a true redundant solution, you MUST use BGP. This allows a single IP range to be shared among two ISP's. If your primary ISP link fails, the secondary will kick in and allow you to continue to function. This is how every major website in the world works (at least any that I know of). Yes, many of them are in colo facilities - Same thing happens, just done by the colo.
On that note, running BGP is not easy, simple or cheap. You have to have routers with enough memory to hold a massive routing table. You need to understand how it works in order to configure and maintain it, otherwise you're risking having everything blow up.
Here's the question - Why do you need two ISP connections? Is it for the additional traffic or is it for redundancy? Is it inbound or outbound?
If you're running a server in house, I have only one piece of advice. Don't. Unless you are well-set, with a fully-redundant, BGP-enabled network it's not worth it. Put the server at a colo somewhere and let someone else manage the network and possibly the server, depending on the content. If you need to have some kind of back-end connection, consider a T1 (or multiples, for redundancy). The whole setup would probably be cheaper than a second ISP connection and a full BGP implementation.
- G