Scaling SQL Server for the Web (Single Writers, Multiple Readers)
It probably depends on two things:
- How big is each individual entry?
- Readers need real-time data?
Writing will block readers while writing, but if each entry is small and fast, readers won't notice.
If you're offloading, say, an end-of-day report, you're uploading your load to a separate server because the readers don't need real-time data. It makes sense
The entry on your primary server needs to be in sync with your secondary offload ... which blocks it as part of the sync process anyway + you add overhead to manage the sync.
Most apps still 95% + read. For example, an update or a delete is a read followed by a write.
My choice would be (probably based on low write level and its web application) to scale out and fill as much RAM as possible on the DB server with separate disk paths for database data files and logs.
source to share
I have no experience in scaling SQL Server for your scenario. However, for a Read-Intensive enabled application, I would like to reduce the database load and use a cache strategy using something like Memcache or MS Velocity
There are two approaches that I am aware of:
-
Load the entire database into the cache and manage the addition and update of items in the cache.
-
Add items to the cache only when requested and remove them when a write operation is performed.
source to share
Some kind of replication would do the trick.
http://msdn.microsoft.com/en-us/library/ms151827.aspx
You will of course need to change the application code.
Some people use partitioned tables, with different ranges of rows stored on different servers - merged with views. This would be invisible to the application. A federation for this practice, I think.
By designing your database, application and server configuration (SQL data - data location / log / system / sql binaries / tempdb), you should be able to handle a pretty good load. Try not to complicate things if you don't need to.
source to share