I've been thinking about building a website which could potentially get a lot of traffic. It would be a research site of sorts with wiki capabilities. So users would be able to edit information on the site. However, 99% of users would probably never edit anything and would simply be searching / browsing the site. So they could theoretically do most things from a readonly copy of the database.

With that in mind, I was thinking that putting together something like this would work well... enter image description here

So there would be 3 physical servers in the same datacenter:

  • The "Primary SQL Server" would receive any UPDATE, INSERT, or DELETE operations.
  • The two web servers would have readonly copies of this database, which would be kept up to date with log shipping. These servers would be queried heavily as users browse the site and would also serve up ASP.NET pages through IIS.

I like that this model improves both scalability and reliability at the same time. If either web server goes down there'd be no interruption of service. If the Primary SQL Server goes down, certain functions of the site would be disabled but the site would still be up in a readonly state, so to speak. Then last of all, the load balancer would distribute load between the two web servers, which is nice.

But I have a couple questions about this:

  1. Is log shipping a good method to accomplish this?
  2. What is the minimum version of SQL Server required to do this? For example, would it be possible to run SQL Server Web edition on the Primary SQL Server and SQL Express on the web servers?

Thanks for your help.


What you have described would work.

However if its mostly read-only you will be dealing with often the easier answer is to look into in-memory or distributed caches. So for instance, when your web servers fire up they would load "x" amount of data either into the memory on your web server or a cache server.

All user requests would go to the in-memory cache (very fast). Any updates could then trigger the cache to go back to the DB and get the latest copy for future requests.

Take a look at the Windows Server App Fabric system. In you want true HA the same set of features is available with Windows Azure and would make things even more redundant.

  • Thanks Brent. I guess one thing I'd be afraid of with the distributed cache approach is what happens when the Primary SQL Server goes down? The web servers would need to have cached the entire database in order for the website to keep running. – Steve Wortham Aug 9 '12 at 18:11
  • Sure, but if thats a point of concern then you start talking about a SQL Server cluster and HA on the SQL side to make sure your server stays up. It just depends on how many levels you think you need HA and thats something only you will be able to answer. – Brent Pabst Aug 9 '12 at 18:35
  • Well, with my plan above, if the Primary SQL Server goes down, the site will still be up in readonly mode. This would be perfectly acceptable. And I think this would be considerably cheaper than building a SQL Server cluster since I wouldn't have to fork over the big money for SQL Server Enterprise edition. – Steve Wortham Aug 9 '12 at 18:53
  • You can run a two node SQL Cluster on standard and in 2008 since it uses an Active/Passive architecture you only have to buy one license for the currently active node and Windows... works out to be about the same if not cheaper since your web server SQL boxes would probably need more than the 4GB of RAM or CPU cycles depending on your load. – Brent Pabst Aug 9 '12 at 19:06
  • But like I said before you may be better off on Azure or AWS to make the design simple and redundant with the least amount of work. – Brent Pabst Aug 9 '12 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.