Answer by David 1 ·
You can but you have to create your own load balancing tier code to sit in the middle. You could also use distributed partitioned views (strictly sharding rather than load balancing).
Or you could consider purchasing the PDW product when it comes out: http://www.microsoft.com/sqlserver/2008/en/us/parallel-data-warehouse.aspx
However, just because your server is overloaded doesn't mean you need to scale out of the box. Maybe a server upgrade or some database tuning would be a more cost effective way to solve the problem.
Answer by TimothyAWiseman ·
I am not aware of any direct ways to do this, but you can certainly approximate it using replication, especially if at least some of the clients are read only.
Assuming some of your clients are read only, you can set up transactional replication between a Master Server(publisher) and one or more Reporting Servers(subscribers). Applications that need to write will write to the publisher, but the read only clients will interact with the Reporting Servers without putting any strain on the Master Server.
If all clients need to read and write you can set up something to accommodate that with merge replication, but that gets more nuanced and complicated quickly.
Answer by Jim Orten ·
There is no true load balancing for SQL Server....yet, not that would allow you to send a client to > 1 SQL Server instances.
An "overloaded server" usually means not enough RAM or disk. Those can be dealt with in much easier ways, except for very large databases, with more RAM (64GB with Windows Server 2008 and 64-bit SQL Server) and advanced disk systems, spreading the load over multiple disks.
At the database architecture level, you could also implement:
Answer by Yuriy Rozhok ·
You might consider new SSAS feature called scalable shared database. Read about this in Introduction to New Data Warehouse Scalability Features in SQL Server 2008
Answer by Peso ·
Overloaded database? What exactly is your definition of that?
In my experience, this is because of badly written code (oh god, how many times have a seen this?)
It is more cost-effective to rewrite code than buy new hardware.
In the series of Phil Factor Speed Phreak competitions, we have over and over seen code being rewritten to gain a 10-fold (and more!) speed.
How much would it cost to buy hardware to get same speed increase?
Answer by speedster ·