How we can implement load balancing in SQL Server?

Question

How we can implement load balancing in SQL Server?

MSQLDBA

SSCrazy

Points: 2783
More actions
July 8, 2011 at 5:55 am

#253413

Hi friends,
How we can implement load balancing in SQL Server?
If i wana to maintain railways enquiry systemd database. Which have thousands and lacks of users to access the database at the same time. How can i implement load balancing? bcoz SQL Server dosnot support load balance clustering.
Is there any other way?
Thanks in Advance,
________________________________________
M.I.
[font="Times New Roman"]
Learning is a path with no destination...
[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

With an immense amount of difficulty.

SQL scales up, not out. If you do decide to go that route, you need some form of replication (probably peer-to-peer) and then implement your own division of work.

Question is, do you need to? Thousands of users are not a problem for a well-designed, well-tuned database running on appropriate hardware.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

steveb. SSC-Forever Points: 46733 More actions · Answer 2

the way we did it was to use a NLB to handle the load balancing between our two client applications which were connected directly to their own database s

these were read only so it made it easier, then all the writes were to a central database and were replicated out to the

client facting dbs.

This worked for us as we had a very very high number of reads but only a very very small amount of writes

Though we did for resliliance as much as performance though that did improve as well.

GSquared SSC Guru Points: 260824 More actions · Answer 3

You can accomplish much of this through federated databases queried through a central "master server" (essentially, it's your load ballancer).

This isn't necessarily easy to do, and you can end up with issues with distributed transactions wrecking the performance of the whole thing if you don't know what you're doing on it.

Done right, it can scale SQL Server out quite effectively.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon