2008 SQL Server Express Load Balancing

Question

Hi, I have 3 home PCs that have 2008 SQL Server Express instances installed on each one of them... but right now only one has my database and I'm also running an application on that machine that requests a bunch of data from the database.

I don't know a whole lot about server architecture, but I have read some guides from Microsoft's website on how execute a query on multiple servers (http://msdn.microsoft.com/en-us/library/bb964743.aspx)

I have all of my queries going through stored procedures, so if the changes are all in the queries, then I don't think it's going to be difficult to do it. I need general information on what's the best way to set it up, here is some more details:

1. My PCs are a bit weak, so I'm trying to use as many of my home PCs as possible to balance the load. Right now I have only one PC that has the database and I'm running an application on the same PC.

2. My database is about 4GB.

   a. I have one stored proc that looks for specific rows.

   b. I have several stored procs that use temporary tables to calculate results based on the data obtained from about 20-30 rows.

   c. I already have an index on the columns which I'm searching.

3. I run an application on one the same PC as the DB that creates about 500 entities and each entity can have about 50 nodes, each node requests data from the database the request can be to any of the single value stored procedures, or they could be to the stored procedures that build a table (obviously the latter would be slower). Nodes are executed as fast as the database can handle the load.

4. Currently it looks like the bottleneck is the SQL server, so I'd like to distribute the load to my other PCs.

And my questions are:

1. If I put the database on the other PCs will that increase the performance?

2. What's the best way to set up my SQL Servers in this case?

3. Any other tips/references? Is there a better way to solve my problem?

FYI: I don't want to buy any hardware yet... I'm hoping for a software solution, I'm also waiting to see if I can get a free server from a friend, but even if I get a bigger server I'd still like to know how I can do load balancing.

Answer 1

Any other tips/references? Is there a better way to solve my problem?

IMHO throw hardware at it. Before you waste time trying to configure systems/software always be running on something you could at least describe as 'adequate' rather than 'weak'.

Answer 2

Buy a server. Seriously.

Lots of RAM, RAID disks, Server OS, no memory limit...

Scaling up is preferable to scaling out, especially at this low level.

Answer 3

Your problem is most likely IO caused by temp tables. See if you can buy a faster hard drive (or raid 0 hard drive setup) and place the tempdb database there. You most definitely do not need any clustering setup or even multiple read-only servers for the problem you describe.

Currently it looks like the bottleneck is the SQL server, so I'd like to distribute the load to my other PCs.

I'm curious, how did you arrive at this conclusion?