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