Loadbalancing SQl Server 2005/2008
-
Hallo,
i have a question between a high performace database solution for a havy cou application running on an SQL 2005.
I have the problem that we have 30 User now on a SQL 2005 on a 8 core machine an my cpu perfomance ist not enouth but we will be in one months 60 users.
My idea was to cluster the DB with a HPC but i wos on a SQL workshop and hear that is not posiblle then a HPC dont suport SQL Databases.
It's posible otherway to cluster a SQl DB so i can use more then one machine to mahe a loadbalancing and user more then one server to get better performance?
Sorry for my bad english i hope you anderstand all i have write.
Thanks for your help and best regard
Chris
-
I don't think you can load balance SQL Server out of the box.
Windows and SQL Server clustering offer high availability but not load balancing. Your SQL instance will olny run on one Active node regardless of the number of node sin the cluster. If you have more than one instance running on the cluster then more than one node can be active.
You could look at using transactional replication (Updating Subscibers, peer 2 peer) to offer some form of load balancing, but i have never done this and don't know too much about setting this up but it might gie you a place to start.
Gethyn Elliswww.gethynellis.com
-
hmm thanks for you answer. I can't belive that all the middle SMB companys work on his crm systems or produktive systems with 4 processor an hundrets of users.
Maybe they use a big-system with more then 4 processor and other databases. But i need a solution to power up my sql 2005 database. the hardware is not the problem, we wont to buy a Blade Server System but if i can use the power of them for my SQl Server its not the right way. I have hear about partition of sql databases, but i dont now i this solution ist only good if you storage performance.
-
Can you tell me what the size is of your database? And what is the activity on your database? Are there processes / sql statements that need tuning, applying indexes and so on. It feels that the 60 users is not the problem you are facing..
-
I'll second Henri's suggestion.
If you're killing an 8 way with 30 users, I'd start with tuning before I'd throw hardware at it. Are you certain it is processor that is your bottleneck? What is the average % usage over an hour of heavy activity?
If it is indeed processor, I suspect you're suffering from a lack of indexes. With poor indexes, SQL has to scan the full tables more often that it should have to. Even if the entire database fits into RAM, it takes a lot of processing to flip through all those pages. If you're using Standard edition, you're even a little worse off as the advanced scanning capabilities of Enterprise are disabled.
Launch profiler on the server and filter on commands w/ > 30K reads. See how many you get, fix those, then start lowering the filter. If you get inundated @ 30K, raise the number until you only get a handful that can be focused on.
There are ways to spread the load through partitioning, replication and/or mirroring - but each of these have specific uses and requirements that are harder to implement than tuning the database.
I'll also add that it may not be an indexing problem at all, but with the information you provided, that is where I would start.
Kyle
-
And I third the suggestion!
More often than not, tune the queries. That is the first place to start, and the biggest gains can be achieved there. I've seen code that still runs slow when you throw the biggest baddest best server at it.
Profile your queries,
use a tool such as cleartrace http://www.cleardata.biz/cleartrace/
find your big slow running queries,
design indexes to support them,
and if necessary re-write to avoid the dreaded RBAR! (see some of Moden's articles http://www.sqlservercentral.com/Authors/Articles/Jeff_Moden/80567/
Only after you are confident that your database is optimized should you scale up or scale out your hardware.
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply