August 5, 2015 at 5:45 pm
Hi
I'm working on a high load web site that's moving away from LAMP towards .NET/MSSQL. We're intending on setting up a load balancing server, two web servers and two database servers. After researching the concepts of load balancing the database servers I've come to the conclusion that a master/slave set up will be suitable, as outlined here:
http://www.oracle.com/technetwork/articles/dsl/white-php-part1-355135.html (I know... I'm not using PHP/MySQL this is just for conceptual purposes)
http://stackoverflow.com/questions/13437835/load-balance-sql-server
The application would have a much higher read to write load, and in some aspects we are currently using a NoSQL approach to reduce load for content that only updates, say, once per week.
I am wondering if anyone here has experience with this and has any thoughts/comments/suggestions.
Thanks!
August 6, 2015 at 1:43 am
hi,
With SQL Server 2012 you can use for example AlwaysOn to have 2 databases that are in sync. You can make The secondary readable. This has also the benefit you can fail over in case of problems.
Then you use the read intent hint so read only goes to your secondary.
We use this setup.
For full reporting we go directly to the other.
https://msdn.microsoft.com/en-us/library/hh213002.aspx
you can use regular mirroring also if needed.
August 6, 2015 at 2:16 am
Have you considered using Azure for a highly scalable web app? A lot of different options around load balancing / traffic management are available.
August 6, 2015 at 9:19 am
Define 'high load', and your need to load balance. Individual SQL Servers can handle billions of batches per day. Storage is usually the bigger bottleneck.
Sharding your data allows you to scale beyond that, an example of which is in the stackoverflow link in your post.
-Eddie
Eddie Wuerch
MCM: SQL
August 9, 2015 at 9:20 pm
Here's some "awstats" figures (don't forget it's currently a LAMP environment).
MonthUnique visitorsNumber of visitsPagesHitsBandwidth
Jan 2015268,056699,0679,762,66032,583,8413137.08 GB
Feb 2015182,677394,1656,042,65418,361,9071811.46 GB
Jan is fairly representative of a peak period, Feb is about average. What it doesn't outline is that of this probably 90% of the traffic occurs over a 2-5 hour period each day. So there's spikes, where the CPU/RAM (Apache/MySQL) on the server get to the point where they hit 100% and just stay there. Restart gets them going again, as a band aid.
This just shows the traffic off a public web site, it doesn't show stats for public web services that provide data feeds, or services for the internal network. So the total load could be substantially higher than that.
What's your thoughts?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply