How to load-balance SQL Server 2008 for high-usage ASP.NET applications?

If it's high availability you need, then Windows/SQL Server Clustering or SQL Server Database Mirroring offer solutions. Clustering does require a lot of planning and familiarisation if you've never done it before, but it will be transparent to the application.

Load balancing is possible with SQL Server, but it's not for the faint hearted. It's a solution that uses Windows Network Load Balancing (NLB) in front of the SQL Servers. The SQL Servers themselves in the NLB are easier to manage if they are read-only, but they can be read-write if you use transactional replication with updateable subscribers. This type of replication is marked for deprecation in a future version though.

One final possibility is Scalable Shared Databases, but they are definitely read-only.

More reading:

Have a look at Allan Hirt's Apress books on SQL Server 2005 High Availability, and Pro SQL Server 2005/2008 Replication from Apress.

Scalable shared databases: http://technet.microsoft.com/en-us/library/ms345392.aspx

Relational database systems are rarely load balanced the same way that web servers are. The problem with the classic approach to load balancing is that all of your databases need to be in constant synchronization. The relational model is worthless if two servers don't have identical state at any point in time.

From your question, it doesn't look like you're even trying to accomplish load balancing, which is primarily a performance measure to ensure that only as many users are hitting each server as that server can handle. It sounds like you want a high availability setup. Since you say that you are using SQL Server, I would look into failovers. This means that, if the primary database is unavailable, clients will attempt to access the failover servers. SQL Server handles keeping the primary instance and each failover in sync and also handles resynchronizing the primary instance to the failover when the primary comes back online from being offline.