SQL 2005 does not offer internal load balancing but you can have multiple processors on a single machine and set their affinity. Some other solutions you might find interesting are here:
If you need to scale out your SQL Server installation you have a couple of options.
1. You can use a federated database which stores part of the database on each server. Then the load used to query each part is isolated on each server.
2. You can use merge replication between a couple of SQL Servers so that you can write to either SQL Server and the data will be replicated to the other server within a few seconds.
3. You can setup a multi-tier database farm where you write to a single server, and setup transactional replication from that server to a set of read only servers which you can then load balance behind a load balancer.
4. You can use the new peer to peer replication technology to create a pool of servers.
When selecting which technique to use to keep in mind that any replication technique which has more than one SQL Server which you are writing to can have data problems if your unique identifier is not an identity field (such as a username for example).