Scaling out SQL Server for the web (Single Writer Multiple Readers)

Question

Has anyone had any experience scaling out SQL Server in a multi reader single writer fashion. If not can anyone suggest a suitable alternative for a read intensive web application, that they have experience with

Answer 1

I don't have any experience with scaling out SQL Server for your scenario. However for a Read-Intensive application, I would be looking at reducing the load on the database and employ a Cache Strategy using something like Memcache or MS Velocity

There are two approaches that I'm aware of:

1. Have the entire database loaded into the Cache and manage Adding and Updating of items in the cache.

2. Add items to the cache only when they are requested and remove them when a write operation is performed.

Answer 2

Some kind of replication would do the trick.
http://msdn.microsoft.com/en-us/library/ms151827.aspx

You of course need to change your app code.

Some people use partitioned tables, with different row ranges being stored on different servers - united with views. This would be invisible to the app. Federation for this practice, I think.

By designing your database, application and server configuration (SQL particulars - location of data/log/system/sql binaries/tempdb), you should be able to handle a pretty good load. Try not to complicate things if you don't have to.

Answer 3

It depends on probably 2 things:

1. How big each single write is?
2. Do readers need real time data?

A write will block readers when writing, but if each write is small and fast then readers won't notice.

If you offload, say, end of day reporting then you batch your load onto a separate server because readers do not require real time data. This makes sense

A write on your primary server must be synched to your offload secondary server... which will block there as part of the synch process anyway + you add an overhead load to manage the synch.

Most apps are 95%+ read anyway all the time. For example, an update or delete is a read followed by a write.

My choice would be (probably, based on the low write volume and it's a web app) to scale up and stuff as much RAM as I could in the DB server with separate disk paths for the data and log files of the database.