Question

load balancing in sql server

SQL Server clustering, SQL Server performance, SQL Server 2005, SQL Server, Load balancing, .

Is there any Load Balancing in SQL Server 2005 to distribute CPU load between more than one server?

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:

http://technet.microsoft.com/en-us/magazine/cc160974.aspx

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).